Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- 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.

SQL SQLite takes one write lock (BEGIN IMMEDIATE) ยท database: bookstore

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.

SQL Optimistic locking with a version column

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) and LOCK_TIMEOUT (SQL Server).
  • Prefer an atomic UPDATE ... SET x = x - 1 over 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.