{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"database-transaction-isolation-5-concurrency-bugs-and-how-to-fix-them-76877","url":"https://zyvop.com/database-transaction-isolation-5-concurrency-bugs-and-how-to-fix-them-76877","title":"Database Transaction Isolation: 5 Concurrency Bugs and How to Fix Them","subtitle":"Dirty reads, lost updates and write skew: what each bug looks like, which isolation level stops it, and what PostgreSQL and MySQL really do.","tldr":"Your code works alone, then two users act at once and a balance goes wrong. This guide shows five classic concurrency bugs as timelines, what each isolation level really stops in PostgreSQL and MySQL, and the fixes that work.","keywords":["postgresql","isolation levels","concurrency","MySQL","database transactions","Databases"],"entities":["Arpan Singh","postgresql","isolation levels","concurrency","MySQL","database transactions","Databases","ZyVOP"],"keyTakeaways":["Your code works perfectly when you test it alone.","Then two users act at the same moment, and an account ends up with the wrong balance, or a hospital ends up with no doctor on call.","These bugs are hard to find because they depend on timing."],"headings":["What isolation means","Five bugs, one at a time","1. Dirty read","2. Non-repeatable read","3. Phantom read","4. Lost update","5. Write skew","The four standard levels","What PostgreSQL and MySQL actually do","The fixes","Reproduce a lost update","Wrapping up"],"outboundLinks":["https://www.postgresql.org/docs/current/transaction-iso.html","https://doi.org/10.1145/568271.223785","https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html","https://github.com/ept/hermitage"],"contentText":"Your code works perfectly when you test it alone. Then two users act at the same moment, and an account ends up with the wrong balance, or a hospital ends up with no doctor on call. These bugs are hard to find because they depend on timing. Every statement is correct. The problem is how two transactions interleave. Isolation levels are the database setting that decides which interleavings are allowed. This post shows five classic bugs as timelines, explains what the four standard levels promise, and covers what PostgreSQL and MySQL actually do. It ends with fixes that work at any level and a lost update you can reproduce yourself in a few lines of Python. What isolation means A transaction is a group of statements that succeed or fail together. Isolation is the promise that transactions running at the same time don’t trip over each other. The ideal is serializable behavior: the result is the same as if the transactions had run one at a time. Databases can deliver that, but it limits concurrency. So most offer weaker levels that allow some odd results in exchange for speed. Your job is to know which odd results your level allows, and whether your code can survive them. In the timelines below, A and B are two transactions talking to one database. Time runs downward, and the numbers show the order of the steps. Five bugs, one at a time 1. Dirty read A dirty read means you read a change that another transaction has not committed yet. If that transaction rolls back, you acted on a value that never existed. sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: UPDATE balance = 50 (not committed yet) B->>DB: SELECT balance DB-->>B: 50 A->>DB: ROLLBACK Note over B: B acted on a value that never existedYou only see dirty reads at the lowest level, Read uncommitted, which few applications use. 2. Non-repeatable read You read the same row twice in one transaction and get different values, because another transaction committed a change in between. sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: SELECT price DB-->>A: 100 B->>DB: UPDATE price = 120, COMMIT A->>DB: SELECT price (same transaction) DB-->>A: 120 Note over A: Same query, different answerThis breaks reports and calculations that read a value more than once and expect it to stay put. 3. Phantom read This is the same problem for a set of rows. You run the same query twice, and the set of matching rows changes because another transaction inserted or deleted one. sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: SELECT count(*) WHERE status = 'pending' DB-->>A: 3 B->>DB: INSERT a pending row, COMMIT A->>DB: SELECT count(*) WHERE status = 'pending' DB-->>A: 4 Note over A: A new row appeared mid-transactionCounts, totals and “does any row exist?” checks are the usual victims. 4. Lost update Two transactions read the same value, each computes a new one, and each writes it back. The second write silently overwrites the first. sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: SELECT balance DB-->>A: 100 B->>DB: SELECT balance DB-->>B: 100 A->>DB: UPDATE balance = 150 (100 + 50), COMMIT B->>DB: UPDATE balance = 130 (100 + 30), COMMIT Note over A,B: Final balance is 130, so A's deposit is lostBoth deposits were valid, but the balance should be 180, not 130. This is probably the most common concurrency bug in application code. 5. Write skew Two transactions read the same data, then each updates a different row based on what it read. Each decision was valid on its own, but together they break a rule. sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: SELECT count(*) FROM on_call DB-->>A: 2 B->>DB: SELECT count(*) FROM on_call DB-->>B: 2 A->>DB: Take Alice off call, COMMIT B->>DB: Take Bob off call, COMMIT Note over A,B: Nobody is on call, yet each check was true when it ranThe rule here is that at least one doctor must stay on call. Because the two transactions wrote different rows, there is no row-level conflict to detect. That is why many databases miss it. The four standard levels The SQL standard defines four levels by which of the first three bugs they forbid. This table is from the PostgreSQL documentation: Level Dirty read Non-repeatable read Phantom read Read uncommitted Possible Possible Possible Read committed Not possible Possible Possible Repeatable read Not possible Not possible Possible Serializable Not possible Not possible Not possible Notice what is missing: lost updates and write skew don’t appear in the table. A well-known 1995 paper, A Critique of ANSI SQL Isolation Levels, pointed out this gap. The standard also sets only minimum protections. A database may do better than the table says, or behave differently, at the same level name. So a level’s name alone can’t tell you whether your code is safe. What PostgreSQL and MySQL actually do Here is how the two most popular open-source databases implement each level: Level PostgreSQL MySQL (InnoDB) Default Read Committed Repeatable Read Read Uncommitted Behaves exactly like Read Committed, so no dirty reads Real dirty reads are possible Read Committed Each statement sees a fresh snapshot Each read sees a fresh snapshot Repeatable Read One snapshot for the whole transaction. A concurrent change to a row you also change makes you fail with an error and retry. No phantoms One snapshot, taken at the first read. But UPDATE, DELETE and locking reads use the latest committed rows, so read-then-write code can lose updates Serializable True serializable behavior. Conflicting transactions are aborted, and you retry them Plain SELECTs become locking reads when autocommit is off, so readers and writers can block each other These details come from the PostgreSQL and MySQL documentation. To see the biggest difference, replay the lost update from earlier at Repeatable Read: sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: BEGIN (Repeatable Read), SELECT balance DB-->>A: 100 B->>DB: UPDATE balance = 130, COMMIT A->>DB: UPDATE balance = 150 (100 + 50) alt PostgreSQL DB-->>A: ERROR could not serialize access due to concurrent update Note over A: A rolls back and retries from the start else MySQL (InnoDB) DB-->>A: OK, the row is updated A->>DB: COMMIT Note over A,B: Final balance is 150, so B's deposit is lost endThe timeline is the same, but the outcome isn’t. PostgreSQL stops A, so your code must be ready to catch the error and retry. MySQL lets A overwrite B’s change with a value computed from an old snapshot. Write skew survives Repeatable Read in both databases. In PostgreSQL only Serializable stops it, and in MySQL, Serializable stops it by locking. Other databases such as Oracle and SQL Server differ again. Hermitage, a test suite by Martin Kleppmann, records how many databases behave at each level, so it’s the place to check if you use something else. The fixes Raising the isolation level is one fix, but it isn’t always the best one. These tools work at any level, and they often cost less. 1. Let the database do the math. Instead of reading a balance and writing back a new one, send one statement: UPDATE accounts SET balance = balance + 50 WHERE id = 1;The read and the write happen together, so a concurrent change can’t slip in between. This fixes lost updates even at Read Committed. 2. Lock what you read. SELECT ... FOR UPDATE makes other transactions wait until you finish. It fixes lost updates, and it fixes write skew if you lock every row your decision depends on. It can’t lock a row that doesn’t exist yet, so it doesn’t cover phantoms. With a lock, the two deposits from the lost update example come out right (shown at Read Committed): sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: SELECT balance FOR UPDATE DB-->>A: 100 (row is now locked) B->>DB: SELECT balance FOR UPDATE Note over B,DB: B waits for A to finish A->>DB: UPDATE balance = 150 (100 + 50), COMMIT DB-->>B: 150 (lock released) B->>DB: UPDATE balance = 180 (150 + 30), COMMIT Note over A,B: Final balance is 180, nothing is lost3. Check before you write. Add a version column, and make the update conditional on it: UPDATE accounts SET balance = 150, version = version + 1 WHERE id = 1 AND version = 7;If this changes zero rows, someone else got there first, so reload and try again. This approach works in any database and holds no locks while a user is thinking. 4. Let the database enforce the rule. Unique, foreign key and check constraints are correct under any timing. PostgreSQL also has exclusion constraints, which can stop two bookings from overlapping. If the rule is a constraint, there is no race to lose. 5. Use Serializable, and retry. Write each transaction as if it were alone, and let the database abort the ones that conflict. Your code must catch the error and rerun the whole transaction. In PostgreSQL the error is a serialization failure (SQLSTATE 40001). Here is the doctors example again at Serializable in PostgreSQL, where the database aborts one of the two transactions: sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: SELECT count(*) FROM on_call DB-->>A: 2 B->>DB: SELECT count(*) FROM on_call DB-->>B: 2 A->>DB: Take Alice off call, COMMIT DB-->>A: OK B->>DB: Take Bob off call, COMMIT DB-->>B: ERROR serialization failure (40001) B->>DB: Retry: SELECT count(*) FROM on_call DB-->>B: 1 Note over B: B now sees one doctor on call and keeps Bob on duty6. Keep transactions short. Never wait for user input or a slow network call inside a transaction. Long transactions hold locks and snapshots, which leads to more conflicts. Which fix to reach for depends on the bug: Bug Simplest fix Dirty read Read Committed or higher, which is the default almost everywhere Non-repeatable read Read everything you need in one statement, or use snapshot reads Phantom read Snapshot reads (Repeatable Read in PostgreSQL), a constraint, or Serializable Lost update Atomic UPDATE, a row lock, or a version check Write skew Lock the rows you read, a constraint, or Serializable Reproduce a lost update You can trigger a lost update on your laptop. This script uses SQLite and Python threads. SQLite runs writers one at a time, so a barrier forces the unlucky timing from the lost update diagram above: import sqlite3, threading, os DB = 'bank.db' def reset(): if os.path.exists(DB): os.remove(DB) con = sqlite3.connect(DB) con.execute('CREATE TABLE accounts(id INTEGER PRIMARY KEY, balance INTEGER)') con.execute('INSERT INTO accounts VALUES (1, 100)') con.commit() con.close() def balance(): con = sqlite3.connect(DB) return con.execute('SELECT balance FROM accounts WHERE id = 1').fetchone()[0] def unsafe_deposit(amount, both_have_read, a_has_written, is_b): con = sqlite3.connect(DB, timeout=5, isolation_level=None) # autocommit current = con.execute('SELECT balance FROM accounts WHERE id = 1').fetchone()[0] both_have_read.wait() # force the bad timing: both have seen 100 if is_b: a_has_written.wait() # B writes last, after A has finished con.execute('UPDATE accounts SET balance = ? WHERE id = 1', (current + amount,)) if not is_b: a_has_written.set() def safe_deposit(amount): con = sqlite3.connect(DB, timeout=5, isolation_level=None) con.execute('UPDATE accounts SET balance = balance + ? WHERE id = 1', (amount,)) def run(*jobs): threads = [threading.Thread(target=job) for job in jobs] for t in threads: t.start() for t in threads: t.join() reset() both_have_read, a_has_written = threading.Barrier(2), threading.Event() run(lambda: unsafe_deposit(50, both_have_read, a_has_written, False), lambda: unsafe_deposit(30, both_have_read, a_has_written, True)) print('read, compute, write in app code:', balance()) # 130, not 180 reset() run(lambda: safe_deposit(50), lambda: safe_deposit(30)) print('single UPDATE statement :', balance()) # 180Running it prints: read, compute, write in app code: 130 single UPDATE statement : 180The database did nothing wrong here. The bug is in the application’s read, compute, write pattern. I ran the script 20 times, and the app-code version always ended at 130. Wrapping up Most concurrency bugs come from a short window between reading a value and writing a decision based on it. Each fix above closes that window in a different way, and Serializable with a retry loop is the fallback when the logic is too complex for the others. Whatever you choose, test it with two real sessions to see what your database actually does.","contentHash":"sha256:e909ea4a86ab5d742d0afed8fbd4ca0bef8a4382212a02cd7e34a768cd6aa9df","authorName":"Arpan Singh","authorUrl":"https://zyvop.com/author/arpan","authorSameAs":[],"category":"Databases","tags":["postgresql","isolation levels","concurrency","MySQL","database transactions"],"audience":"Senior software engineers, systems architects, and technical leads working with Databases","tone":"Instructional, practical, code-first","readingTimeMinutes":9,"wordCount":2230,"faqs":null,"primaryTopic":"Databases","publishedAt":"2026-10-31T11:34:01.358Z","updatedAt":"2026-10-31T11:34:01.358Z","canonicalUrl":"https://zyvop.com/database-transaction-isolation-5-concurrency-bugs-and-how-to-fix-them-76877"}