Transaction Lock Management

Database management systems utilize two types of synchronization mechanisms: Locks and Latches, each serving distinct purposes:

Transaction-level locks come in two fundamental types:

• Shared Lock (S-LOCK): Permits multiple transactions to read the same object concurrently. Multiple S-locks can coexist on the same object.
• Exclusive Lock (X-LOCK): Allows a single transaction to modify an object. Incompatible with all other lock types, ensuring exclusive access.

Lock compatibility matrix:

Database systems employ a dedicated Lock Manager component that maintains a lock table and processes lock acquisition, upgrade, and release requests while ensuring schedule correctness and concurrency.

Two-Phase Locking Protocol

The Two-Phase Locking (2PL) protocol provides pessimistic concurrency control by enforcing lock acquisition and release in two distinct phases:

Growing Phase: Transactions request locks from the lock manager as needed. The lock manager grants or denies these requests.

Shrinking Phase: Transactions can only release previously acquired locks. No new lock acquisitions are permitted once this phase begins.

2PL guarantees serializable schedules by ensuring transaction dependencies form a directed acyclic graph. However, it can lead to cascading aborts where one transaction's failure forces others to roll back.

Advantages: Ensures serializable schedules.

Disadvantages: May restrict concurrency by disallowing some potentially serializable schedules.

Lock Conversion

Lock conversion enhances concurrency by allowing:

• Lock escalation (S-LOCK to X-LOCK) during the growing phase
• Lock demotion (X-LOCK to S-LOCK) during the shrinking phase

Strong Strict Two-Phase Locking

Strong Strict 2PL (Rigorous 2PL) extends basic 2PL by requiring all locks to be held until transaction commit. This prevents:

• Dirty reads
• Cascading aborts

While providing stronger consistency guarantees, it imposes greater concurrency restrictions.

Consider a funds transfer example:

-- Transaction 1: Transfer $100 from account A to B --
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

-- Transaction 2: Calculate and display total balance --
BEGIN TRANSACTION;
SELECT SUM(balance) FROM accounts WHERE id IN ('A', 'B');
COMMIT;

Deadlock Management

2PL implementations must address deadlocks where transactions form circular wait dependencies.

Deadlock Detection

Detection approaches maintain a waits-for graph where:

• Nodes represent transactions
• Edges indicate lock wait dependencies

The system periodically checks for cycles and resolves them by selecting a victim transaction to abort based on criteria such as:

• Transaction duration
• Progerss made
• Number of locks held
• Restart history

Configuration considerations include detection frequency and rollback scope (full vs. partial).

Deadlock Prevention

Prevention strategies avoid deadlocks entirely by assigning transaction priorities based on age (timestamp):

• Wait-Die: Higher-priority transactions wait for lower-priority ones; lower-priority transactions abort when conflicting with higher-priority holders.
• Wound-Wait: Higher-priority transactions force lower-priority holders to abort; lower-priority transactions wait for higher-priority holders.

Both strategies prevent circular waits through consistent priority enforcement.

Lock Granularity

Hierarchical locking enables efficient resource management by allowing locks at different abstraction levels:

1. Database level
2. Table level
3. Page level
4. Tuple level
5. Attribute level

Intention Locks

Intention locks facilitate hierarchical locking:

• Intention-Shared (IS): Indicates intent to acquire S-locks at finer granularity
• Intention-Exclusive (IX): Indicates intent to acquire X-locks at finer granularity
• Shared+Intention-Exclusive (SIX): Combines S-lock with IX intent

Lock compatibility matrix for hierarchical locking:

Multi-Granularity Locking Protocol

Rules for hierarchical locking:

1. Transactions must respect lock compatibility
2. Root node must be locked first
3. S/IS locks require IX/IS on parent
4. X/SIX/IX locks require IX/SIX on parent
5. Locking follows two-phase protocol
6. Unlocking requires no held child locks

Lock escalation reduces overhead by replacing multiple fine-grained locks with coarser-grained equivalents.

Practical Lock Implementation

Most database systems handle locking automatically, though explicit locking hints can optimize performance:

Table-level explicit locking syntax varies by database:

-- PostgreSQL/DB2/Oracle --
LOCK TABLE employees IN EXCLUSIVE MODE;

-- SQL Server --
SELECT 1 FROM employees WITH (TABLOCKX, HOLDLOCK);

-- MySQL --
LOCK TABLES employees WRITE;

Row-level locking for updates:

SELECT * FROM employees WHERE department = 'Engineering' FOR UPDATE;

Shared row-level locking:

-- PostgreSQL --
SELECT * FROM employees WHERE department = 'Engineering' FOR SHARE;

-- MySQL --
SELECT * FROM employees WHERE department = 'Engineering' LOCK IN SHARE MODE;