Kaushik's Notes

Shared vs Exclusive Lock

7 min read

Overview

  • Purpose: Control concurrent access to database resources
  • Granularity: Applied to rows, pages, tables, or indexes
  • Compatibility: Determines which lock types can coexist
  • Duration: Held for duration of transaction or operation

Exclusive Lock (X-Lock)

Definition

  • Access Control: Only one transaction can hold exclusive lock
  • Operations: Required for write operations (INSERT, UPDATE, DELETE)
  • Exclusivity: Blocks all other row locks (shared and exclusive)
  • Guarantee: Ensures no other transaction can modify or lock the data
  • MVCC note: In PostgreSQL a plain SELECT takes no row lock at any isolation level (including inside BEGIN); it reads the last committed version, so it is never blocked by an X-lock. Only locking reads (FOR KEY SHARE / FOR SHARE / FOR NO KEY UPDATE / FOR UPDATE) and UPDATE/DELETE of the same row wait until the other transaction commits or rolls back
  • InnoDB note: Plain SELECT is also a non-locking consistent read, except under SERIALIZABLE with autocommit off, where InnoDB silently turns it into SELECT ... FOR SHARE and it does block
  • DDL exception: Table-level locks from ALTER TABLE, TRUNCATE or LOCK TABLE ... IN ACCESS EXCLUSIVE MODE block even plain SELECTs on that table

Characteristics

  • Single Writer: Only one transaction can modify data
  • Read Blocking: Prevents other transactions from taking locking reads (plain MVCC reads still see the old committed version)
  • Write Blocking: Prevents other transactions from writing
  • Isolation: Complete isolation of the locked resource

When Acquired

  • UPDATE Statements: Modifying existing rows
  • DELETE Statements: Removing rows
  • INSERT Statements: Adding new rows (sometimes)
  • SELECT FOR UPDATE: Explicit exclusive read

Example Scenarios

-- Transaction A acquires exclusive lock
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 
-- Transaction B: plain SELECT is NOT blocked (MVCC) - it sees the last committed balance
SELECT balance FROM accounts WHERE id = 1;
-- Transaction B must wait (blocked) until A commits/rolls back
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

Verified on PostgreSQL 17.8

  • Setup: Session A runs BEGIN; UPDATE accounts SET balance = 50 WHERE id = 1; (not committed; committed value is 100). Session B then touches the same row:
Statement in BBlocks?Result
SELECT (autocommit)❌ NoReturns 100 (old committed value)
BEGIN; SELECT (Read Committed)❌ NoReturns 100
BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT❌ NoReturns 100
BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT❌ NoReturns 100
SELECT ... FOR SHARE✅ YesWaits “while locking tuple (0,1)“
SELECT ... FOR UPDATE✅ YesWaits until A commits/rolls back
UPDATE same row✅ YesWaits “while updating tuple”
SELECT ... FOR UPDATE NOWAIT❌ No (fails fast)ERROR: could not obtain lock on row
  • After A commits: every reader sees 50
  • Check who is blocked: the waiting session shows wait_event_type = Lock, wait_event = transactionid, and pg_blocking_pids() returns A’s pid
SELECT pid, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Shared Lock (S-Lock)

Definition

  • Access Control: Multiple transactions can hold shared locks simultaneously
  • Operations: Taken by locking reads (SELECT ... FOR SHARE, InnoDB LOCK IN SHARE MODE, plain SELECT under InnoDB SERIALIZABLE with autocommit off) and foreign-key checks
  • MVCC note: Plain SELECTs in PostgreSQL and InnoDB (default isolation) take no shared row lock; readers and writers don’t block each other. Classic lock-based engines (e.g. SQL Server without snapshot isolation) do take S-locks for every read
  • Compatibility: Compatible with other shared locks
  • Protection: Prevents data modification while reading

Characteristics

  • Multiple Readers: Many transactions can read simultaneously
  • Read Allowing: Allows concurrent read access
  • Write Blocking: Prevents write operations
  • Consistency: Ensures stable read during transaction

When Acquired

  • SELECT Statements: Reading data (isolation level dependent)
  • Range Queries: Scanning multiple rows
  • Index Lookups: Accessing index pages
  • Foreign Key Checks: Validating references

Example Scenarios

-- Transaction A and B can both acquire shared locks
-- Transaction A
SELECT balance FROM accounts WHERE id = 1 FOR SHARE;
 
-- Transaction B (allowed simultaneously)
SELECT balance FROM accounts WHERE id = 1 FOR SHARE;
 
-- Transaction C must wait (blocked) until A and B finish
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- (Without FOR SHARE, A and B take no lock and C would not wait)

Lock Compatibility Matrix

Current LockRequested LockCompatible
NoneShared (S)✅ Yes
NoneExclusive (X)✅ Yes
Shared (S)Shared (S)✅ Yes
Shared (S)Exclusive (X)❌ No
Exclusive (X)Shared (S)❌ No
Exclusive (X)Exclusive (X)❌ No

Key Rules

Shared Lock Rules

  • Multiple Holders: Many transactions can hold shared locks on same resource
  • Read Protection: Guarantees data won’t change during read
  • Write Prevention: Blocks exclusive locks (prevents writes)
  • Compatible: Only with other shared locks

Exclusive Lock Rules

  • Single Holder: Only one transaction can hold exclusive lock
  • Full Control: Complete read and write access
  • Total Blocking: Blocks all other lock types
  • Incompatible: With all other locks (shared and exclusive)

Lock Escalation

When It Happens

  • Too Many Row Locks: Database converts to table lock
  • Memory Pressure: Reduce lock table memory usage
  • Performance Optimization: Fewer locks to manage

Impact

  • Reduced Granularity: Row locks become table locks
  • Lower Concurrency: More blocking behavior
  • Trade-off: Memory vs concurrency

Isolation Level Impact

  • Note: The per-level lock behaviour below is the lock-based (textbook / SQL Server) model. In PostgreSQL, plain SELECTs take no row locks at any level (isolation comes from snapshots); in InnoDB only SERIALIZABLE with autocommit off makes plain SELECTs lock

Read Uncommitted

  • Shared Locks: Not acquired for reads
  • Dirty Reads: Possible to read uncommitted data

Read Committed

  • Shared Locks: Acquired and released immediately after read
  • Non-repeatable Reads: Possible within transaction

Repeatable Read

  • Shared Locks: Held until transaction end
  • Consistent Reads: Same data throughout transaction

Serializable

  • Range Locks: Shared locks on ranges to prevent phantoms
  • Full Isolation: Strongest consistency guarantees

Performance Considerations

Shared Lock Benefits

  • High Concurrency: Multiple readers don’t block each other
  • Read Scalability: Supports many concurrent read operations
  • Consistency: Prevents dirty reads

Shared Lock Costs

  • Write Blocking: Locking readers block writers (plain MVCC readers don’t)
  • Lock Overhead: Memory and CPU for lock management
  • Potential Deadlocks: With exclusive lock requests

Exclusive Lock Benefits

  • Data Integrity: Guarantees isolated write operations
  • Consistency: Prevents lost updates and race conditions
  • Predictable: Clear ownership semantics

Exclusive Lock Costs

  • Low Concurrency: Blocks all other access
  • Potential Bottlenecks: Serial access to popular data
  • Deadlock Risk: Higher with multiple exclusive locks

Common Scenarios

Reader-Writer Problem

-- Many readers can coexist
SELECT * FROM products WHERE category = 'electronics' FOR SHARE;
 
-- Writer must wait for the FOR SHARE readers to finish
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
-- With plain SELECTs (MVCC), the writer does NOT wait and readers keep seeing the old version

Banking Transaction

-- Exclusive lock prevents concurrent modifications
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Inventory Management

-- Shared lock for checking availability (plain SELECT would take no lock under MVCC)
SELECT quantity FROM inventory WHERE product_id = 123 FOR SHARE;
 
-- Exclusive lock for updating stock
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;

Best Practices

Minimize Lock Duration

  • Short Transactions: Reduce lock hold time
  • Efficient Queries: Use indexes to speed up operations
  • Batch Operations: Group related changes

Appropriate Lock Types

  • Read-Only: Use shared locks or lower isolation levels
  • Modifications: Use exclusive locks appropriately
  • SELECT FOR UPDATE: When you need to lock for future update

Avoid Lock Contention

  • Access Patterns: Design to minimize conflicts
  • Transaction Ordering: Consistent resource access order
  • Partitioning: Reduce contention through data partitioning

Troubleshooting

Lock Waits

  • Monitor: Track lock wait times and frequencies
  • Identify: Find queries causing long waits
  • Optimize: Improve query performance or change access patterns

Deadlocks

  • Detection: Database automatically detects and resolves
  • Prevention: Consistent resource ordering
  • Recovery: Implement retry logic for deadlock victims
  • Intent Locks: Hierarchical locking for table/row relationships
  • Update Locks: Intermediate lock type to prevent deadlocks
  • Deadlocks: Circular waiting scenarios
  • Two-Phase Locking: Protocol ensuring serializability

Summary

  • Shared Locks: Multiple readers, blocks writers, enables concurrent reads (taken only by locking reads like FOR SHARE in Postgres/InnoDB)
  • MVCC: In Postgres plain SELECTs never wait for row locks at any isolation level (verified on 17.8); locking reads (FOR SHARE/FOR UPDATE) and UPDATE/DELETE of the same row do wait. InnoDB behaves the same except SERIALIZABLE with autocommit off. DDL (ALTER TABLE, TRUNCATE, ACCESS EXCLUSIVE) blocks even plain SELECTs
  • Exclusive Locks: Single accessor, blocks all others, ensures isolated writes
  • Compatibility: Shared locks compatible with each other, exclusive locks compatible with none
  • Trade-off: Concurrency vs consistency based on lock type choice
  • Best Practice: Use appropriate lock type for operation and minimize duration

Graph View

Backlinks