iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
MariaDB error 1020 means a record changed after the transaction last read it. In a documented InnoDB snapshot-isolation conflict, MariaDB rolls back the entire transaction—not just the failing UPDATE or DELETE. Capture the exact server version and effective settings before attributing the change to MariaDB 12: the default for innodb_snapshot_isolation varies by release, and the available documentation does not establish the configuration of a particular upgraded server.
What error 1020 means
MariaDB identifies error 1020 as ER_CHECKREAD. Its current error reference says: “Record has changed since last read in table ‘%s’; try restarting transaction.” The message describes a conflict between the record your transaction read and the record state encountered later.
One documented InnoDB cause is snapshot-isolation conflict detection. If innodb_snapshot_isolation is enabled, an UPDATE or DELETE can fail when another transaction changed a row after the current transaction established its snapshot. MariaDB documents this conflict as one that is treated similarly to a deadlock: the entire transaction is rolled back. The error itself does not prove that this mechanism caused a particular incident.
Why an upgrade might change the outcome
Do not infer the cause from “MariaDB 12” alone. MariaDB documents different defaults for innodb_snapshot_isolation across release series: it is ON by default from 11.6.2, while earlier series that introduced the variable—including 10.11 and 11.4—document it as OFF by default. The documentation lists introduction points in 10.6.18, 10.11.8, 11.0.6, 11.1.5, 11.2.4, and 11.4.2.
Those release markers do not establish what a particular MariaDB 12 package uses, what its earlier package used, or whether configuration files or session-level settings override defaults. Distribution and cloud-provider packaging may also matter. Check the running server rather than treating a major-version label as proof of a setting change.
Diagnose the conflict in order
- Record the exact before-and-after server builds. Capture the full version and distribution or package details for both sides of the upgrade. Compare them with the release-specific availability and default history for
innodb_snapshot_isolation. - Inspect settings on the affected connection. Query
SELECT @@GLOBAL.innodb_snapshot_isolation, @@SESSION.innodb_snapshot_isolation;and inspect the transaction isolation level for that session. The variable supports global and session scope and is dynamic, so a global value alone may not reflect the affected connection’s effective setting. - Verify the storage engine. Confirm the affected table uses InnoDB. The snapshot-isolation conflict explanation applies specifically to InnoDB.
- Reconstruct both transaction timelines. Record each connection’s
BEGIN, reads, writes, commits or rollbacks, and the exact failing statement. Determine whether the affected transaction performed its initial consistent read before the competing transaction committed. - Inspect indexes and plans. Check the read and update execution plans and relevant indexes. InnoDB locks index records. A locking read can use a covering secondary index without touching the clustered primary-index record, so seeing the same logical row in two statements does not prove they locked the same record.
- Trace the complete failure context. Preserve the exact SQL, connection/session settings, error text, and transaction boundary. Without the build, configuration, schema, execution plan, and competing schedule, the error cannot by itself identify an upgrade regression or its cause.
Choose a recovery strategy that matches the failure
Retry the whole operation in a new transaction
For the documented snapshot-isolation conflict, MariaDB recommends restarting the transaction, and the entire transaction has been rolled back. If the application can safely retry, treat 1020 as a transaction conflict: begin a new transaction, reread the data, and rerun the complete business operation using fresh state. Retrying only the failed statement can reuse assumptions from a transaction that no longer exists.
Bounded backoff and idempotency safeguards can be sensible application-level protections, but they are design choices; MariaDB does not guarantee them. Ensure that retrying cannot duplicate external side effects such as payments, messages, or emails.
Do not confuse replica retries with application retries
MariaDB documents error 1020 in some replication SQL-thread retry lists. That documents behavior for the replica SQL thread; it does not mean a client library or application automatically retries its transaction. Verify retry behavior in the application and client stack directly.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Understand isolation and locking before changing settings
| Choice or behavior | What it means | What to weigh |
|---|---|---|
| REPEATABLE READ | InnoDB’s default isolation level; consistent reads within a transaction share the snapshot established by its first read. | A stable snapshot can make a concurrent change relevant to a later write, depending on the enabled snapshot-isolation behavior. |
| READ COMMITTED | Each consistent read takes a fresh snapshot. | It changes snapshot and locking behavior; evaluate the application’s consistency requirements before switching. |
innodb_snapshot_isolation disabled |
MariaDB says disabling it restores traditional current-read behavior for locking reads, UPDATE, and DELETE. |
MariaDB notes that this can produce non-repeatable-read anomalies. Disabling it is a semantics change, not a generic error fix. |
Locking read such as FOR UPDATE |
The records actually locked depend on the indexes and execution plan. | A locking read is not proof that every logically related update is blocked, especially if a covering secondary index is used. |
Before changing either isolation level or innodb_snapshot_isolation, verify the current effective values and test the impact against the application’s correctness requirements. A configuration change may avoid this specific conflict while changing what transactions can observe.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
