SQL Reference ยท Transactions and Performance
SQL locking and deadlocks
How SQL databases lock data: shared and exclusive locks, row and table locks, SELECT FOR UPDATE, SKIP LOCKED, optimistic locking, deadlocks and SQLite's busy errors.
When many sessions change the same data, the database must stop them from corrupting each other. Locks do that: a session that wants to read or write something first acquires a lock, and conflicting sessions wait until it is released. Locks are normally released at COMMIT or ROLLBACK, which is one more reason to keep transactions short.
You rarely manage locks by hand, because ordinary UPDATE and DELETE statements take the locks they need. But understanding them explains why queries wait, why deadlocks happen, and how to reserve rows deliberately.
Kinds of locks
| Lock | Meaning |
|---|---|
| Shared (read) | Many sessions can hold it together; blocks writers. |
| Exclusive (write) | Only one session holds it; blocks other readers that lock, and all writers. |
| Row lock | Covers individual rows. Best concurrency. Used by PostgreSQL, MySQL (InnoDB), Oracle and SQL Server. |
| Table lock | Covers a whole table. Used by DDL and some bulk operations. |
| Database lock | SQLite locks the whole database file: many readers or one writer. |
Databases may escalate many small locks into a bigger one (SQL Server does this) to save memory.
Reserving rows with SELECT ... FOR UPDATE
To read a row and then change it safely, lock it when you read it. Other sessions that try to change it wait until you finish.
-- PostgreSQL, MySQL, Oracle BEGIN; SELECT stock FROM books WHERE id = 1 FOR UPDATE; -- decide in application code, then: UPDATE books SET stock = stock - 1 WHERE id = 1; COMMIT; -- SQL Server SELECT stock FROM books WITH (UPDLOCK, ROWLOCK) WHERE id = 1;
Variants control waiting: NOWAIT fails immediately if the row is locked, and SKIP LOCKED ignores locked rows, which is how job queues let many workers each claim a different task. SQLite has no FOR UPDATE; use BEGIN IMMEDIATE to take the write lock for the whole database up front.
Press Run (or Ctrl + Enter) to see the result here.
Optimistic locking: no lock, just a check
Instead of locking while you think, read a version number, and write only if it is unchanged. If another session got there first, your update matches zero rows and you retry. This works in every database and is common in web applications.
Press Run (or Ctrl + Enter) to see the result here.
Editor B updated zero rows, which tells the application "someone else changed this; reload and try again" instead of silently overwriting A's work.
Deadlocks
A deadlock happens when two transactions each hold a lock the other needs. Session 1 locks row A and then wants row B, while session 2 has locked row B and wants row A. Neither can continue. Databases detect this and abort one transaction (the "victim") with an error; the other proceeds. PostgreSQL reports deadlock detected, MySQL Deadlock found when trying to get lock, SQL Server error 1205, and Oracle ORA-00060.
To prevent deadlocks:
- Always touch rows in the same order (for example, sort ids before updating).
- Keep transactions short and avoid user interaction inside them.
- Lock everything you need at the start.
- Index the columns you filter on so statements lock fewer rows.
- Retry the victim transaction from the beginning.
SQLite and busy errors
SQLite allows many readers or one writer. If another connection holds the write lock, you get database is locked (SQLITE_BUSY). Set a wait time with PRAGMA busy_timeout = 5000; so SQLite retries for up to 5 seconds, and use WAL mode (PRAGMA journal_mode = WAL;) so readers do not block the writer.
Dialect differences
| Database | Row-level locking | Lock a row on read | Notes |
|---|---|---|---|
| SQLite | No (database lock) | BEGIN IMMEDIATE |
busy_timeout, WAL mode. |
| PostgreSQL | Yes | FOR UPDATE, FOR SHARE, NOWAIT, SKIP LOCKED |
Readers never block writers (MVCC). Advisory locks available. |
| MySQL (InnoDB) | Yes, plus gap locks | FOR UPDATE, FOR SHARE, NOWAIT, SKIP LOCKED (8.0+) |
Gap locks at REPEATABLE READ can surprise you. |
| SQL Server | Yes, with escalation | WITH (UPDLOCK, HOLDLOCK) |
READPAST skips locked rows. |
| Oracle | Yes | FOR UPDATE [NOWAIT / SKIP LOCKED / WAIT n] |
Readers never block writers. |
Usage notes
- A statement that waits too long can time out; the settings are
lock_timeout(PostgreSQL),innodb_lock_wait_timeout(MySQL) andLOCK_TIMEOUT(SQL Server). - Prefer an atomic
UPDATE ... SET x = x - 1over read-then-write; it needs no explicit lock. - Foreign key checks can take locks on parent rows too.
- Lock waits and deadlocks are usually symptoms of long transactions or missing indexes.