Database Locking: Row-Level, Table-Level, and Page-Level Locks

Database Locking: Row-Level, Table-Level, and Page-Level Locks

Database locking is the mechanism that prevents concurrent transactions from interfering with each other. When one transaction is reading or modifying data, locks prevent other transactions from performing conflicting operations on the same data. Locks are essential for maintaining data integrity, but they come at a cost: they reduce concurrency and can lead to deadlocks if not managed properly.

Different lock types and granularities offer different trade-offs between concurrency and overhead. Choosing the right locking strategy is critical for application performance. To understand locking properly, it is helpful to be familiar with database transactions, isolation levels, and database deadlocks.

Lock Granularity:  Row < Page < Table
Concurrency:       High ←─────────────────→ Low
Overhead:          High ←─────────────────→ Low

Lock Modes:
Shared Lock (S)    → For reads. Multiple transactions can hold shared locks on same resource.
Exclusive Lock (X) → For writes. Only one transaction can hold exclusive lock.
Intent Locks (IS, IX, SIX) → Used for hierarchical locking (table → page → row)

What Is Database Locking

Database locking is a concurrency control mechanism that prevents multiple transactions from simultaneously modifying the same data or reading data that is being modified. Locks ensure that transactions execute in an isolated manner, preserving data integrity even under high concurrency.

• Lock Granularity: The size of the locked resource (row, page, table, database).
• Lock Mode: The type of lock (shared, exclusive, update, intent).
• Lock Duration: How long the lock is held (until end of statement or transaction).
• Lock Escalation: Converting many fine-grained locks into a single coarse-grained lock.
• Lock Timeout: How long a transaction will wait for a lock before giving up.

Why Locking Matters

Without locking, concurrent transactions would corrupt data. With too much locking, performance suffers. Proper locking balances data integrity with concurrency.

• Data Integrity: Prevents dirty reads, non-repeatable reads, and lost updates.
• Consistency: Ensures transactions see a consistent view of the data.
• Concurrency Control: Allows multiple transactions to run simultaneously when they access different data.
• Deadlock Prevention: Proper lock design reduces deadlock probability.
• Performance Tuning: Choosing the right lock granularity improves throughput.

Lock Granularity Levels

1. Row-Level Locking

Row-level locking locks only the specific rows being accessed. It offers the highest concurrency because different transactions can modify different rows in the same table simultaneously. However, it has higher overhead because each lock requires memory and management.

-- Transaction A
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks only row 1
    -- Row 2 is not locked
COMMIT;

-- Transaction B (can run concurrently)
BEGIN;
    UPDATE accounts SET balance = balance + 50 WHERE id = 2;  -- Locks only row 2
    -- No conflict with Transaction A
COMMIT;

Pros: Highest concurrency
Cons: Higher memory overhead, more deadlocks
Used by: PostgreSQL, MySQL InnoDB, Oracle (default)

2. Page-Level Locking

Page-level locking locks an entire database page (typically 4-16 KB containing multiple rows). It balances concurrency and overhead. It is less granular than row-level but more granular than table-level.

Database Page (contains rows 1-100)
┌─────────────────────────────────────┐
│ Row1 │ Row2 │ Row3 │ ... │ Row100   │
└─────────────────────────────────────┘

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Locks the entire page containing row 1
-- Rows 2-100 on same page cannot be modified by other transactions

Pros: Lower overhead than row-level, better than table-level for concurrency
Cons: Less concurrency than row-level
Used by: SQL Server (certain scenarios), MySQL (deprecated in favor of row-level)

3. Table-Level Locking

Table-level locking locks the entire table. It has the lowest overhead but the worst concurrency. Only one transaction can modify the table at a time.

-- Transaction A
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks entire accounts table
    -- No other transaction can read or write any row in accounts table
COMMIT;

-- Transaction B (must wait)
BEGIN;
    SELECT * FROM accounts WHERE id = 2;  -- Must wait for Transaction A to complete
COMMIT;

Pros: Lowest overhead, no deadlocks
Cons: Worst concurrency, significant contention
Used by: MySQL MyISAM, some data warehouse operations

4. Database-Level Locking

Database-level locking locks the entire database. This is rarely used and typically occurs during administrative operations like backups or schema changes.

-- Backup operation
ALTER DATABASE mydb SET READ_ONLY = ON;
-- Entire database locked for writes
BACKUP DATABASE mydb TO DISK = 'backup.bak';
ALTER DATABASE mydb SET READ_ONLY = OFF;

Lock Modes

        │  S  │  X  │  IS │  IX │ SIX
────────┼─────┼─────┼─────┼─────┼─────
   S    │ Yes │ No  │ Yes │ No  │ No
   X    │ No  │ No  │ No  │ No  │ No
   IS   │ Yes │ No  │ Yes │ Yes │ Yes
   IX   │ No  │ No  │ Yes │ Yes │ No
   SIX  │ No  │ No  │ Yes │ No  │ No

Lock Duration

Statement-Level Locks

Locks are held only for the duration of a single SQL statement. At Read Committed isolation level, shared locks are released after each statement.

-- Shared locks are released after each SELECT (Read Committed)
BEGIN;
    SELECT * FROM accounts WHERE id = 1;  -- Lock acquired and released
    SELECT * FROM accounts WHERE id = 2;  -- New lock acquired and released
COMMIT;

Transaction-Level Locks

Locks are held until the transaction commits or rolls back. At Repeatable Read and Serializable isolation levels, locks are held for the entire transaction.

-- Locks held until COMMIT (Repeatable Read)
BEGIN;
    SELECT * FROM accounts WHERE id = 1;  -- Lock acquired, held
    SELECT * FROM accounts WHERE id = 2;  -- Lock acquired, held
COMMIT;  -- All locks released

Lock Escalation

Lock escalation is the process of converting many fine-grained locks (row-level) into a single coarse-grained lock (table-level) to save memory. This can cause sudden drops in concurrency.

SQL Server:
- Escalates when a single transaction acquires > 5000 locks
- Escalates when lock memory exceeds 40% of memory grant

PostgreSQL:
- Does not escalate locks (keeps row-level locks)
- May hit max_locks_per_transaction limit

MySQL (InnoDB):
- Does not escalate locks
- Uses row-level locks exclusively

Preventing lock escalation:
- Break large operations into smaller batches
- Use row-level locking hints
- Ensure proper indexing

-- Bad: Single large update (may escalate to table lock)
UPDATE orders SET status = 'archived' WHERE created_at < '2023-01-01';

-- Good: Batched updates
DECLARE @batch_size INT = 1000;
WHILE 1=1
BEGIN
    UPDATE TOP (@batch_size) orders SET status = 'archived'
    WHERE created_at < '2023-01-01' AND status != 'archived';
    
    IF @@ROWCOUNT = 0 BREAK;
    
    WAITFOR DELAY '00:00:01';  -- Allow other transactions
END

Lock Hints (Database-Specific)

SELECT * FROM accounts WITH (NOLOCK);        -- Read uncommitted (dirty reads allowed)
SELECT * FROM accounts WITH (READCOMMITTED); -- Read committed (default)
SELECT * FROM accounts WITH (REPEATABLEREAD);-- Repeatable read
SELECT * FROM accounts WITH (SERIALIZABLE);  -- Serializable
SELECT * FROM accounts WITH (UPDLOCK);       -- Update lock (prevents deadlocks)
SELECT * FROM accounts WITH (ROWLOCK);       -- Row-level lock
SELECT * FROM accounts WITH (PAGLOCK);       -- Page-level lock
SELECT * FROM accounts WITH (TABLOCK);       -- Table-level lock

BEGIN;
    -- Row-level locks
    SELECT * FROM accounts WHERE id = 1 FOR UPDATE;     -- Exclusive row lock
    SELECT * FROM accounts WHERE id = 2 FOR SHARE;      -- Shared row lock
    SELECT * FROM accounts WHERE id = 3 FOR NO KEY UPDATE;
    SELECT * FROM accounts WHERE id = 4 FOR KEY SHARE;
    
    -- Skip locked rows (for queue processing)
    SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
    
    -- Don't wait if lock not available
    SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
COMMIT;

-- Shared lock (allows other reads, blocks writes)
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;

-- Exclusive lock (blocks both reads and writes)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Skip locked (MySQL 8.0+)
SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED;

-- No wait (MySQL 8.0+)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

Lock Monitoring

-- View current locks
SELECT
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    pid,
    query
FROM pg_locks
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE NOT granted;  -- Waiting locks

-- Show current transactions and locks
SHOW ENGINE INNODB STATUS\G

-- Current locks (MySQL 8.0+)
SELECT * FROM performance_schema.data_locks;

-- Waiting locks
SELECT * FROM performance_schema.data_lock_waits;

-- View current locks
SELECT
    request_session_id,
    resource_type,
    resource_description,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

-- Find blocking chains
SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

Common Locking Mistakes to Avoid

• Using Table Hints Incorrectly: NOLOCK can cause dirty reads. TABLOCK reduces concurrency. Understand the implications.
• Long-Running Transactions: Hold locks for too long, causing contention and deadlocks. Keep transactions short.
• Missing Indexes: Without indexes, row-level locks escalate to table locks (or lock more rows than necessary).
• Lock Escalation Surprises: Large transactions may escalate to table locks. Batch large operations.
• Not Monitoring Locks: Without monitoring, you cannot detect lock contention problems.
• Ignoring Lock Timeout Settings: Transactions waiting indefinitely for locks cause application hangs.

Locking Best Practices

• Use Row-Level Locking by Default: Most modern databases default to row-level locking. Do not change unless necessary.
• Keep Transactions Short: Minimize the time between BEGIN and COMMIT to reduce lock contention.
• Add Proper Indexes: Indexes allow the database to lock only the rows that match your conditions.
• Access Tables in Consistent Order: Reduces deadlock probability.
• Use SELECT FOR UPDATE Sparingly: Only when you need to lock rows for subsequent updates.
• Set Lock Timeout: Configure lock_timeout to prevent indefinite waiting.
• Monitor Lock Contention: Regularly check for long-running locks and blocking chains.
• Batch Large Operations: Break large updates into smaller batches to prevent lock escalation.

-- PostgreSQL
SET lock_timeout = '5s';

-- MySQL
SET lock_wait_timeout = 5;

-- SQL Server
SET LOCK_TIMEOUT 5000;  -- milliseconds

Frequently Asked Questions

1. What is the difference between row-level and table-level locking?
   Row-level locking locks only specific rows, allowing high concurrency. Table-level locking locks the entire table, blocking all other access. Row-level has higher overhead but better concurrency.
2. Which databases use row-level locking by default?
   PostgreSQL (always), MySQL InnoDB (default), Oracle (default), SQL Server (with certain isolation levels).
3. What is an intent lock?
   Intent locks are used at higher levels (table, page) to indicate that a transaction intends to acquire locks at lower levels (row). They improve performance by preventing unnecessary lock checks.
4. Does SELECT lock rows?
   At Read Committed isolation level, SELECT does not hold locks (shared locks released after read). At Repeatable Read and Serializable, SELECT holds shared locks for the duration of the transaction.
5. What is the NOLOCK hint and when should I use it?
   NOLOCK (SQL Server) or READ UNCOMMITTED allows dirty reads but avoids being blocked by write locks. Use only for reporting where slight inconsistency is acceptable.
6. What should I learn next after database locking?
   After mastering locking, explore deadlock detection and prevention, isolation levels in depth, optimistic locking patterns, and concurrency control for complete transaction management.