Database Lock Types (Shared, Exclusive, Intent) and Lock Escalation

• Shared lock (S) – acquired to read a resource; multiple transactions can hold it concurrently, but it is incompatible with an Exclusive lock.
• Exclusive lock (X) – acquired to modify a resource; only one transaction can hold it, blocking all other access.
• Intent locks (IS/IX) – placed on a coarser level (table) to signal that a finer-grained lock (row) exists below, letting the engine check compatibility in O(1) without scanning every row lock.
• Lock escalation – the engine automatically converts many fine-grained row locks into a single coarse table lock when the count exceeds a threshold (around 5000 in SQL Server), reducing overhead but blocking unrelated transactions.

What is a Shared lock?

A Shared lock (S) is acquired to read a resource. It is compatible with other shared locks, so many transactions can read the same row simultaneously. It is incompatible with an exclusive lock, so a reader blocks a writer and vice versa.

-- Transaction A: reads account balance (acquires S lock)
BEGIN;
SELECT balance FROM accounts WHERE id = 1001;
-- Holds S lock on row 1001

-- Transaction B: also reads the same account (also acquires S lock)
BEGIN;
SELECT balance FROM accounts WHERE id = 1001;
-- Both S locks coexist: no conflict

What is an Exclusive lock?

An Exclusive lock (X) is acquired to modify a resource. It is incompatible with everything, including other shared locks. Only one transaction can hold it.

-- Transaction A: updates account balance (acquires X lock)
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1001;
-- Holds X lock on row 1001

-- Transaction B: tries to read the same row
BEGIN;
SELECT balance FROM accounts WHERE id = 1001;
-- Under strict locking: WAITS for A to commit or rollback
-- Under MVCC (PostgreSQL/InnoDB): reads the old snapshot (no wait)

The S/X compatibility matrix is the core of lock-based concurrency: readers do not block readers, but writers block everyone on that resource.

What is the lock compatibility matrix?

| Requested \ Held | None | S (Shared) | X (Exclusive) |
|---|---|---|---|
| S (Shared) | Granted | Granted | Wait |
| X (Exclusive) | Granted | Wait | Wait |

This matrix governs every lock decision.

What are Intent locks and why do they exist?

Intent locks solve a performance problem in multi-granularity locking. Before a transaction locks a specific row with X, it first places an Intent Exclusive (IX) lock on the containing table.

-- Transaction A wants to update row 1001 in accounts
-- Step 1: engine places IX lock on the accounts TABLE
-- Step 2: engine places X lock on ROW 1001

-- Transaction B wants an exclusive lock on the ENTIRE table
-- Step 1: engine checks the table for intent locks
-- Step 2: sees IX from Transaction A
-- Step 3: WAITS (table X is incompatible with IX)
-- Without intent locks, engine would scan ALL row locks: expensive

Intent locks are a cheap flag at the coarse level that makes table-level compatibility checks O(1).

What are the Intent lock types?

| Lock Type | Meaning |
|---|---|
| IS (Intent Shared) | Transaction intends to read rows (S locks below) |
| IX (Intent Exclusive) | Transaction intends to modify rows (X locks below) |
| SIX (Shared + Intent Exclusive) | Reads entire table (S) but modifies some rows (X below) |

Intent locks do not block ordinary row-level work. They only prevent conflicting table-level operations.

What is lock escalation?

Lock escalation is the engine's response to holding too many fine-grained locks. Each lock costs memory and management overhead.

-- At National Bank of India, a year-end job updates interest on all accounts
BEGIN;
UPDATE accounts SET balance = balance * 1.04 WHERE account_type = 'SAVINGS';
-- This touches 2,000,000 rows
-- Each row gets an X lock
-- After ~5000 locks, SQL Server escalates to a TABLE-level X lock
-- Now ALL other transactions on the accounts table are BLOCKED
COMMIT;

SQL Server escalates around 5000 locks on one object. InnoDB primarily uses row-level locking and does not escalate the same way. Oracle uses row-level locks and generally avoids escalation.

How do you avoid unwanted lock escalation?

Batch the operation into smaller chunks so the lock count never reaches the escalation threshold.

-- Wrong: one giant update that triggers escalation
UPDATE accounts SET balance = balance * 1.04 WHERE account_type = 'SAVINGS';

-- Correct: batch in chunks of 1000
DO $$
DECLARE
    batch_size INT := 1000;
    affected INT;
BEGIN
    LOOP
        UPDATE accounts
        SET balance = balance * 1.04
        WHERE account_type = 'SAVINGS'
          AND NOT interest_applied
        LIMIT batch_size;
        GET DIAGNOSTICS affected = ROW_COUNT;
        COMMIT;
        EXIT WHEN affected = 0;
    END LOOP;
END $$;

Other techniques: use good indexes so you lock few rows, choose the right isolation level, and keep transactions short.

How do lock types differ across database engines?

| Feature | SQL Server | InnoDB (MySQL) | PostgreSQL |
|---|---|---|---|
| Row locks | Yes | Yes (next-key) | Yes (MVCC + row locks) |
| Intent locks | IS, IX, SIX | IS, IX | Not explicit (MVCC) |
| Lock escalation | Row to table (~5000) | No escalation | No escalation |
| MVCC | Snapshot isolation | Yes | Yes |
| Deadlock detection | Wait-for graph | Wait-for graph | Wait-for graph |

What should a senior engineer know?

A senior engineer understands that lock escalation is a deliberate overhead-versus-concurrency trade, not a bug.

package com.bank.service;

import java.sql.*;

public class InterestService {

    private static final int BATCH_SIZE = 1000;

    /**
     * Apply interest in batches to avoid lock escalation
     * at National Bank of India.
     */
    public void applyInterestInBatches(Connection conn) throws SQLException {
        conn.setAutoCommit(false);
        String sql = "UPDATE accounts SET balance = balance * 1.04 "
                   + "WHERE account_type = 'SAVINGS' AND NOT interest_applied "
                   + "ORDER BY id LIMIT ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            int affected;
            do {
                ps.setInt(1, BATCH_SIZE);
                affected = ps.executeUpdate();
                conn.commit();
            } while (affected > 0);
        }
    }
}