MariaDB 1020 Error in Python Script After 11.8.3 Upgrade

The MariaDB 1020 error—“Record has changed since last read in table”—pops up in your Python script because innodb_snapshot_isolation, now enabled by default in MariaDB 11.8.3, catches self-modifications within a single transaction. Even with mostly reads and just one write before commit, a read → write → read pattern violates snapshot consistency under REPEATABLE READ isolation, triggering ER_CHECKREAD. Upgrading from older versions flips this switch on, breaking legacy code that assumed looser checks.

Contents

• What Causes MariaDB Error 1020 After Upgrade to 11.8.3
• How innodb_snapshot_isolation Triggers the Error
• Reproducing MariaDB 1020 in Read-Write-Read Patterns
• Root Cause: Self-Modification in Snapshot Isolation
• Fixing MariaDB Error 1020: Disable or Adjust Settings
• Python Code Changes for MariaDB 11.8.3 Compatibility
• Best Practices to Avoid MariaDB 1020 Post-Upgrade
• Sources
• Conclusion

What Causes MariaDB Error 1020 After Upgrade to 11.8.3

Picture this: your Python script hummed along fine on MariaDB 10.x, chugging through reads with a single update tossed in at the end. Then you upgrade to 11.8.3. Boom—MariaDB error 1020. Why now?

The culprit is innodb_snapshot_isolation, a variable flipped to ON by default starting in MariaDB 11.6.2 (and carried into 11.8.3). This enforces stricter consistency in InnoDB transactions using REPEATABLE READ isolation—the MariaDB default. Before, InnoDB let certain self-changes slide. Now? It flags them as violations, raising error 1020 (ER_CHECKREAD) when your transaction tries to commit after reading a row it just modified.

It’s not about concurrent users or high traffic. This hits even in solo transactions. Real-world apps like HammerDB workloads started failing immediately post-upgrade, as seen in GitHub issues. And the official MariaDB error docs confirm it’s tied to snapshot view mismatches.

But what exactly is a “changed record”? InnoDB creates a read view at transaction start. Any row version outside that snapshot—including ones your own write creates—triggers the error on re-read or commit.

How innodb_snapshot_isolation Triggers the Error

innodb_snapshot_isolation amps up InnoDB’s snapshot mechanism. When ON, it blocks commits if the transaction’s read view detects modifications to rows it read, even from the same transaction.

In your script’s flow—read data, do one write, read again, commit—the second read sees the post-write row version. But your snapshot expects the pre-write state. Conflict. Commit fails with MariaDB 1020.

This wasn’t default before 11.6.2. Older versions (like 10.6) had it OFF, mimicking MySQL’s looser REPEATABLE READ. MariaDB tightened it for better anomaly prevention, as detailed in their isolation level testing blog. Python connectors like mariadb-python or PyMySQL pass this through unchanged, so your code doesn’t “feel” different—until 1020 hits intermittently, especially under load.

Ever wonder why only one write breaks it? Because that write generates a new undo log entry, shifting the row’s visible version outside your initial snapshot.

Reproducing MariaDB 1020 in Read-Write-Read Patterns

Want to see it yourself? Fire up MariaDB 11.8.3 and run this Python repro straight from MariaDB’s Jira ticket MDEV-35124—it matches your “mostly reads, one write” scenario exactly.

import mariadb

conn = mariadb.connect(user='root', host='localhost', database='test')
cur = conn.cursor()
cur.execute("CREATE TABLE t1 (id INT PRIMARY KEY, val INT)")
cur.execute("INSERT INTO t1 VALUES (1, 10)")
conn.commit()

# Transaction starts here - REPEATABLE READ default
cur.execute("SELECT val FROM t1 WHERE id=1") # Read: sees 10
val = cur.fetchone()[0]
print(f"Initial val: {val}")

cur.execute("UPDATE t1 SET val = val + 1 WHERE id=1") # Single write

cur.execute("SELECT val FROM t1 WHERE id=1") # Read again: now sees 11, but snapshot expects 10!
val = cur.fetchone()[0]
print(f"Updated val: {val}")

conn.commit() # ERROR 1020 here!

On 11.8.3 with innodb_snapshot_isolation=ON, commit throws: MariaDB Error 1020: Record has changed since last read in table 't1'.

SQL-only version from MDEV-38490:

START TRANSACTION;
SELECT * FROM t1 WHERE id=1; -- Read
INSERT INTO t1 VALUES (2,20); -- Write (even unrelated row can trigger in some cases)
SELECT * FROM t1 WHERE id=1; -- Re-read
COMMIT; -- 1020!

Simple. Deadly for read-heavy scripts assuming InnoDB ignores self-updates.

Root Cause: Self-Modification in Snapshot Isolation

Dig deeper: InnoDB’s MVCC (multi-version concurrency control) uses undo logs for snapshots. At txn start, you get a read view pinning row versions.

Your write creates a new version. The re-read fetches it, but innodb_snapshot_isolation=ON cross-checks: “Does this match my original view?” No—because the write invalidated it. Hence, 1020 on commit.

This prevents “non-repeatable reads” from self-changes, a phantom anomaly MySQL ignores but MariaDB now polices. The MariaDB blog spells it out: it’s like READ COMMITTED but with REPEATABLE READ’s consistency, catching edge cases legacy apps exploited.

In Python? Connection pooling or autocommit=off keeps txns long-lived, amplifying the issue. Stack Overflow threads like this Python-specific one mirror your pain: post-upgrade, intermittent in read-write loops.

Fixing MariaDB Error 1020: Disable or Adjust Settings

Don’t panic—fixes abound. Pick based on your risk tolerance.

Quick Global Disable (Not Ideal for Production)

Edit my.cnf:

[mysqld]
innodb_snapshot_isolation=0

Restart. Fixes app breakage but weakens isolation. Seen in XenForo forums.

Session-Level (Per-Connection, Safer)

In Python, before your txn:

cur.execute("SET SESSION innodb_snapshot_isolation=0")

Or switch isolation:

cur.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")

READ COMMITTED dodges snapshots entirely.

App-Level Retry Logic

Wrap in try-except:

max_retries = 3
for attempt in range(max_retries):
 try:
 # Your read-write-read code
 conn.commit()
 break
 except mariadb.Error as e:
 if e.args[0] == 1020:
 conn.rollback()
 if attempt == max_retries - 1:
 raise
 time.sleep(0.1 * (attempt + 1)) # Exponential backoff
 else:
 raise

These handle the self-mod without config changes.

Python Code Changes for MariaDB 11.8.3 Compatibility

Python scripts need tweaks beyond SQL. Use mariadb connector (not mysqlclient—it’s fussier).

1. Explicit Isolation: Set READ COMMITTED per txn. Avoids snapshots.
2. Short Transactions: Commit after writes, restart reads. Breaks long read-write chains.
3. Read Uncommitted for Non-Critical Reads:

cur.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

4. Connection Args: autocommit=False, isolation_level='READ-COMMITTED'.
5. ORM Users (SQLAlchemy): engine = create_engine(..., isolation_level="READ_COMMITTED").

From Stack Overflow PHP analog, same logic: session vars > retries > downgrade.

Test locally: Docker MariaDB 11.8.3 image, run repro, apply fix. Gone.

Best Practices to Avoid MariaDB 1020 Post-Upgrade

Upgrades bite—plan ahead.

• Staging Tests: Repro workloads mimicking prod (read:write 100:1).
• Monitor Logs: Grep for 1020 pre/post-upgrade.
• Audit Transactions: Tools like pt-query-digest flag long txns.
• Prefer READ COMMITTED: Unless you need full REPEATABLE READ.
• App Refactor: Separate reads from writes into micro-txns.
• MariaDB 11.x Awareness: Check changelogs—more defaults tighten in 11.8+.

Apps like DOMjudge hit this hard (GitHub issue); they fixed via session settings. Your Python script? Same path. Future-proof by design.

Sources

1. MDEV-35124 — Official repro of Python read-update-read triggering 1020 with innodb_snapshot_isolation: https://jira.mariadb.org/browse/MDEV-35124
2. Isolation Level Violation Testing in MariaDB — Explains snapshot checks and ER_CHECKREAD mechanism: https://mariadb.com/resources/blog/isolation-level-violation-testing-and-debugging-in-mariadb/
3. MDEV-38490 — SQL test cases for 1020 in REPEATABLE READ self-modifications: https://jira.mariadb.org/browse/MDEV-38490
4. DatabaseError(1020) Python MariaDB — Real Python script failure post-11.8.3 upgrade: https://stackoverflow.com/questions/79870594/databaseerror1020-1020-hy000-record-has-changed-since-last-read-in-table
5. HammerDB MariaDB 11.8 Issue — Workload breakage and my.cnf fix recommendation: https://github.com/TPC-Council/HammerDB/issues/778
6. MariaDB Error 1020 — Official error code reference and basics: https://mariadb.com/kb/en/e1020/
7. MariaDB PDO PHP 1020 Error — Session isolation fixes for similar read-write patterns: https://stackoverflow.com/questions/79767034/mariadb-pdo-php-1020-error-record-has-changed-since-last-read-in-table

Conclusion

MariaDB error 1020 in your read-heavy Python script stems from innodb_snapshot_isolation=ON in 11.8.3 flagging self-changes—fix it session-side with SET SESSION innodb_snapshot_isolation=0 or READ COMMITTED, add retries, and refactor long txns. Test repros religiously before prod upgrades. You’ll dodge this pitfall and keep scripts flying smooth.