{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"database-deadlocks-explained-why-they-happen-and-how-to-prevent-them-ndkxl","url":"https://zyvop.com/database-deadlocks-explained-why-they-happen-and-how-to-prevent-them-ndkxl","title":"Database Deadlocks Explained: Why They Happen and How to Prevent Them","subtitle":"Understand why database deadlocks happen, how PostgreSQL, MySQL and SQLite handle them, and how to prevent them with practical fixes.","tldr":"Database deadlocks occur when transactions hold locks that each other needs. Learn how PostgreSQL, MySQL and SQLite detect and break them, reproduce deadlocks with Python and SQLite, and prevent them through consistent lock ordering, shorter transactions, and safe retries.","keywords":["postgresql","concurrency","SQL","databases","MySQL","Databases"],"entities":["Arpan Singh","postgresql","concurrency","SQL","databases","MySQL","Databases","ZyVOP"],"keyTakeaways":["Two transactions, two locks and a circular wait: how databases detect and break deadlocks, what causes most of them, and the fixes that work, from lock ordering to safe retries.","Everything passes in testing.","Then, in production, a request fails with “deadlock detected.” Each SQL statement is correct, and running the two transactions one after the other works fine."],"headings":["What a deadlock is","How databases break a deadlock","Four patterns behind most deadlocks","Fix 1: Lock in a consistent order","Fix 2: Take the strongest lock first","Fix 3: Lock less, and for less time","Fix 4: Retry, because some will still happen","Finding deadlocks in production","Measure it yourself","Wrapping up","Frequently Asked Questions","What is a database deadlock?","How do PostgreSQL, MySQL, and SQLite detect and handle deadlocks?","What are the common patterns that cause deadlocks?","How can I prevent deadlocks by using a consistent lock order?","Why should I acquire the strongest lock first, and how does it work in SQLite?","What practices help reduce deadlocks by locking less and for less time?","How should my application handle deadlock errors?","How can I find deadlocks in production for PostgreSQL and MySQL?","What is the difference between a deadlock and a lock wait timeout?","How does the retry logic for deadlocks work in the provided example?"],"outboundLinks":["https://www.postgresql.org/docs/current/explicit-locking.html","https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.html","https://www.sqlite.org/c3ref/busy_handler.html"],"contentText":"Two transactions, two locks and a circular wait: how databases detect and break deadlocks, what causes most of them, and the fixes that work, from lock ordering to safe retries. Everything passes in testing. Then, in production, a request fails with “deadlock detected.” Each SQL statement is correct, and running the two transactions one after the other works fine. The problem is the timing between them. A deadlock isn’t a database bug. It is what happens when two transactions each wait for something the other one holds. You can’t remove deadlocks entirely, but you can make them rare and harmless. This post explains how a deadlock forms, how PostgreSQL, MySQL and SQLite deal with one, and four fixes. The experiments use real threads and SQLite, and the scripts are at the end. What a deadlock is When a transaction changes a row, it locks that row until it commits or rolls back. Any other transaction that wants the same row has to wait. Waiting is normal and usually short. A deadlock is different: two transactions each hold a lock that the other one needs, so neither can ever continue. sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: UPDATE account 1 (locks row 1) B->>DB: UPDATE account 2 (locks row 2) A->>DB: UPDATE account 2 Note over A,DB: A waits for B's lock on row 2 B->>DB: UPDATE account 1 Note over B,DB: B waits for A's lock on row 1 DB-->>B: ERROR deadlock detected, B is rolled back DB-->>A: A's update goes through, then A commitsHere A moves money from account 1 to account 2, while B moves money the other way. Each locks its first row, and each then waits for the row the other one holds. You can reproduce it in two sessions: -- 1. Session A BEGIN; UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- locks row 1 -- 2. Session B BEGIN; UPDATE accounts SET balance = balance - 20 WHERE id = 2; -- locks row 2 -- 3. Session A UPDATE accounts SET balance = balance + 10 WHERE id = 2; -- waits for B -- 4. Session B UPDATE accounts SET balance = balance + 20 WHERE id = 1; -- waits for A: deadlockYou don’t need explicit locking for this. PostgreSQL’s manual notes that deadlocks can happen from ordinary row locks, even if you never ask for a lock. A long wait is not the same thing as a deadlock. A transaction stuck behind a slow one is just waiting, and it will continue when the slow one commits. A deadlock is a cycle, and nothing inside it can ever finish. How databases break a deadlock Databases keep track of who is waiting for whom. Transactions are the nodes, each “waits for” is an arrow, and a cycle means deadlock. Cycles can involve more than two transactions. flowchart LR ta[\"Transaction A<br/>holds row 1\"] -->|\"waits for row 2\"| tb[\"Transaction B<br/>holds row 2\"] tb -->|\"waits for row 1\"| ta linkStyle 0,1 stroke:#d08000,stroke-width:2px style ta fill:#fff4e0,stroke:#d08000,stroke-width:2px,color:#1a1a1a style tb fill:#fff4e0,stroke:#d08000,stroke-width:2px,color:#1a1a1aThe database breaks the cycle by choosing a victim, rolling it back and releasing its locks. The other transaction then continues, and the victim’s client gets an error. Each database does this a little differently: PostgreSQL doesn’t look for cycles the moment a wait starts, because the check is relatively expensive. It waits deadlock_timeout first, which is one second by default. The manual says which transaction gets aborted is hard to predict and shouldn’t be relied on. MySQL’s InnoDB detects deadlocks by default and rolls back one transaction, usually the one that changed fewer rows. If detection is turned off, InnoDB relies on innodb_lock_wait_timeout instead. SQLite has no row locks, because it locks the whole database. It can still deadlock when a connection holding a read lock tries to write. According to its documentation, when waiting could deadlock, SQLite returns SQLITE_BUSY immediately instead of waiting. A demo below shows this. What your application sees: Database Error Code PostgreSQL ERROR: deadlock detected SQLSTATE 40P01 MySQL (InnoDB) Deadlock found when trying to get lock; try restarting transaction Error 1213, SQLSTATE 40001 SQLite database is locked, returned immediately SQLITE_BUSY Don’t confuse a deadlock with a lock wait timeout. MySQL’s error 1205 means a transaction waited the full innodb_lock_wait_timeout (50 seconds by default) for a lock held by a slow or stuck transaction. There was no cycle, and by default only the statement is rolled back. A deadlock (1213) rolls back the whole transaction. Four patterns behind most deadlocks Opposite lock order. Two transactions touch the same rows in different orders, like the transfers above. Lock upgrades. A transaction reads a row, which takes a shared lock, and later updates it, which needs a stronger one. If two transactions do this to the same row, each waits for the other’s shared lock to go away. Locking too much. An UPDATE without a useful index can scan and lock many rows, which makes overlaps far more likely. MySQL’s manual advises indexing the columns used in SELECT ... FOR UPDATE and UPDATE ... WHERE. Holding locks too long. Slow work inside a transaction, such as a network call or waiting for a user, keeps locks held and widens the window for an overlap. PostgreSQL waits indefinitely for a lock as long as no deadlock exists. The four fixes below follow the same order. Fix 1: Lock in a consistent order PostgreSQL’s manual says the best defense is to make sure all applications acquire locks on multiple objects in a consistent order. Its own deadlock example adds that if both transactions had updated the rows in the same order, no deadlock would have occurred. sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: Lock row 1 (the lower id first) B->>DB: Lock row 1 (the lower id first) Note over B,DB: B waits for A, but A never needs anything from B A->>DB: Lock row 2, update both rows, COMMIT DB-->>B: Row 1 lock granted B->>DB: Lock row 2, update both rows, COMMITB still waits, but A never needs anything from B, so there is no cycle. B simply continues when A commits. The easiest rule is to sort by primary key. For a transfer, lock the lower account id first, whichever direction the money moves: BEGIN; SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE; -- locks the rows in id order UPDATE accounts SET balance = balance - 10 WHERE id = 1; UPDATE accounts SET balance = balance + 10 WHERE id = 2; COMMIT;The same idea applies across tables. MySQL’s manual recommends using the same order of operations in every transaction that updates multiple tables. To measure the effect, I ran four threads making 400 transfers in total between 8 accounts, with one lock per account. The locks stand in for row locks, because I couldn’t run PostgreSQL or MySQL here. A wait of more than 20 ms for the second lock counted as a deadlock, and the thread gave up and retried after a short random delay: Lock order Deadlocks per run (median of 5 runs) Range From account, then to account 19 14 to 23 Sorted by account id 0 0 to 0 Fix 2: Take the strongest lock first PostgreSQL’s manual says the first lock a transaction takes on an object should be the strongest mode it will need. For row work, that means locking the row for update before you read it, instead of reading first and writing later. sequenceDiagram autonumber participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: SELECT row 1 FOR SHARE B->>DB: SELECT row 1 FOR SHARE A->>DB: UPDATE row 1 Note over A,DB: A waits for B's shared lock to go away B->>DB: UPDATE row 1 Note over B,DB: B waits for A's shared lock to go away DB-->>B: Deadlock detected, B is rolled backIn PostgreSQL and MySQL, make SELECT ... FOR UPDATE your first read. In SQLite, use BEGIN IMMEDIATE, which takes the write lock up front. SQLite makes the problem easy to see, because it refuses to wait when waiting can’t help. This is what my demo printed: B: failed after 0.000 s (database is locked), although it was willing to wait 5 s A: committed, balance = 150 BEGIN IMMEDIATE: both committed, balance = 180; the second one waited 0.33 sWith a plain BEGIN, both connections read first, so both hold a read lock. A’s update gets the write lock, but B’s fails instantly, because A can’t commit until B releases its read lock. Without a retry, B’s deposit of 30 is lost, and the balance ends at 150 instead of 180. With BEGIN IMMEDIATE, the second connection waits its turn, about 0.33 seconds here, and both deposits land. Fix 3: Lock less, and for less time Keep transactions short. Do slow work, such as network calls, before the transaction starts or after it commits. Never wait for user input inside one. Index the columns in your WHERE clauses. A query that can find its rows through an index locks fewer of them. Lock only what you need. Locking isn’t free: PostgreSQL’s manual notes that locking a row can cause a disk write. Let workers skip locked rows. For job queues, SELECT ... FOR UPDATE SKIP LOCKED lets each worker take rows that no other worker holds, instead of waiting for them. Put a ceiling on waiting. PostgreSQL has lock_timeout, and MySQL has innodb_lock_wait_timeout. A wait that ends with an error is easier to handle than one that never ends. Don’t expect the isolation level to solve this. MySQL’s manual says the possibility of deadlocks isn’t affected by the isolation level, because the level changes how reads behave and deadlocks come from writes. Fix 4: Retry, because some will still happen Even correct code gets picked as a victim sometimes. MySQL’s manual is blunt: even if your application logic is correct, you must still handle the case where a transaction has to be retried. PostgreSQL’s manual says the same for deadlocks you can’t prevent. The error message tells you what to do. A deadlock rolls back the whole transaction, so you restart from the beginning, not from the statement that failed. flowchart TD begintx[\"Begin the transaction\"] --> work[\"Run the statements and commit\"] work --> outcome{\"Deadlock error?\"} outcome -->|\"No\"| finished[\"Finished\"] outcome -->|\"Yes: 40P01 or MySQL 1213\"| attempts{\"Attempts left?\"} attempts -->|\"Yes\"| pause[\"Wait a random, growing delay\"] pause --> begintx attempts -->|\"No\"| surface[\"Report the failure\"] style pause fill:#e6f0fa,stroke:#2b7de0,stroke-width:2px,color:#1a1a1aThe helper is short: import random, time RETRYABLE = {'40P01', '40001'} # PostgreSQL deadlock_detected and serialization_failure; MySQL error 1213 also reports 40001 class DatabaseError(Exception): def __init__(self, message, sqlstate): super().__init__(message) self.sqlstate = sqlstate def run_transaction(work, max_attempts=5, base=0.05, cap=2.0, sleep=time.sleep): for attempt in range(1, max_attempts + 1): try: return work() # work() begins, runs and commits one whole transaction except DatabaseError as err: if err.sqlstate not in RETRYABLE or attempt == max_attempts: raise sleep(random.uniform(0, min(cap, base * 2 ** attempt))) # random, growing delayMap your driver’s exception to the SQLSTATE, then follow these rules: Retry the whole transaction, never only the failed statement. Make the work safe to repeat. Don’t send an email or call a payment API inside the transaction. Do it after the commit, or make it idempotent. Cap the attempts and randomize the delay, so two victims don’t collide again at the same moment. Log every deadlock. A rising count points at a pattern you can fix. Finding deadlocks in production PostgreSQL logs every deadlock. The message names the processes involved, and the server log shows their statements: ERROR: deadlock detected DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67890. Process 67890 waits for ShareLock on transaction 12345; blocked by process 12345. HINT: See server log for query details.These tools help: PostgreSQL: log_lock_waits = on logs waits longer than deadlock_timeout, the deadlocks column in pg_stat_database counts them per database, and the pg_locks view shows who is waiting right now. MySQL: SHOW ENGINE INNODB STATUS shows the most recent deadlock, and innodb_print_all_deadlocks writes every one to the error log. When you read a report, look for the two statements and the order in which each one took its locks. That order is nearly always the cause. Measure it yourself I couldn’t run PostgreSQL or MySQL in my sandbox, so the SQL above follows their documentation, and the experiments use Python threads and SQLite. The first script produced the lock-order table. Its numbers vary slightly between runs, because thread timing does: import random, statistics, threading, time ACCOUNTS, THREADS, TRANSFERS = 8, 4, 100 # 4 workers, each making 100 transfers between 8 accounts locks = [threading.Lock() for _ in range(ACCOUNTS)] def transfer(a, b, ordered, stats, rng): first, second = sorted((a, b)) if ordered else (a, b) # ordered: always lock the lower id first while True: locks[first].acquire() time.sleep(0.0005) # some work while holding the first lock if locks[second].acquire(timeout=0.02): # waiting this long almost certainly means deadlock time.sleep(0.0005) locks[second].release() locks[first].release() return locks[first].release() # give up, as a database does with its victim stats['deadlocks'] += 1 time.sleep(rng.uniform(0, 0.01)) # random delay, then retry the transfer def worker(seed, ordered, stats): rng = random.Random(seed) for _ in range(TRANSFERS): a, b = rng.sample(range(ACCOUNTS), 2) # from-account, to-account transfer(a, b, ordered, stats, rng) def run(ordered): stats = {'deadlocks': 0} start = time.perf_counter() threads = [threading.Thread(target=worker, args=(i, ordered, stats)) for i in range(THREADS)] for t in threads: t.start() for t in threads: t.join() return stats['deadlocks'], time.perf_counter() - start if __name__ == '__main__': print(f'{\"lock order\":26} {\"deadlocks (median of 5 runs)\":>28} {\"range\":>9} {\"seconds\":>8}') for name, ordered in (('as given (from, then to)', False), ('sorted by account id', True)): runs = [run(ordered) for _ in range(5)] d = [r[0] for r in runs] print(f'{name:26} {statistics.median(d):>28.0f} {min(d):>4}-{max(d):<4} {statistics.median(r[1] for r in runs):>8.1f}') print('transfers per run:', THREADS * TRANSFERS)The second script reproduces the SQLite lock-upgrade deadlock and its fix, and its output is deterministic: import os, sqlite3, threading, time DB = 'upgrade.db' def fresh(): 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 connect(): return sqlite3.connect(DB, timeout=5, isolation_level=None) # wait up to 5 s for locks, manual transactions def balance(): return connect().execute('SELECT balance FROM accounts WHERE id = 1').fetchone()[0] # 1. read first, write later (a plain BEGIN): both connections hold a read lock, then both want to write fresh() a, b = connect(), connect() for con in (a, b): con.execute('BEGIN') con.execute('SELECT balance FROM accounts WHERE id = 1').fetchone() a.execute('UPDATE accounts SET balance = balance + 50 WHERE id = 1') # A gets the write lock started = time.perf_counter() try: b.execute('UPDATE accounts SET balance = balance + 30 WHERE id = 1') # B asks for it too print('B: succeeded') except sqlite3.OperationalError as err: print(f'B: failed after {time.perf_counter() - started:.3f} s ({err}), although it was willing to wait 5 s') b.execute('ROLLBACK') # B gives way, like a deadlock victim a.execute('COMMIT') # now A can commit print('A: committed, balance =', balance()) # 2. take the write lock first (BEGIN IMMEDIATE): the second connection simply waits its turn fresh() waited = {} def deposit(amount, delay): time.sleep(delay) con = connect() t = time.perf_counter() con.execute('BEGIN IMMEDIATE') # wait here, before reading anything waited[amount] = time.perf_counter() - t current = con.execute('SELECT balance FROM accounts WHERE id = 1').fetchone()[0] time.sleep(0.3) # some work inside the transaction con.execute('UPDATE accounts SET balance = ? WHERE id = 1', (current + amount,)) con.execute('COMMIT') threads = [threading.Thread(target=deposit, args=(50, 0.0)), threading.Thread(target=deposit, args=(30, 0.05))] for t in threads: t.start() for t in threads: t.join() print(f'BEGIN IMMEDIATE: both committed, balance = {balance()}; the second one waited {waited[30]:.2f} s')Wrapping up Deadlocks come from a simple pattern: two transactions each hold one lock and wait for another. A consistent lock order and the strongest lock first prevent most of them. Short transactions and good indexes make the rest rarer. For the ones that remain, let your application retry the whole transaction when the database picks it as the victim. Frequently Asked Questions What is a database deadlock? A deadlock occurs when two (or more) transactions each hold a lock that the other needs, forming a cycle so none can proceed. How do PostgreSQL, MySQL, and SQLite detect and handle deadlocks? PostgreSQL waits a deadlock_timeout (default 1 s) then chooses a victim to roll back; MySQL InnoDB detects deadlocks immediately and rolls back the transaction that changed fewer rows (or uses innodb_lock_wait_timeout if detection is off); SQLite locks the whole database and returns SQLITE_BUSY immediately when a wait could deadlock. What are the common patterns that cause deadlocks? The main patterns are opposite lock order, lock upgrades (shared to exclusive), locking too many rows (e.g., missing indexes), and holding locks for too long due to slow work inside a transaction. How can I prevent deadlocks by using a consistent lock order? Acquire locks on multiple rows or tables in a deterministic order, such as sorting by primary key, so all transactions request resources in the same sequence and cannot form a cycle. Why should I acquire the strongest lock first, and how does it work in SQLite? Taking the strongest lock (e.g., SELECT … FOR UPDATE) first avoids a situation where two transactions hold shared locks and then block each other when upgrading to exclusive; in SQLite you can use BEGIN IMMEDIATE to obtain the write lock up front, causing the second connection to wait instead of failing instantly. What practices help reduce deadlocks by locking less and for less time? Keep transactions short, perform slow work outside them, index columns used in WHERE clauses, lock only needed rows, use SKIP LOCKED for job queues, and set lock timeout limits (PostgreSQL lock_timeout, MySQL innodb_lock_wait_timeout). How should my application handle deadlock errors? Treat deadlock errors as retryable: roll back the whole transaction, wait a random growing delay, and retry the transaction from the beginning, limiting the number of attempts and ensuring the work is idempotent. How can I find deadlocks in production for PostgreSQL and MySQL? PostgreSQL logs deadlocks when log_lock_waits is on and provides details in pg_stat_database and pg_locks; MySQL shows the latest deadlock with SHOW ENGINE INNODB STATUS and can log all deadlocks with innodb_print_all_deadlocks. What is the difference between a deadlock and a lock wait timeout? A deadlock is a cycle of waiting transactions that cannot proceed, causing the database to abort one victim; a lock wait timeout occurs when a transaction waits longer than the configured timeout for a lock held by another transaction, without a cycle, and usually only the statement is rolled back. How does the retry logic for deadlocks work in the provided example? The example catches DatabaseError, checks if the SQLSTATE is retryable (e.g., 40P01, 40001), and if attempts remain, sleeps a random time that grows exponentially (capped), then re‑executes the whole transaction.","contentHash":"sha256:65221f89f097ca303e4cca2deb10e2a457bef951fef05f5977e53e732f797cc5","authorName":"Arpan Singh","authorUrl":"https://zyvop.com/author/arpan","authorSameAs":[],"category":"Databases","tags":["postgresql","concurrency","SQL","databases","MySQL"],"audience":"Software engineers and developers building applications with Databases","tone":"Instructional, practical, code-first","readingTimeMinutes":13,"wordCount":3330,"faqs":[{"question":"What is a database deadlock?","answer":"A deadlock occurs when two (or more) transactions each hold a lock that the other needs, forming a cycle so none can proceed."},{"question":"How do PostgreSQL, MySQL, and SQLite detect and handle deadlocks?","answer":"PostgreSQL waits a deadlock_timeout (default 1 s) then chooses a victim to roll back; MySQL InnoDB detects deadlocks immediately and rolls back the transaction that changed fewer rows (or uses innodb_lock_wait_timeout if detection is off); SQLite locks the whole database and returns SQLITE_BUSY immediately when a wait could deadlock."},{"question":"What are the common patterns that cause deadlocks?","answer":"The main patterns are opposite lock order, lock upgrades (shared to exclusive), locking too many rows (e.g., missing indexes), and holding locks for too long due to slow work inside a transaction."},{"question":"How can I prevent deadlocks by using a consistent lock order?","answer":"Acquire locks on multiple rows or tables in a deterministic order, such as sorting by primary key, so all transactions request resources in the same sequence and cannot form a cycle."},{"question":"Why should I acquire the strongest lock first, and how does it work in SQLite?","answer":"Taking the strongest lock (e.g., SELECT … FOR UPDATE) first avoids a situation where two transactions hold shared locks and then block each other when upgrading to exclusive; in SQLite you can use BEGIN IMMEDIATE to obtain the write lock up front, causing the second connection to wait instead of failing instantly."},{"question":"What practices help reduce deadlocks by locking less and for less time?","answer":"Keep transactions short, perform slow work outside them, index columns used in WHERE clauses, lock only needed rows, use SKIP LOCKED for job queues, and set lock timeout limits (PostgreSQL lock_timeout, MySQL innodb_lock_wait_timeout)."},{"question":"How should my application handle deadlock errors?","answer":"Treat deadlock errors as retryable: roll back the whole transaction, wait a random growing delay, and retry the transaction from the beginning, limiting the number of attempts and ensuring the work is idempotent."},{"question":"How can I find deadlocks in production for PostgreSQL and MySQL?","answer":"PostgreSQL logs deadlocks when log_lock_waits is on and provides details in pg_stat_database and pg_locks; MySQL shows the latest deadlock with SHOW ENGINE INNODB STATUS and can log all deadlocks with innodb_print_all_deadlocks."},{"question":"What is the difference between a deadlock and a lock wait timeout?","answer":"A deadlock is a cycle of waiting transactions that cannot proceed, causing the database to abort one victim; a lock wait timeout occurs when a transaction waits longer than the configured timeout for a lock held by another transaction, without a cycle, and usually only the statement is rolled back."},{"question":"How does the retry logic for deadlocks work in the provided example?","answer":"The example catches DatabaseError, checks if the SQLSTATE is retryable (e.g., 40P01, 40001), and if attempts remain, sleeps a random time that grows exponentially (capped), then re‑executes the whole transaction."}],"primaryTopic":"Databases","publishedAt":"2026-11-14T08:12:21.499Z","updatedAt":"2026-11-14T08:12:21.499Z","canonicalUrl":"https://zyvop.com/database-deadlocks-explained-why-they-happen-and-how-to-prevent-them-ndkxl"}