Skip to main content

SQL Locking Principles for Concurrent SQL Transactions

When multiple transactions try to access the same data simultaneously, the database needs a traffic control system to prevent chaos. That system is locking. Without locks, two transactions could modify the same row at the exact same instant, producing corrupted data that reflects neither transaction's intent. Locks ensure orderly access by making transactions wait their turn when they conflict.

Understanding SQL locking is essential for any developer working with concurrent database access. Poorly managed locks lead to performance bottlenecks, mysterious application hangs, and the dreaded deadlock. Well-managed locks keep your application fast and your data correct.

This guide explains how row-level and table-level locks work, how to use SELECT ... FOR UPDATE for explicit locking, and how deadlocks happen and how to prevent them. Every concept includes practical examples with step-by-step timelines showing exactly what happens when two transactions collide.

Why Locking Exists​

Consider two airline agents booking the last seat on a flight simultaneously:

Agent A: SELECT available_seats FROM flights WHERE id = 101;  → Returns 1
Agent B: SELECT available_seats FROM flights WHERE id = 101;  → Returns 1
Agent A: UPDATE flights SET available_seats = 0 WHERE id = 101;  → Success
Agent B: UPDATE flights SET available_seats = 0 WHERE id = 101;  → Success??

Both agents saw 1 seat, both "sold" it. The airline just double-booked a seat that does not exist. Without locking, both transactions ran as if the other did not exist.

With proper locking, Agent B's SELECT or UPDATE would be forced to wait until Agent A's transaction completes. Agent B would then see 0 available seats and correctly refuse the booking.

The Sample Data​

We will use these tables throughout the guide:

accounts table:

| id | owner   | balance  |
|----|---------|----------|
| 1  | Alice   | 5000.00  |
| 2  | Bob     | 3000.00  |
| 3  | Charlie | 1500.00  |

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    owner VARCHAR(50) NOT NULL,
    balance DECIMAL(12,2) NOT NULL
);
INSERT INTO accounts (id, owner, balance) VALUES
(1, 'Alice',   5000.00),
(2, 'Bob',     3000.00),
(3, 'Charlie', 1500.00);

products table:

idnamestockprice
101Widget Pro1079.99
102Gadget Max5149.99
103Desk Lamp245.00
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    stock INT NOT NULL,
    price DECIMAL(10,2) NOT NULL
);
INSERT INTO products (id, name, stock, price) VALUES
(101, 'Widget Pro', 10, 79.99),
(102, 'Gadget Max', 5, 149.99),
(103, 'Desk Lamp',  2, 45.00);

jobs table:

idpayloadstatusassigned_tocreated_at
1Send email #1pendingNULL2024-01-01 08:00:00
2Send email #2pendingNULL2024-01-01 08:01:00
3Send email #3pendingNULL2024-01-01 08:02:00
4Process reportpendingNULL2024-01-01 08:03:00
CREATE TABLE jobs (
    id INT PRIMARY KEY,
    payload VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL,
    assigned_to INT NULL,       -- Can link to a user/worker if desired
    created_at DATETIME NOT NULL
);
INSERT INTO jobs (id, payload, status, assigned_to, created_at) VALUES
(1, 'Send email #1',  'pending', NULL, '2024-01-01 08:00:00'),
(2, 'Send email #2',  'pending', NULL, '2024-01-01 08:01:00'),
(3, 'Send email #3',  'pending', NULL, '2024-01-01 08:02:00'),
(4, 'Process report', 'pending', NULL, '2024-01-01 08:03:00');

Lock Types: Shared vs Exclusive​

Before discussing row-level and table-level locks, you need to understand the two fundamental categories of locks.

Shared Locks (Read Locks)​

A shared lock allows multiple transactions to read the same data simultaneously. If Transaction A holds a shared lock on a row, Transaction B can also acquire a shared lock on the same row and read it. However, neither can modify the row until all shared locks are released.

Think of it like a library book on the reading table. Multiple people can read it at the same time, but nobody can take it home (modify it) until everyone is done reading.

Exclusive Locks (Write Locks)​

An exclusive lock gives a single transaction sole access to the data for both reading and writing. If Transaction A holds an exclusive lock, Transaction B must wait until A releases it before doing anything with that data.

Think of it like checking a book out of the library. Once someone checks it out, nobody else can read or borrow it until it is returned.

Lock Compatibility Matrix​

Shared Lock RequestedExclusive Lock Requested
No Lock Held✅ Granted✅ Granted
Shared Lock Held✅ Granted (compatible)❌ Must wait
Exclusive Lock Held❌ Must wait❌ Must wait

Key takeaway: readers do not block readers, but writers block everyone and everyone blocks writers.

MVCC Changes the Rules

Databases that use Multi-Version Concurrency Control (MVCC), such as PostgreSQL and MySQL InnoDB, modify the traditional locking behavior significantly. In MVCC, readers never block writers and writers never block readers for normal operations. Instead of waiting for locks, readers simply see an older snapshot of the data. Explicit locks (SELECT ... FOR UPDATE) still cause blocking because they explicitly request locking behavior.

Row-Level vs Table-Level Locks​

Locks can be applied at different granularities. The two most important are row-level and table-level.

Row-Level Locks​

A row-level lock locks only the specific row(s) being accessed. Other transactions can freely read and modify other rows in the same table. This provides the highest concurrency because transactions operating on different rows never interfere.

Transaction A locks row id=1     Transaction B locks row id=2
──────────────────────────────   ──────────────────────────────
BEGIN;                           BEGIN;
UPDATE accounts                  UPDATE accounts
SET balance = 4500               SET balance = 2500
WHERE id = 1;                   WHERE id = 2;
-- Locks ONLY row 1              -- Locks ONLY row 2
-- No conflict!                  -- No conflict!
COMMIT;                          COMMIT;

Both transactions proceed simultaneously because they lock different rows.

When the same row is involved:

Transaction A                    Transaction B
─────────────                    ─────────────
BEGIN;                           
UPDATE accounts                  
SET balance = 4500               
WHERE id = 1;                   
-- Exclusive lock on row 1       
                                 BEGIN;
                                 UPDATE accounts
                                 SET balance = 4000
                                 WHERE id = 1;
                                 -- ⏳ BLOCKED! Waiting for A
                                 --    to release lock on row 1

COMMIT;                          
-- Lock on row 1 released        
                                 -- ✅ Unblocked! B can proceed
                                 -- B sees balance = 4500 (A's committed value)
                                 -- B sets balance = 4000
                                 COMMIT;

Transaction B is blocked only while Transaction A holds the lock on row 1. All other rows in the table remain accessible.

Which Operations Acquire Row-Level Locks?​

OperationLock TypeAutomatic?
SELECT (normal)No lock (MVCC) or shared lock (SQL Server default)Depends on database
SELECT ... FOR UPDATEExclusive row lockExplicit
SELECT ... FOR SHAREShared row lockExplicit
UPDATEExclusive row lockAutomatic
DELETEExclusive row lockAutomatic
INSERTExclusive row lock on new rowAutomatic

Table-Level Locks​

A table-level lock locks the entire table, preventing other transactions from reading or writing any row. This is a much coarser granularity and significantly reduces concurrency, but it is sometimes necessary for operations that affect the whole table.

-- PostgreSQL: Explicit table lock
BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- Entire table is locked. No other transaction can read or write
-- any row until this transaction commits or rolls back.
UPDATE accounts SET balance = balance * 1.05;  -- 5% raise for everyone
COMMIT;
-- MySQL: Explicit table lock
LOCK TABLES accounts WRITE;
UPDATE accounts SET balance = balance * 1.05;
UNLOCK TABLES;
-- SQL Server: Table-level hint
BEGIN TRANSACTION;
SELECT * FROM accounts WITH (TABLOCKX);
-- Exclusive lock on entire table
UPDATE accounts SET balance = balance * 1.05;
COMMIT TRANSACTION;

When Table-Level Locks Are Used​

SituationWhy
DDL operations (ALTER TABLE, DROP TABLE)Must prevent all access while changing structure
Bulk operations (mass updates, truncation)Row-by-row locking would be too expensive
LOCK TABLE commandsExplicitly requested by the developer
Lock escalation (SQL Server)Too many row locks; database upgrades to a table lock for efficiency
Lock Escalation in SQL Server

SQL Server automatically escalates row-level locks to a table-level lock when a single transaction acquires too many row locks (typically around 5,000). This is a performance optimization for SQL Server's lock manager, but it can unexpectedly block other transactions that were previously running fine.

-- SQL Server: This might cause lock escalation
BEGIN TRANSACTION;
UPDATE orders SET status = 'archived'
WHERE order_date < '2023-01-01';
-- If this affects >5000 rows, SQL Server may escalate to a table lock
-- blocking ALL other access to the orders table
COMMIT TRANSACTION;

To prevent escalation, process large updates in batches:

-- Process in batches of 1000 to avoid lock escalation
WHILE 1 = 1
BEGIN
    UPDATE TOP (1000) orders
    SET status = 'archived'
    WHERE order_date < '2023-01-01' AND status != 'archived';

    IF @@ROWCOUNT = 0 BREAK;  -- No more rows to process
END

Row-Level vs Table-Level Comparison​

AspectRow-Level LockTable-Level Lock
GranularityIndividual rowEntire table
ConcurrencyHigh (other rows accessible)Low (entire table blocked)
OverheadHigher (one lock per row)Lower (one lock total)
Use caseNormal OLTP operationsDDL, bulk operations, explicit locking
Default for DMLYes (most databases)No (must be explicit)

SELECT ... FOR UPDATE​

SELECT ... FOR UPDATE is the most important explicit locking command for application developers. It reads rows and locks them for the duration of the transaction, preventing other transactions from modifying or locking those same rows.

Why Normal SELECT Is Not Enough​

A normal SELECT in MVCC databases does not acquire any locks. This means the data can change between your SELECT and a subsequent UPDATE:

Transaction A                    Transaction B
─────────────                    ─────────────
BEGIN;

SELECT balance FROM accounts
WHERE id = 1;
-- Returns: 5000
-- NO LOCK acquired
                                 BEGIN;
                                 UPDATE accounts
                                 SET balance = balance - 3000
                                 WHERE id = 1;
                                 COMMIT;
                                 -- Balance is now 2000

-- A thinks balance is 5000
UPDATE accounts
SET balance = balance - 4000
WHERE id = 1;
-- Balance becomes 2000 - 4000 = -2000!
-- Without a CHECK constraint, this succeeds
-- and the account is overdrawn!
COMMIT;

Transaction A read 5000, decided a $4000 withdrawal was safe, but by the time it executed the withdrawal, the balance had already been reduced to $2000 by Transaction B.

SELECT FOR UPDATE Prevents This​

Transaction A                    Transaction B
─────────────                    ─────────────
BEGIN;

SELECT balance FROM accounts
WHERE id = 1
FOR UPDATE;
-- Returns: 5000
-- EXCLUSIVE LOCK acquired on row 1

                                 BEGIN;
                                 UPDATE accounts
                                 SET balance = balance - 3000
                                 WHERE id = 1;
                                 -- ⏳ BLOCKED!
                                 -- Waiting for A's lock on row 1

-- A safely uses the balance value
-- knowing nobody can change it
UPDATE accounts
SET balance = balance - 4000
WHERE id = 1;
-- Balance: 5000 - 4000 = 1000 ✅

COMMIT;
-- Lock released

                                 -- ✅ Unblocked! B proceeds
                                 -- Sees balance = 1000
                                 -- Sets balance = 1000 - 3000 = -2000
                                 -- (Would fail if CHECK constraint exists)
                                 COMMIT;

With FOR UPDATE, Transaction A locks the row immediately upon reading. Transaction B must wait. By the time B can proceed, A has already committed and B sees the updated balance.

Syntax Across Databases​

-- PostgreSQL / MySQL / Oracle
SELECT * FROM accounts
WHERE id = 1
FOR UPDATE;

-- SQL Server (uses hints)
SELECT * FROM accounts WITH (UPDLOCK, ROWLOCK)
WHERE id = 1;

-- SQLite (uses BEGIN IMMEDIATE or BEGIN EXCLUSIVE for transaction-level locking)
BEGIN IMMEDIATE;
SELECT * FROM accounts WHERE id = 1;

FOR UPDATE Variants​

Different databases offer variations that provide finer control over locking behavior:

FOR UPDATE NOWAIT​

Instead of waiting for a lock, immediately fail if the row is already locked:

BEGIN;
SELECT * FROM accounts
WHERE id = 1
FOR UPDATE NOWAIT;
-- If row 1 is locked by another transaction:
-- ERROR: could not obtain lock on row (PostgreSQL)
-- ERROR: Lock wait timeout exceeded (MySQL, if configured)

This is useful when you would rather fail fast and retry or try a different approach instead of waiting indefinitely.

-- Practical use: try to lock, handle failure gracefully
BEGIN;

-- Try to acquire the lock
SELECT balance FROM accounts
WHERE id = 1
FOR UPDATE NOWAIT;

-- If we get here, we have the lock
-- If an error occurs, catch it in application code and handle it

COMMIT;

FOR UPDATE SKIP LOCKED​

Skip rows that are already locked by other transactions and return only the unlocked rows:

BEGIN;
SELECT * FROM products
WHERE stock > 0
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Returns the first UNLOCKED row with stock > 0
-- If row 101 is locked by another transaction, returns row 102 instead
COMMIT;

This is extremely valuable for job queue patterns, which we will cover in detail below.

FOR SHARE (FOR KEY SHARE)​

Acquire a shared lock instead of an exclusive lock. Multiple transactions can hold shared locks simultaneously, but none can acquire an exclusive lock until all shared locks are released:

-- Transaction A: shared lock
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Others can also SELECT FOR SHARE on this row
-- But nobody can UPDATE or DELETE until A commits

-- Transaction B: also shared lock (succeeds!)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- ✅ Compatible with A's shared lock

-- Transaction C: exclusive lock (blocked!)
BEGIN;
UPDATE accounts SET balance = 4000 WHERE id = 1;
-- ⏳ BLOCKED until both A and B release their shared locks
ClauseLock TypeCompatible WithUse Case
FOR UPDATEExclusiveNothingRead-then-write operations
FOR SHARESharedOther FOR SHAREEnsure data does not change while reading
FOR UPDATE NOWAITExclusive (fail fast)NothingLock with instant failure if unavailable
FOR UPDATE SKIP LOCKEDExclusive (skip busy rows)NothingJob queues, work distribution
FOR KEY SHARE (PostgreSQL)Weakest shared lockMost operations except key updatesForeign key checks
MySQL Equivalent Syntax

MySQL uses slightly different syntax:

-- MySQL: Shared lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Or older syntax:
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;

-- MySQL: Exclusive lock with NOWAIT
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- MySQL: Exclusive lock with SKIP LOCKED
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;

Practical Locking Patterns​

Pattern 1: Safe Balance Transfer​

The classic example: transfer money between accounts without race conditions.

BEGIN;

-- Lock BOTH accounts to prevent concurrent modifications
-- Always lock in consistent order (by id) to prevent deadlocks
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

-- Now we have exclusive locks on both rows
-- Check sufficient funds
-- (Application code verifies balance >= transfer amount)

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Pattern 2: Inventory Reservation​

Prevent overselling by locking the product row before checking and deducting stock:

BEGIN;

-- Lock the product row
SELECT stock FROM products
WHERE id = 101
FOR UPDATE;
-- Returns: 10

-- Application checks: is stock >= requested quantity?
-- If yes, proceed:

UPDATE products SET stock = stock - 3 WHERE id = 101;
-- Stock: 10 - 3 = 7

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 101, 3);

COMMIT;

If another transaction tries to reserve the same product simultaneously:

Transaction A                    Transaction B
─────────────                    ─────────────
BEGIN;                           BEGIN;

SELECT stock FROM products       
WHERE id = 101                   
FOR UPDATE;                      
-- Returns: 10, locks row        
                                 SELECT stock FROM products
                                 WHERE id = 101
                                 FOR UPDATE;
                                 -- ⏳ BLOCKED (A holds the lock)

UPDATE products                  
SET stock = stock - 3            
WHERE id = 101;                  

COMMIT;                          
                                 -- ✅ Unblocked
                                 -- Returns: 7 (sees A's committed change)
                                 -- B correctly sees only 7 in stock
                                 COMMIT;

Pattern 3: Job Queue with SKIP LOCKED​

Multiple workers process jobs from a shared queue without conflicts:

-- Worker 1
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Returns: (1, 'Send email #1')
-- Locks row 1

UPDATE jobs SET status = 'processing', assigned_to = 'worker-1'
WHERE id = 1;
COMMIT;

-- Process the job...
BEGIN;
UPDATE jobs SET status = 'completed' WHERE id = 1;
COMMIT;
-- Worker 2 (running at the same time)
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Row 1 is locked by Worker 1, so it is SKIPPED
-- Returns: (2, 'Send email #2')
-- Locks row 2

UPDATE jobs SET status = 'processing', assigned_to = 'worker-2'
WHERE id = 2;
COMMIT;

Both workers operate on different jobs without blocking each other. If 10 workers run simultaneously, each picks up a different pending job because SKIP LOCKED automatically avoids contention.

-- Worker 3 (also running at the same time)
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Rows 1 and 2 are locked, skipped
-- Returns: (3, 'Send email #3')
COMMIT;
tip

The SKIP LOCKED pattern is the foundation of high-performance job queues in PostgreSQL. Libraries like pgBoss, Graphile Worker, and Que use this pattern under the hood. It scales excellently because workers never wait for each other.

Pattern 4: Optimistic vs Pessimistic Locking​

There are two philosophies for handling concurrent access. Understanding both helps you choose the right approach.

Pessimistic Locking (Lock First, Then Work)​

Assume conflicts will happen. Lock the data immediately upon reading:

-- Pessimistic: lock the row before doing anything
BEGIN;

SELECT balance FROM accounts
WHERE id = 1
FOR UPDATE;                    -- Lock immediately
-- Returns: 5000

-- Safely work with the value
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

COMMIT;

Pros: Guarantees no conflicts. Simple logic. Cons: Reduces concurrency. Transactions hold locks longer.

Optimistic Locking (Work First, Check at the End)​

Assume conflicts are rare. Read without locking, then check for changes when updating:

-- Optimistic: no lock, use a version check instead
BEGIN;

SELECT balance, version FROM accounts WHERE id = 1;
-- Returns: balance = 5000, version = 3

-- Do calculations without holding a lock...

UPDATE accounts
SET balance = balance - 500,
    version = version + 1
WHERE id = 1 AND version = 3;  -- Only succeeds if version hasn't changed
-- If rows_affected = 0, someone else modified the row → retry!

COMMIT;

Pros: Maximum concurrency. No waiting. Cons: Requires retry logic. Wasted work if conflicts occur.

AspectPessimisticOptimistic
Conflict assumptionConflicts are likelyConflicts are rare
LockingUpfront (FOR UPDATE)None (version check)
ConcurrencyLower (locks held)Higher (no locks)
Retry needed?NoYes (on version mismatch)
Best forHigh-contention data (account balances, inventory)Low-contention data (user profiles, blog posts)
-- Optimistic locking with a timestamp instead of version number
BEGIN;

SELECT balance, updated_at FROM accounts WHERE id = 1;
-- Returns: balance = 5000, updated_at = '2024-01-15 10:30:00'

UPDATE accounts
SET balance = balance - 500,
    updated_at = NOW()
WHERE id = 1
  AND updated_at = '2024-01-15 10:30:00';
-- If rows_affected = 0: conflict detected, retry

COMMIT;

Deadlocks and How to Avoid Them​

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular dependency where none of them can proceed.

How a Deadlock Happens​

Transaction A                    Transaction B
─────────────                    ─────────────
BEGIN;                           BEGIN;

UPDATE accounts                  
SET balance = balance - 100      
WHERE id = 1;                   
-- 🔒 Locks row 1               
                                 UPDATE accounts
                                 SET balance = balance - 200
                                 WHERE id = 2;
                                 -- 🔒 Locks row 2

UPDATE accounts                  
SET balance = balance + 100      
WHERE id = 2;                   
-- ⏳ BLOCKED!                   
-- Waiting for B to release       
-- lock on row 2                 
                                 UPDATE accounts
                                 SET balance = balance + 200
                                 WHERE id = 1;
                                 -- ⏳ BLOCKED!
                                 -- Waiting for A to release
                                 -- lock on row 1

-- 💀 DEADLOCK!
-- A waits for B (needs row 2)
-- B waits for A (needs row 1)
-- Neither can proceed!
    Transaction A ──waits for──→ Row 2 (locked by B)
         ↑                              │
         │                              ↓
    Row 1 (locked by A) ←──waits for── Transaction B

    ← Circular dependency = DEADLOCK →

How Databases Handle Deadlocks​

All major databases have deadlock detectors that automatically identify circular wait dependencies. When a deadlock is detected, the database kills one of the transactions (the "victim") and allows the other to proceed.

-- PostgreSQL deadlock detection output:
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
        blocked by process 5679.
        Process 5679 waits for ShareLock on transaction 5677;
        blocked by process 1234.
HINT: See server log for query details.
-- MySQL deadlock detection output:
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
-- SQL Server deadlock detection output:
Msg 1205, Level 13, State 51
Transaction was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

The victim transaction is rolled back. The surviving transaction completes normally. Your application must be prepared to catch and retry the rolled-back transaction.

Prevention Strategy 1: Consistent Lock Ordering​

The most effective deadlock prevention technique is to always lock resources in the same order across all transactions. If every transaction locks row 1 before row 2, the circular dependency cannot form.

-- BAD: Different lock ordering in different transactions
-- Transaction A: locks 1, then 2
-- Transaction B: locks 2, then 1
-- → DEADLOCK possible!

-- GOOD: Both transactions lock in the same order (by id, ascending)

-- Transaction A: Transfer from account 1 to account 2
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Locks row 1 first, then row 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction B: Transfer from account 2 to account 1
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- ALSO locks row 1 first, then row 2 (same order!)
UPDATE accounts SET balance = balance + 200 WHERE id = 1;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;
COMMIT;

Even though the transfers go in opposite directions, both transactions acquire locks in ascending id order. No circular dependency is possible.

-- General pattern: sort lock targets by primary key
-- Transfer between any two accounts
BEGIN;

-- Determine lock order
-- smaller_id = MIN(from_account, to_account)
-- larger_id  = MAX(from_account, to_account)

SELECT * FROM accounts
WHERE id IN (from_account, to_account)
ORDER BY id
FOR UPDATE;

-- Now safely perform the transfer
UPDATE accounts SET balance = balance - amount WHERE id = from_account;
UPDATE accounts SET balance = balance + amount WHERE id = to_account;

COMMIT;

Prevention Strategy 2: Keep Transactions Short​

The longer a transaction runs, the longer it holds locks, and the greater the chance another transaction needs one of those locks. Short transactions dramatically reduce deadlock probability.

-- BAD: Long transaction with locks held for extended period
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Lock acquired...
-- Application does slow external API call (2 seconds)
-- Lock still held...
-- Application does complex calculation (500ms)
-- Lock still held...
UPDATE accounts SET balance = 4500 WHERE id = 1;
COMMIT;  -- Lock held for ~2.5 seconds total

-- GOOD: Do all preparation OUTSIDE the transaction
-- Application does API call and calculations first

-- Then start a short transaction
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = 4500 WHERE id = 1;
COMMIT;  -- Lock held for ~5ms

Prevention Strategy 3: Lock All Needed Resources at Once​

Instead of acquiring locks one at a time (which creates a window for deadlocks), lock everything you need at the beginning of the transaction:

-- BAD: Acquire locks incrementally
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Lock row 1
-- Window for deadlock: other transaction can lock row 2 here
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Try to lock row 2
COMMIT;

-- GOOD: Lock all needed rows upfront
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Both rows locked immediately, in consistent order
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Prevention Strategy 4: Use Lock Timeouts​

Set a maximum time a transaction will wait for a lock. If the timeout expires, the transaction fails instead of waiting indefinitely (which could eventually deadlock):

-- PostgreSQL: 5-second lock timeout
SET lock_timeout = '5s';

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- If lock cannot be acquired within 5 seconds:
-- ERROR: canceling statement due to lock timeout
COMMIT;
-- MySQL: 10-second lock wait timeout
SET innodb_lock_wait_timeout = 10;

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- If lock cannot be acquired within 10 seconds:
-- ERROR: Lock wait timeout exceeded; try restarting transaction
COMMIT;
-- PostgreSQL: Fail immediately if lock is unavailable
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR (immediately): could not obtain lock on row
COMMIT;

Prevention Strategy 5: Reduce Lock Scope​

Lock only what you need. Avoid locking entire tables when you only need specific rows:

-- BAD: Locks the entire table
BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- GOOD: Locks only the rows you need
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Handling Deadlocks in Application Code​

Since deadlocks can never be completely eliminated (only minimized), your application must handle them gracefully with retry logic:

# Python with psycopg2
import psycopg2
import psycopg2.errors
import time
import random

MAX_RETRIES = 3

def transfer_funds(conn, from_id, to_id, amount):
    for attempt in range(MAX_RETRIES):
        try:
            with conn:
                with conn.cursor() as cur:
                    # Lock in consistent order
                    ids = sorted([from_id, to_id])
                    cur.execute(
                        "SELECT id, balance FROM accounts "
                        "WHERE id = ANY(%s) ORDER BY id FOR UPDATE",
                        (ids,)
                    )
                    
                    cur.execute(
                        "UPDATE accounts SET balance = balance - %s "
                        "WHERE id = %s",
                        (amount, from_id)
                    )
                    cur.execute(
                        "UPDATE accounts SET balance = balance + %s "
                        "WHERE id = %s",
                        (amount, to_id)
                    )
            return  # Success!
            
        except psycopg2.errors.DeadlockDetected:
            conn.rollback()
            if attempt < MAX_RETRIES - 1:
                # Random backoff to reduce chance of immediate re-deadlock
                time.sleep(random.uniform(0.05, 0.2) * (attempt + 1))
                continue
            raise  # Max retries exceeded

        except psycopg2.errors.LockNotAvailable:
            conn.rollback()
            raise  # NOWAIT lock failure, don't retry

The random backoff is important: if two transactions deadlock and both immediately retry with the same timing, they may deadlock again. Random delays break the cycle.

Monitoring Locks​

PostgreSQL: View Current Locks​

-- See all current locks
SELECT
    pid,
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    waitstart
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY pid;
-- See blocked queries and what's blocking them
SELECT
    blocked.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking_activity.query AS blocking_query
FROM pg_locks blocked
JOIN pg_locks blocking
    ON blocked.transactionid = blocking.transactionid
    AND blocked.pid != blocking.pid
JOIN pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE NOT blocked.granted;

MySQL: View Current Locks​

-- See InnoDB lock waits
SELECT
    r.trx_id AS waiting_trx,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- MySQL 8.0+ with performance_schema
SELECT * FROM performance_schema.data_lock_waits;

SQL Server: View Current Locks​

-- See blocked processes
SELECT
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time,
    st.text AS blocked_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) st
WHERE blocked.blocking_session_id != 0;

Killing a Blocking Session​

When a lock-holding transaction is stuck or abandoned, you may need to terminate it:

-- PostgreSQL: Terminate a blocking session
SELECT pg_terminate_backend(pid);  -- pid of the blocking process

-- MySQL: Kill a blocking thread
KILL thread_id;

-- SQL Server: Kill a blocking session
KILL session_id;
caution

Terminating a session rolls back its transaction. Only do this when you are certain the blocking transaction is stuck or abandoned, not just slow.

Common Mistakes to Avoid​

Mistake 1: Locking More Than Necessary​

-- BAD: Locks ALL pending orders just to process one
BEGIN;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- Locks every pending order! Other workers are completely blocked.

-- Only process one order
UPDATE orders SET status = 'processing' WHERE id = 1;
COMMIT;

-- GOOD: Lock only the row you need
BEGIN;
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- Lock one row, skip if busy
UPDATE orders SET status = 'processing' WHERE id = <selected_id>;
COMMIT;

Mistake 2: Forgetting That SELECT FOR UPDATE Requires a Transaction​

-- BAD: FOR UPDATE without an explicit transaction
-- In auto-commit mode, the lock is immediately released after the SELECT
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Lock already released! The next statement is a separate transaction.
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- No protection from concurrent modification

-- GOOD: Wrap in a transaction
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Lock held until COMMIT or ROLLBACK
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;

Mistake 3: Holding Locks During External Operations​

-- BAD: Lock held while calling an external API
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- Lock acquired

-- Call payment gateway (takes 2-5 seconds)
-- Lock still held! Other transactions on this row are blocked!

UPDATE orders SET status = 'paid' WHERE id = 1;
COMMIT;

-- GOOD: Minimize lock duration
-- Do the API call OUTSIDE the transaction
-- Then update the status in a short transaction
BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 1;
COMMIT;

If you must lock during an external call, use lock timeouts to prevent indefinite blocking.

Mistake 4: Inconsistent Lock Ordering Across the Codebase​

# Module A: transfer.py
# Locks sender first, then receiver
def transfer(from_id, to_id, amount):
    lock(from_id)     # Lock sender
    lock(to_id)       # Lock receiver

# Module B: refund.py  
# Locks receiver first, then sender (OPPOSITE ORDER!)
def refund(original_from_id, original_to_id, amount):
    lock(original_to_id)   # Lock original receiver (now sender of refund)
    lock(original_from_id) # Lock original sender (now receiver of refund)

# These two modules can DEADLOCK with each other!

Fix: Establish a project-wide convention for lock ordering (e.g., always lock by ascending primary key) and enforce it through code review or utility functions:

# Shared utility: always locks in consistent order
def lock_accounts(conn, *account_ids):
    sorted_ids = sorted(account_ids)
    cur = conn.cursor()
    cur.execute(
        "SELECT * FROM accounts WHERE id = ANY(%s) ORDER BY id FOR UPDATE",
        (sorted_ids,)
    )
    return cur.fetchall()

Summary​

SQL locking is the mechanism that prevents concurrent transactions from corrupting shared data:

  • Shared locks allow multiple readers. Exclusive locks allow only one writer and block everyone else.
  • Row-level locks (the default for DML) provide high concurrency by locking only the affected rows. Table-level locks block the entire table and are used for DDL and bulk operations.
  • SELECT ... FOR UPDATE explicitly locks rows for the duration of the transaction, preventing other transactions from modifying them. Use it whenever you read data that you plan to update based on what you read.
  • FOR UPDATE NOWAIT fails immediately if the lock is unavailable. FOR UPDATE SKIP LOCKED skips locked rows, making it ideal for job queues and work distribution.
  • Deadlocks occur when transactions wait for each other in a circular dependency. Prevent them by acquiring locks in a consistent order, keeping transactions short, locking all resources upfront, and setting lock timeouts.
  • Always implement retry logic for deadlocks, because they can never be completely eliminated, only minimized.
  • Optimistic locking (version checks) provides maximum concurrency for low-contention scenarios. Pessimistic locking (FOR UPDATE) guarantees safety for high-contention scenarios.
The Three Rules of Locking
  1. Lock as little as possible. Row-level, not table-level. Only the rows you need.
  2. Lock as briefly as possible. Keep transactions short. Do preparation outside the transaction.
  3. Lock in consistent order. Same order everywhere in your codebase. No exceptions.

Follow these three rules and you will avoid the vast majority of locking problems in production.

Table of Contents