Lock-Based Protocols and Two-Phase Locking in DBMS

6
0

Introduction

Multiple transactions may need to read or update the same database records. Without coordination, their operations can interfere, causing problems such as overwritten updates or decisions based on unfinished changes.

Lock-based protocols control these interactions by requiring transactions to obtain suitable locks. Two-phase locking, commonly called 2PL, adds rules about when locks can be acquired and released so that concurrent execution remains conflict serializable.

What Are Lock-Based Protocols?

A lock is a database mechanism that controls access to a data item. A locking protocol defines which locks a transaction needs and when it may acquire or release them.

Before accessing an item, a transaction requests the appropriate lock. The database’s lock manager checks whether the request is compatible with locks already held by other transactions.

If the request is compatible, it can be granted. Otherwise, the transaction normally waits until the conflicting lock is released.

Locks can protect rows, pages, tables, or ranges of index values. This article uses individual account records to explain the basic ideas.

Example Table

The examples use an Account table containing account identifiers, holder names, and balances.

Account: initial data

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

AccountID is the primary key. In the theoretical examples, A represents Asha’s account and B represents Ravi’s account.

T1 and T2 identify different transactions. Each conceptual example begins with the initial data above.

Shared and Exclusive Locks

The classical locking model uses two main lock modes.

  • Shared lock, S: Allows a transaction to read an item. Multiple transactions can hold shared locks on the same item.

  • Exclusive lock, X: Allows a transaction to read and write an item. It conflicts with both shared and exclusive locks held by other transactions on that item.

For example, T1 and T2 can both hold shared locks while reading Asha’s account. However, if T1 holds an exclusive lock on that account, T2 cannot obtain a shared or exclusive lock on it until the conflict is resolved.

The compatibility rules are:

Lock already held by another transaction

Requested S lock

Requested X lock

S

Compatible

Must wait

X

Must wait

Must wait

This table describes the classical S/X model. Modern databases also support snapshot reads, which can read committed versions without requesting these shared row locks.

An exclusive lock is not the same as locking the entire database. It protects the item or range covered by that lock.

Shared and exclusive locks

Shared and exclusive locks

How Two-Phase Locking Works

Obtaining locks alone is not enough to guarantee a correct schedule. The order in which transactions acquire and release them also matters.

Two-phase locking requires every transaction to divide its lock activity into two phases:

Phase

Allowed

Not allowed

Growing phase

Acquire new locks.

Release locks.

Shrinking phase

Release locks.

Acquire new locks.

The first lock release starts the shrinking phase. Once that happens, the transaction cannot acquire another lock.

Consider a transaction that needs to update both accounts. Here, X(A) means an exclusive lock on A, and unlock(A) means releasing the lock on A.

Valid lock sequence:

Acquire X(A)
Acquire X(B)
Perform the required account operations
Unlock A
Unlock B

Both locks are acquired before either is released, so the sequence follows 2PL.

Two phases of locking

Two phases of locking

Invalid lock sequence:

Acquire X(A)
Perform operations on A
Unlock A
Acquire X(B)

Acquiring the lock on B after releasing the lock on A violates 2PL.

The moment a transaction obtains its final lock is called its lock point. When all transactions follow 2PL and lock their accesses correctly, their lock points provide an equivalent serial ordering.

The two phases concern lock acquisition and release, not a reading phase followed by a writing phase. A transaction can perform data operations while holding the required locks.

What Does 2PL Guarantee?

In the classical locking model, 2PL guarantees conflict serializability: the resulting schedule is conflict equivalent to some serial execution.

However, basic 2PL does not automatically guarantee:

  • Freedom from deadlocks.

  • Freedom from cascading rollback.

  • Recoverability.

For example, basic 2PL may allow a transaction to release a write lock before committing. Another transaction could then read its uncommitted value. Stronger variants retain locks longer to prevent this problem.

2PL is sufficient for conflict serializability, but it is not necessary: some conflict-serializable schedules cannot be generated under 2PL.

Main Variants of Two-Phase Locking

Variant

Main rule

Benefit or limitation

Basic 2PL

No new lock acquisition after the first lock release.

Ensures conflict serializability, but may allow cascading rollback and deadlocks.

Conservative 2PL

Obtain all required locks before beginning data operations. If the full set is unavailable, wait without holding a partial set.

Avoids lock deadlocks, but requires knowing the required locks in advance.

Strict 2PL

Follow 2PL and retain all exclusive locks until commit or abort.

Prevents dirty reads and dirty writes, simplifying recovery. Deadlocks remain possible.

Rigorous 2PL

Retain all shared and exclusive locks until commit or abort.

Adds stronger lock retention than strict 2PL. Deadlocks remain possible.

In strict 2PL, shared locks may be released earlier, but the basic two-phase rule still applies. After any lock is released, no new lock may be acquired.

Conservative 2PL is a different restriction from holding every lock until commit. Obtaining all locks in advance does not, by itself, mean they must all be retained until the transaction ends.

The table uses strict for retaining exclusive locks and rigorous for retaining both shared and exclusive locks.

Lock Conversion

A transaction may need to change its lock mode.

  • Upgrade, S to X: A transaction that initially reads an item may later need to update it. Under 2PL, the upgrade must occur during the growing phase.

  • Downgrade, X to S: A transaction may stop requiring write access but continue reading. Under basic 2PL, a downgrade belongs to the shrinking phase.

An upgrade is granted only when it is compatible with other transactions’ locks. If T1 and T2 both hold S locks on A, neither can immediately upgrade to X while the other retains its S lock.

Strict 2PL does not permit an exclusive lock to be downgraded before commit or abort, because that would release its exclusive protection too early.

Deadlocks and Practical Limitations

A deadlock occurs when transactions wait for one another in a cycle, so none can continue.

Consider this sequence:

Step

T1

T2

1

Acquires X(A).

2

Acquires X(B).

3

Requests X(B), but waits for T2.

4

Requests X(A), but waits for T1.

Both transactions are still acquiring locks, so neither has violated 2PL. Nevertheless, they are deadlocked.

The database can detect the cycle and abort one transaction, releasing its locks so the other can proceed. The application may then retry the aborted transaction.

Useful practices include:

  • Use a consistent lock order: Transactions accessing these accounts can lock A before B.

  • Keep transactions short: Avoid leaving transactions open while waiting for user input.

  • Handle retries: Deadlocks can occur even in carefully designed applications.

  • Choose suitable lock granularity: Smaller locks usually allow more concurrency but require more lock-management work.

Locking only existing rows may also be insufficient to protect a condition such as “all accounts with balance above 700.” Preventing changes to such a matching set can require range protection or other serializable-isolation mechanisms.

Repeated waiting or repeatedly choosing the same transaction for rollback can cause starvation. Fair lock scheduling and retry handling help reduce that risk.

How a deadlock forms

How a deadlock forms

A Compact SQL Example

Objective: Transfer 200 from Asha to Ravi while locking both accounts, and show that another transaction cannot obtain a conflicting lock on Asha’s account until the transfer finishes.

The starting data is:

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

The example uses two separate database connections:

  • Session A: Locks both accounts and performs the transfer.

  • Session B: Requests a conflicting lock on Asha’s account.

The example assumes both accounts exist and Asha has sufficient funds.

1. Create and populate the table

Consider the following queries to create the initial account data.

CREATE TABLE Account (
AccountID INT PRIMARY KEY,
HolderName VARCHAR(50) NOT NULL,
Balance DECIMAL(10, 2) NOT NULL
);
START TRANSACTION;
INSERT INTO Account (AccountID, HolderName, Balance)
VALUES
(1, 'Asha', 1000.00),
(2, 'Ravi', 500.00);
COMMIT;

The table now contains the two initial rows shown above.

2. Session A: Lock the accounts and transfer 200

Consider the following queries to lock account 1 before account 2, then update both balances.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT AccountID, Balance
FROM Account
WHERE AccountID = 1
FOR UPDATE;
SELECT AccountID, Balance
FROM Account
WHERE AccountID = 2
FOR UPDATE;
UPDATE Account
SET Balance = Balance - 200
WHERE AccountID = 1;
UPDATE Account
SET Balance = Balance + 200
WHERE AccountID = 2;
-- Keep the transaction open.

FOR UPDATE requests a row lock suitable for subsequent updates. The two separate queries make the intended acquisition order explicit: account 1, then account 2.

First locking query result:

AccountID

Balance

1

1000.00

Second locking query result:

AccountID

Balance

2

500.00

After the updates, Session A has pending balances of 800.00 and 700.00. Its locks remain held because the transaction has not ended.

3. Session B: Request a conflicting lock

Consider the following queries to request a lock on Asha’s account while Session A remains open.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT AccountID, Balance
FROM Account
WHERE AccountID = 1
FOR UPDATE;

Session B’s query waits for Session A’s conflicting lock. It does not immediately return a result.

4. Session A: Commit the transfer

Consider the following command to commit the transfer and release Session A’s locks.

COMMIT;

Session B’s waiting query can now complete.

Result returned to Session B:

AccountID

Balance

1

800.00

Consider the following queries to finish Session B and display the final account table.

COMMIT;
SELECT AccountID, HolderName, Balance
FROM Account
ORDER BY AccountID;

Expected result:

AccountID

HolderName

Balance

1

Asha

800.00

2

Ravi

700.00

The combined balance remains 1500.00. Session A acquired both required row locks before releasing them at commit.

This demonstrates explicit row locking and lock retention until transaction end. It does not mean that every operation in MySQL or PostgreSQL follows the simple S/X model. Ordinary snapshot reads can behave differently, and READ COMMITTED does not guarantee serializability for arbitrary transactions.

There is no START 2PL SQL command. The database manages locks according to its implementation, isolation level, and the statements executed.

Summary

Lock-based protocols coordinate access through compatible and conflicting lock modes. Two-phase locking requires a growing phase with no releases, followed by a shrinking phase with no new acquisitions. When applied correctly to the relevant accesses, it guarantees conflict serializability.

Strict and rigorous variants retain locks longer to improve recovery safety, while conservative 2PL avoids deadlocks by obtaining the required lock set in advance. Practical applications still need consistent lock ordering, short transactions, and appropriate handling of transaction failures.

CS Core

Read Similar Blogs

Comments0