Locking #

Locking is the mechanism that lets a database serve many transactions concurrently without letting them corrupt each other’s data. Without locking, two transactions reading and modifying the same data in parallel can produce invalid states — half-written data, wrong numbers, or changes that simply disappear.

But locking is a double-edged sword. Used correctly, it becomes an invisible guardian of consistency — working in the background, ensuring every transaction gets a consistent view of the data. Used carelessly, it becomes the biggest bottleneck in the system: queries that should finish in milliseconds waiting for seconds, connection pools filled by queued processes, throughput dropping while the CPU is still idle, and deadlocks rolling back transactions without a clear warning.

The difference between the two isn’t whether you use locking or not — it’s how deeply you understand how it works, when to activate it, and how to minimize its impact on system concurrency.

Three Anomalies Locking Wants to Prevent #

Before discussing lock types, it’s important to understand the problems they solve. There are three classic anomalies that can occur when multiple transactions access the same data without coordination.

Dirty Read #

A dirty read happens when one transaction reads data that another transaction is modifying but hasn’t committed yet. If that uncommitted transaction is eventually rolled back, the data that was read is data that never really existed.

Dirty read scenario:

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B
    participant DB as Database

    Note over A, B: "Dirty read scenario"
    Note over A, DB: "T=0ms"
    A->>DB: BEGIN

    Note over A, DB: "T=1ms"
    A->>DB: "UPDATE orders SET status='shipped' WHERE id = 10"
    Note over A: "Not COMMITTED yet"

    Note over B, DB: "T=2ms"
    B->>DB: "SELECT status FROM orders WHERE id = 10"
    DB-->>B: "shipped (Dirty Read)"

    Note over A, DB: "T=3ms"
    A->>DB: "ROLLBACK (cancelled due to an error)"

    Note over B: "T=4ms: The data that was read never existed!"

Transaction B made a decision based on invalid data.

Non-Repeatable Read #

A non-repeatable read happens when a transaction reads the same row twice and gets different values — because between the two reads, another transaction modified and committed the data.

Non-repeatable read scenario:

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B
    participant DB as Database

    Note over A, B: "Non-repeatable read scenario"
    Note over A, DB: "T=0ms"
    A->>DB: BEGIN

    Note over A, DB: "T=1ms"
    A->>DB: "SELECT balance FROM wallets WHERE id = 1"
    DB-->>A: "100,000"

    Note over B, DB: "T=2ms"
    B->>DB: "UPDATE wallets SET balance = 50000 WHERE id = 1 & COMMIT"

    Note over A, DB: "T=3ms"
    A->>DB: "SELECT balance FROM wallets WHERE id = 1"
    DB-->>A: "50,000 (Different from T=1ms!)"

Within the same transaction, the same data gives different results. Reports or calculations relying on this consistency become untrustworthy.

Phantom Read #

A phantom read happens when a transaction runs a query with a certain condition twice, and between the two runs another transaction inserts a new row satisfying that condition. The new row “appears” like a ghost in the second read.

Phantom read scenario:

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B
    participant DB as Database

    Note over A, B: "Phantom read scenario"
    Note over A, DB: "T=0ms"
    A->>DB: BEGIN

    Note over A, DB: "T=1ms"
    A->>DB: "SELECT COUNT(*) FROM orders WHERE status = 'pending'"
    DB-->>A: "5"

    Note over B, DB: "T=2ms"
    B->>DB: "INSERT INTO orders (status) VALUES ('pending') & COMMIT"

    Note over A, DB: "T=3ms"
    A->>DB: "SELECT COUNT(*) FROM orders WHERE status = 'pending'"
    DB-->>A: "6 (A new row 'appears')"

Transaction A changed nothing, but its count changed. This is problematic for operations like “process all pending orders”.

All three anomalies are prevented by the combination of locking and isolation levels — and understanding them is the key to choosing the right locking strategy.

Seven Lock Types You Need to Understand #

Shared Lock (S Lock) #

A shared lock is used when a transaction wants to read data and ensure it doesn’t change while being read. Many transactions may hold a shared lock on the same row simultaneously — they’re all only reading, nobody’s writing, so there’s no conflict.

A conflict occurs when a transaction wants to write: a writing transaction can’t get an exclusive lock while a shared lock is still active.

-- Shared lock: a transaction reads and doesn't want the data to change while reading
SELECT * FROM products WHERE id = 42 LOCK IN SHARE MODE;

-- Another transaction CAN read the same row (shared locks don't conflict)
-- Another transaction CANNOT write to the same row until the shared lock is released

-- When to use:
-- When you need to read data for validation, but worry it might change
-- before you finish processing. Lighter than an exclusive lock.

Exclusive Lock (X Lock) #

An exclusive lock is used when a transaction will write data. Only one transaction may hold an exclusive lock on a row — no other transaction may read (with a shared lock) or write (with an exclusive lock) that row until the lock is released.

-- Exclusive lock: the transaction will write; nobody may read or write
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;

-- Another transaction CANNOT read with LOCK IN SHARE MODE
-- Another transaction CANNOT write
-- Another transaction CAN read without a lock (non-locking read) — depends on the isolation level

-- When to use:
-- When you're reading data you're about to modify
-- and don't want anything changing it between your read and write.

Lock compatibility table:

              Lock Requested by a New Transaction
              ─────────────────────────────────────
  Existing    │  Shared (S)  │  Exclusive (X)
  Lock        │              │
  ────────────┼──────────────┼───────────────
  Shared (S)  │  Compatible  │  Conflict
              │  (allowed)   │  (waiting)
  ────────────┼──────────────┼───────────────
  Exclusive(X)│  Conflict    │  Conflict
              │  (waiting)   │  (waiting)

Intent Lock #

An intent lock is an internal mechanism used by database engines (like InnoDB) to coordinate locks at different levels — row level and table level. Before locking a row, the database automatically places an intent lock at the table level to signal its intention.

This lets the database check lock compatibility efficiently without having to check each row one by one.

Intent locks — placed automatically by the database, no need to write them manually:

  Intent Shared (IS)    → I'm going to place shared locks on some rows of this table
  Intent Exclusive (IX) → I'm going to place exclusive locks on some rows of this table

  Why it matters to understand:
  When you see a deadlock log with "TABLE LOCK table... trx id... lock mode IX",
  that's an intent exclusive lock — a sign this transaction will write to this table.
  Understanding this helps you read and diagnose deadlocks more accurately.

Row-Level Lock #

A row-level lock locks specific rows, not the entire table. This is the lock type InnoDB uses by default and the reason InnoDB is far better for concurrent workloads than MyISAM, which uses table-level locks.

With row-level locks, two transactions can modify the same table simultaneously as long as they touch different rows — no conflict.

-- Row-level lock: only the row with user_id = 1 is locked
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;

-- Another transaction can still access rows user_id = 2, 3, etc.
-- Only the user_id = 1 row is blocked for writes.

-- IMPORTANT: row-level locking only happens if the query uses an index.
-- If the query doesn't use an index, InnoDB performs a table lock.
-- This is a very common trap that often goes unnoticed.

Table-Level Lock #

A table-level lock locks the entire table. No other transaction can access that table (for conflicting operations) until the lock is released. Concurrency drops drastically — only one transaction can work on the table at a time.

-- Explicit table lock — almost never needed in modern applications
LOCK TABLES orders WRITE;
-- All operations to the orders table from other transactions are blocked

-- Usually happens implicitly when:
-- 1. A query doesn't use an index and InnoDB does a full table scan with locking
-- 2. ALTER TABLE (depending on the operation type and MySQL version)
-- 3. LOAD DATA INFILE

UNLOCK TABLES;

If you see LOCK TABLES ... WRITE in a production application codebase, it’s almost always a sign of a design problem. Table locks are very rarely needed in modern applications and can almost always be replaced with more precise row-level locks.

Gap Lock #

A gap lock locks the empty space between index values, not existing rows. Its purpose is to prevent other transactions from inserting new rows into the locked range — to avoid phantom reads.

-- This query places a gap lock on the range id > 10 AND id < 20
-- No rows with id 11-19 exist, but the space is locked
SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;

-- Another transaction trying to INSERT with id = 15 will be blocked
-- even though that row didn't exist before

Gap lock visualization:

  Index:  ... [8] [9] [10] (gap) [20] [21] ...
                            ─────
                        gap lock here
                   prevents INSERTs between 10 and 20

  Existing rows: id=10, id=20 (both also locked by row locks)
  Locked gap: all values between 10 and 20 (exclusive)

Gap locks are a very common source of confusion because they lock data that’s “invisible”. A transaction can be blocked trying to insert a row into a locked range — even if that row never existed before.

Gap locks are only active at isolation level REPEATABLE READ and above. At READ COMMITTED, gap locks aren’t used — meaning phantom reads can happen, but INSERTs won’t be blocked by range operations in other transactions.

Next-Key Lock #

A next-key lock is a combination of a row lock and a gap lock — it locks the existing row as well as the space after that row. InnoDB uses next-key locks by default at isolation level REPEATABLE READ to prevent phantom reads.

Next-key lock visualization for the query: SELECT * FROM orders WHERE id <= 20 FOR UPDATE

  Index:  ... [10] (gap) [15] (gap) [20] (gap after 20) ...
               ────────────────────────────────────────────
               all of this is locked: row locks + gap locks combined

  Row id=10: row lock (nobody can update/delete)
  Gap 10-15:   gap lock (nobody can INSERT here)
  Row id=15: row lock
  Gap 15-20:   gap lock
  Row id=20: row lock
  Gap after 20: gap lock (prevents INSERTs above 20 that still fall in range)

Locking and Isolation Levels #

The isolation level determines how aggressively the database places locks and which anomalies are prevented. Choosing too high an isolation level sacrifices concurrency — choosing too low opens up data anomaly risks.

The relationship between isolation levels, locking, and anomalies:

┌──────────────────────┬──────────────────────────────┬────────────┬──────────────┬─────────────┐
│ Isolation Level      │ Locking Behavior              │ Dirty Read │ Non-Rep Read │ Phantom Read│
├──────────────────────┼──────────────────────────────┼────────────┼──────────────┼─────────────┤
│ READ UNCOMMITTED     │ Almost no locks               │ Can happen │ Can happen   │ Can happen  │
│ READ COMMITTED       │ Locks released after reads    │ Prevented  │ Can happen   │ Can happen  │
│ REPEATABLE READ      │ Row locks + gap locks         │ Prevented  │ Prevented    │ Prevented*  │
│ SERIALIZABLE         │ All reads become locking reads│ Prevented  │ Prevented    │ Prevented   │
└──────────────────────┴──────────────────────────────┴────────────┴──────────────┴─────────────┘

* InnoDB REPEATABLE READ prevents phantom reads through next-key locks,
  even though the SQL standard says phantom reads are still possible at this level.

MySQL InnoDB uses REPEATABLE READ as its default — and this is the right choice for most applications. SERIALIZABLE provides the strongest guarantee but throughput can drop significantly because every SELECT becomes a locking read.

-- Check the active isolation level
SELECT @@transaction_isolation;

-- Change the isolation level for this session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Change globally (needs a restart or reconnect for full effect)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Four Bad Impacts of Incorrect Locking #

Long Transactions — Locks Held Too Long #

Every lock is held while the transaction stays open. The longer the transaction, the longer the lock is held, the longer other processes wait. In high-concurrency systems, a single transaction open for 5 seconds can make dozens of other requests queue up.

Impact of long transactions:

  T=0s    Transaction A acquires locks on rows X, Y, Z
  T=0s    Request 1 waits for lock X
  T=1s    Request 2 waits for lock X
  T=2s    Request 3 waits for lock X
  ...
  T=5s    Transaction A finishes, locks released
  T=5s    Requests 1-N can finally proceed

  If the connection timeout is shorter than 5s, those requests have already
  errored out before they could get the lock.

The most common cause of unintentional long transactions: making HTTP calls to external services, file operations, or heavy computation inside a transaction holding a lock.

-- ANTI-PATTERN: slow operations inside a transaction
START TRANSACTION;

SELECT * FROM orders WHERE id = 10 FOR UPDATE;

-- [HTTP call to the payment gateway: 1-3 seconds]
-- The lock is held for the duration of the HTTP call
-- All other processes needing order id=10 wait

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

-- CORRECT: external operations outside the transaction
payment_result = payment_gateway.charge(amount)  -- outside the transaction

START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
UPDATE orders SET status = payment_result.status WHERE id = 10;
COMMIT;
-- The transaction only holds the lock during database operations — milliseconds, not seconds

Lock Contention — Competing for the Same Lock #

Lock contention happens when many transactions compete for locks on the same rows or tables. The higher the contention, the more time is spent waiting — not working.

Signs of lock contention:

  ✗ Low server CPU but also low throughput
  ✗ Response time rising without new heavy queries
  ✗ SHOW PROCESSLIST showing many queries with "Waiting for lock" status
  ✗ The slow query log filling with queries that should be fast

-- Detecting lock contention in MySQL InnoDB
SHOW ENGINE INNODB STATUS\G

-- Look at the TRANSACTIONS section:
-- "lock wait X lock struct(s)" → a transaction is waiting for a lock
-- "heap size", "row lock(s)" → details of held locks
-- "WAITING FOR THIS LOCK TO BE GRANTED" → the lock being waited on

Deadlocks — Waiting on Each Other Without End #

A deadlock is a condition where two or more transactions each hold a lock the other needs, and neither is willing to release. The database detects this and rolls back one transaction as the victim.

Deadlock anatomy:

  Transaction A holds locks on: Wallets(id=1) row
  Transaction A waits for lock on: Orders(id=10) row

  Transaction B holds locks on: Orders(id=10) row
  Transaction B waits for lock on: Wallets(id=1) row

  → Neither can move forward → deadlock
  → The database rolls back one as the victim
  → The application must handle the error and retry

MySQL error code: 1213 (ER_LOCK_DEADLOCK)
"Deadlock found when trying to get lock; try restarting transaction"

-- Viewing the last deadlock info in InnoDB
SHOW ENGINE INNODB STATUS\G

-- The LATEST DETECTED DEADLOCK section will show:
-- - The transactions involved
-- - The locks each holds
-- - The locks each is waiting for
-- - Which transaction was chosen as the victim (TRANSACTION ... WAS VICTIM)

Throughput Dropping Without an Obvious Cause #

This is the most confusing symptom: low CPU, plenty of memory, normal network — but the database feels slow. Almost always the cause is locks: processes queueing for their turn to access the same data, and that waiting time isn’t reflected in CPU or memory metrics.

Locking Best Practices #

Keep Transactions as Short as Possible #

This is the most important rule in locking. Locks are held while the transaction is open — the shorter the transaction, the shorter the lock is held, the fewer other processes are affected.

-- ANTI-PATTERN: lots of logic and queries inside one long transaction
START TRANSACTION;

SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- [many SELECTs for supporting data]
-- [complex calculations]
-- [long business validation]
UPDATE users SET balance = balance - 10000 WHERE id = 1;
INSERT INTO transactions (user_id, amount, type) VALUES (1, 10000, 'debit');
-- [send notifications?]
-- [update statistics?]

COMMIT;

-- CORRECT: separate preparation from execution
-- Step 1: gather all needed data BEFORE opening the transaction
user_data      = SELECT * FROM users WHERE id = 1;            -- no lock
product_data   = SELECT * FROM products WHERE id = 5;         -- no lock
-- [all calculations and validations happen here, outside the transaction]
final_amount   = calculate_final_amount(user_data, product_data)

-- Step 2: open the transaction only for the write operations needing protection
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;                  -- the lock starts here
-- [minimal validation: is the balance still sufficient after we locked?]
UPDATE users SET balance = balance - final_amount WHERE id = 1;
INSERT INTO transactions (user_id, amount) VALUES (1, final_amount);
COMMIT;                                                        -- the lock ends here
-- The transaction is only as wide as the write operations that truly need protection

Make Sure Locking Queries Always Use Indexes #

This is a trap very often unnoticed. When SELECT ... FOR UPDATE runs on a non-indexed column, InnoDB can’t do row-level locking — it performs a full table scan and locks every row it encounters, even irrelevant ones.

-- ANTI-PATTERN: FOR UPDATE on a column without an index → implicit table lock
-- Assume the 'status' column has no index
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- InnoDB will scan the entire table and lock all rows encountered
-- Not just status='pending', but every row in the scan path

-- CORRECT: make sure the column in the WHERE clause is indexed before using FOR UPDATE
-- Add an index if it doesn't exist:
ALTER TABLE orders ADD INDEX idx_orders_status (status);

-- Or better, use the primary key or a unique key for the most precise lock:
SELECT * FROM orders WHERE id = 42 FOR UPDATE;
-- Only the id=42 row is locked — no other rows are affected

-- How to verify that a query uses an index (not a full scan):
EXPLAIN SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- Look at the 'type' column:
-- 'ref' or 'range' → uses an index ✓
-- 'ALL' → full table scan → will cause a wide lock ✗

Avoid Unnecessary Gap Locks #

Gap locks appear when a query uses a range condition (BETWEEN, >, <, >=, <=) with FOR UPDATE. Gap locks block INSERTs in the locked range — even for values that don’t exist yet — and this often causes surprising blocking.

-- ANTI-PATTERN: a range lock wider than needed
-- This places gap locks on all values between 100 and 200
SELECT * FROM products WHERE price BETWEEN 100 AND 200 FOR UPDATE;

-- If another transaction tries to INSERT a product with price=150,
-- it will be blocked even though that product doesn't exist.

-- CORRECT: lock by primary key when possible
-- More precise, no gap locks
SELECT * FROM products WHERE id IN (42, 43, 44) FOR UPDATE;

-- Or if a range lock is truly needed, make sure you're aware
-- that INSERTs into that range will be blocked while the transaction runs.

Access Tables and Rows in a Consistent Order #

Deadlocks almost always happen because two transactions access the same resources in different orders. Establishing and following an access order convention is the most effective way to prevent deadlocks.

-- ANTI-PATTERN: inconsistent lock orders in two parts of the code

-- In the transfer endpoint:
START TRANSACTION;
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;   -- locks wallet first
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;  -- then locks account
...
COMMIT;

-- In the payment endpoint:
START TRANSACTION;
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;  -- locks account first ← reversed!
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;   -- then locks wallet
...
COMMIT;

-- If both run at the same time:
-- Transfer holds the wallet lock, waits for the account lock
-- Payment holds the account lock, waits for the wallet lock → DEADLOCK

-- CORRECT: establish an order convention and follow it across the codebase
-- Convention: ALWAYS lock wallets before accounts, never the reverse.

-- In the transfer endpoint:
START TRANSACTION;
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;   -- wallet first
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;  -- then account
COMMIT;

-- In the payment endpoint (SAME order):
START TRANSACTION;
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;   -- wallet first
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;  -- then account
COMMIT;
-- No deadlocks because the order is always consistent.

Use Lock Timeouts to Prevent Waiting Forever #

By default, a transaction waiting for a lock waits until innodb_lock_wait_timeout is reached (default 50 seconds in MySQL). For web applications, waiting 50 seconds is clearly unacceptable. Set a timeout matching the application’s SLA.

-- Set the lock wait timeout for this session (in seconds)
SET innodb_lock_wait_timeout = 5;

-- Now if a transaction waits for a lock longer than 5 seconds,
-- it gets an error:
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- In the application, catch this error and give the user an appropriate response
-- (e.g. "The system is busy, please try again in a moment")

Monitor Locking Actively #

Locks that aren’t monitored are locks discovered only when they’ve become an incident. Several queries can be run to monitor locking conditions in real time.

-- View all active transactions and the locks they hold
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
    trx_rows_locked,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- Transactions with high duration_seconds are long transaction candidates
-- worth investigating.

-- View locks currently being waited on (blocked processes)
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS wait_seconds
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

-- This query shows: who's blocked, by whom, and for how long.
-- Very useful for real-time lock contention investigations.

When Locking Is Actually Needed #

Not every operation needs explicit locking. Here’s a simple guide for deciding:

USE explicit locking (SELECT ... FOR UPDATE) if:
  ✓ You're about to modify a row you just read
  ✓ There's a real race condition risk (many requests can access the same row)
  ✓ Data consistency is highly critical (financial, stock, processing status)
  ✓ Atomic operations aren't enough because the business logic is complex

DON'T use explicit locking if:
  ✗ You're only reading without writing
  ✗ The data being read isn't for a critical decision
  ✗ An atomic update (SET balance = balance - X) already handles the problem
  ✗ Optimistic locking (version column) fits better because the conflict rate is low
  ✗ The query doesn't use an index (locking will widen to the table level)

CONSIDER optimistic locking if:
  ✓ The conflict rate is low — most operations don't collide
  ✓ You want maximum concurrency without blocking other processes
  ✓ Retrying after a conflict can be done safely

Anti-Patterns to Avoid #

-- ✗ Anti-pattern 1: FOR UPDATE without an index → a disguised table lock
SELECT * FROM orders WHERE created_at > '2024-01-01' FOR UPDATE;
-- If created_at isn't indexed, the entire table is scanned and locked

-- ✓ Solution: make sure there's an index on the WHERE column, or use the primary key

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 2: LOCK TABLES in production applications
LOCK TABLES wallets WRITE, orders WRITE;
-- All other processes touching these two tables are totally blocked

-- ✓ Solution: use row-level locks with SELECT ... FOR UPDATE inside a transaction

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 3: a transaction opened but never committed or rolled back
-- (a zombie transaction because the exception wasn't handled)
START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
-- [an exception happens here, but there's no ROLLBACK]
-- The lock is held until the connection times out or the database restarts

-- ✓ Solution: always use try-finally or a similar mechanism to ensure
-- COMMIT or ROLLBACK is always called, even on exceptions

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 4: assuming a lock-free SELECT is safe for critical operations
SELECT balance FROM wallets WHERE user_id = 1;
-- [check: is the balance sufficient?]
UPDATE wallets SET balance = balance - 10000 WHERE user_id = 1;
-- Race condition: the balance can change between the SELECT and the UPDATE

-- ✓ Solution: use an atomic update or SELECT FOR UPDATE inside a transaction
START TRANSACTION;
SELECT balance FROM wallets WHERE user_id = 1 FOR UPDATE;
-- [check the balance]
UPDATE wallets SET balance = balance - 10000 WHERE user_id = 1;
COMMIT;

Locking Review Checklist #

TRANSACTION DESIGN:
  □ Every transaction as short as possible — only as wide as the write operations needing protection
  □ No HTTP calls, file I/O, or heavy computation inside transactions
  □ Every execution path (including exceptions) always closes the transaction (COMMIT/ROLLBACK)
  □ Lock timeouts configured to match the SLA (not the 50-second default)

LOCK USAGE:
  □ SELECT FOR UPDATE only used when data will be modified soon
  □ All FOR UPDATE queries verified to use indexes
  □ EXPLAIN run on FOR UPDATE queries to verify no full table scans
  □ Range locks (BETWEEN, >, <) with FOR UPDATE — the gap lock implications understood
  □ LOCK TABLES not used in application code

DEADLOCK PREVENTION:
  □ Table and row access order consistent across the codebase
  □ Lock order conventions documented and known to the whole team
  □ No transaction locks more resources than needed

MONITORING:
  □ INNODB_TRX monitored to detect long transactions
  □ INNODB_LOCK_WAITS monitored to detect lock contention
  □ Deadlock logs alerted if the frequency rises
  □ innodb_lock_wait_timeout configured with a sensible value

LOCKING ALTERNATIVES:
  □ Atomic operations (conditional UPDATEs) considered before using FOR UPDATE
  □ Optimistic locking (version column) considered for low conflict rates
  □ "Why pessimistic locking here" decisions documented

Summary #

Locking is a double-edged sword — used correctly it preserves data consistency; used carelessly it becomes a bottleneck making the system feel slow for no obvious reason.
Shared locks allow many concurrent readers but block writers. Exclusive locks block all other transactions — readers and writers alike.
Row-level locking only happens when the query uses an index — without an index, InnoDB does a full table scan and the lock widens to the entire table without warning.
Gap locks lock the empty space between index values — they block INSERTs in the locked range, including for values that don’t exist yet. This is often a source of surprising blocking.
Transaction duration = lock duration — the shorter the transaction, the shorter the lock is held, the smaller the impact on concurrency. This is the most important rule in locking.
Don’t put slow operations inside transactions — HTTP calls, file I/O, or heavy computation inside a transaction holding a lock can make other processes wait for seconds to minutes.
Deadlocks are prevented with consistent access order — establish a table and row locking order convention and follow it across the codebase without exception.
Lock timeouts must be explicitly configured — the 50-second default is too long for web applications. Set a value matching the SLA and catch timeout errors in the app.
Monitoring is mandatory — INNODB_TRX to detect long transactions, INNODB_LOCK_WAITS to detect contention, and deadlock logs to detect access order problems.
Locking isn’t the only solution — consider atomic operations and optimistic locking before using pessimistic locking. Choose the lightest option that still meets consistency needs.

← Previous: Replication Next: Race Condition →