Two-phase locking (2PL) in DBMS Theory - Full Explanation

Introduction

When multiple users try to change data at the same time, conflicts can happen that cause errors or wrong results. Two-phase locking helps manage these changes so that everyone sees consistent data and no conflicts occur.

Explanation

Locking Phase

In this first phase, a transaction requests and obtains all the locks it needs on data items before it can proceed. These locks prevent other transactions from changing the same data at the same time. No locks are released during this phase.

All locks are acquired and held during the locking phase to prevent conflicts.

Unlocking Phase

After the transaction finishes its work, it enters the unlocking phase where it releases all the locks it held. Once a lock is released, the transaction cannot request any new locks. This phase ensures that other transactions can now access the data safely.

Locks are released only after all needed locks are acquired and no new locks can be requested.

Strictness and Serializability

Two-phase locking guarantees that transactions appear to run one after another, not at the same time, which keeps data consistent. Strict 2PL also holds all locks until the transaction commits or aborts, preventing other transactions from seeing partial changes.

2PL ensures transactions are serializable, preserving data correctness.

Real World Analogy

Imagine a library where people want to read and write notes in shared books. Before writing, a person must put a 'Do Not Disturb' sign on the book and keep it until done. Only after finishing and removing the sign can others use the book.

Locking Phase → Putting the 'Do Not Disturb' sign on the book before writing

Unlocking Phase → Removing the sign after finishing writing so others can use the book

Strictness and Serializability → Ensuring only one person writes at a time so notes don't get mixed up

Diagram

Diagram

┌───────────────────────┐
│   Transaction Starts   │
└──────────┬────────────┘
           │
           ▼
┌───────────────────────┐
│   Locking Phase       │
│ (Acquire all locks)   │
└──────────┬────────────┘
           │
           ▼
┌───────────────────────┐
│   Unlocking Phase     │
│ (Release all locks)   │
└──────────┬────────────┘
           │
           ▼
┌───────────────────────┐
│ Transaction Ends       │
└───────────────────────┘

This diagram shows the two phases of two-phase locking: acquiring all locks first, then releasing them after work is done.

Key Facts

Two-phase locking (2PL) → A concurrency control method that divides a transaction into a locking phase and an unlocking phase.

Locking Phase → The phase where a transaction acquires all the locks it needs without releasing any.

Unlocking Phase → The phase where a transaction releases all its locks and cannot acquire new ones.

Serializability → The property that ensures transactions appear to run one after another, preserving data correctness.

Strict 2PL → A variant of 2PL where all locks are held until the transaction commits or aborts.

Common Confusions

Believing locks can be released and reacquired multiple times during a transaction.

Believing locks can be released and reacquired multiple times during a transaction. In 2PL, once the unlocking phase starts and locks begin to be released, no new locks can be acquired.

Thinking 2PL allows transactions to see uncommitted changes from others.

Thinking 2PL allows transactions to see uncommitted changes from others. Strict 2PL prevents this by holding locks until commit or abort, ensuring no partial changes are visible.

Summary

Two-phase locking divides a transaction into a phase of acquiring all locks and a phase of releasing them to avoid conflicts.

This method ensures transactions run in a way that keeps data consistent and prevents errors from simultaneous changes.

Strict 2PL holds locks until the transaction finishes, preventing other transactions from seeing incomplete changes.