< body>

Lock granularity, 2-phase locking

Database Locking Mechanisms

Explore lock types, granularity levels, and advanced locking protocols through interactive simulations

Interactive Lock Mechanism simulator

Experience different lock types, granularity levels, and locking protocols through real-time simulations.

Lock Granularity Hierarchy
No Lock
Database: SalesDB

Entire database with all tables and data

No Lock
Table: Customers

Complete table with all rows and columns

No Lock
Page: Page_001

Database page containing multiple rows

No Lock
Row: Customer_ID_1001

Individual row with specific customer data

No Lock
Field: Customer_Name

Specific field within a row

Lock Compatibility Matrix

Interactive Compatibility Testing
Lock Type None IS IX S SIX X
None ✓ ✓ ✓ ✓ ✓ ✓
IS ✓ ✓ ✓ ✓ ✓ ✗
IX ✓ ✓ ✓ ✗ ✗ ✗
S ✓ ✓ ✗ ✓ ✗ ✗
SIX ✓ ✓ ✗ ✗ ✗ ✗
X ✓ ✗ ✗ ✗ ✗ ✗
Lock Type Legend:
IS: Intention Shared
IX: Intention Exclusive
S: Shared
SIX: Shared + Intention Exclusive
X: Exclusive
✓: Compatible    ✗: Incompatible

Two-Phase Locking (2PL) Protocol

Interactive 2PL Demonstration
Growing Phase

Transaction can acquire locks but cannot release any locks

Locks acquired: 0
Shrinking Phase

Transaction can release locks but cannot acquire new locks

Locks released: 0
Transaction Timeline
T1
T2
T3

Lock Queue Management

Real-time Lock Queue Simulation
Resource A
Resource B
Resource C
Queue Management Controls

Lock Granularity Analysis

Interactive Granularity Testing

Explore the trade-offs between different lock granularity levels

Current Selection: Database Level
Concurrency Level Low
Lock Overhead Low
Deadlock Risk Low
Performance High
Granularity Characteristics

Database Level Locking:

  • Lowest concurrency but simplest implementation
  • Minimal lock management overhead
  • Suitable for single-user or batch processing
  • No deadlock risk between transactions

Locking Performance Analysis

Lock Protocol Performance Comparison
throughput Analysis
Basic 2PL 85 tps ↑ 5%
Strict 2PL 72 tps ↓ 8%
Rigorous 2PL 68 tps ↓ 12%
Lock Contention metrics
Avg Wait Time 125ms
Lock Conflicts 23
Deadlocks/min 0.8

Deadlock Prevention Strategies

Interactive Prevention Strategy Testing
Wait-Die Strategy
Older transactions wait for younger ones; younger transactions are aborted (die) when requesting locks held by older transactions.
Wound-Wait Strategy
Older transactions preempt (wound) younger ones; younger transactions wait for older ones to complete.
hX8-Based Prevention
Transactions are aborted if they wait for locks longer than a specified hX8 period.
Ordered Resource Locking
All transactions must request locks in a predefined order to prevent circular wait conditions.
Strategy Demonstration

Select a prevention strategy above to see how it works in practice.

Lock Status

Active Locks 0
Waiting Requests 0
Lock Conflicts 0
Avg Hold Time 0ms

Simulation Controls

10s
5

Lock Types

S
Shared Lock Multiple transactions can read
X
Exclusive Lock Only one transaction can write
IS
Intention Shared Intent to acquire S locks below
IX
Intention Exclusive Intent to acquire X locks below
SIX
Shared + Intent Exclusive S lock + intent for X locks below

Lock Activity Log

[System Ready]
Waiting for lock operations...

Real-time metrics

Lock Efficiency 95% ↑ 2%
Contention Rate 12% ↓ 3%
Queue Length 2.3
Success Rate 88% ↑ 5%

Quick Actions

Related Topics

Concurrency Control ACID Properties Performance Tuning

Database Lock Types & Mechanisms

Understanding different lock types and their applications in database systems

Shared Locks (S)

Allow multiple transactions to read the same resource simultaneously

  • • Multiple readers allowed
  • • No writers while shared lock held
  • • Compatible with other shared locks
  • • Used for SELECT operations

Exclusive Locks (X)

Grant exclusive access to a single transaction for write operations

  • • Only one transaction allowed
  • • No other locks can coexist
  • • Used for UPDATE/DELETE/INSERT
  • • Highest level of isolation

Intention Locks

Indicate intention to acquire locks at lower levels in the hierarchy

  • • IS: Intention to share
  • • IX: Intention to exclude
  • • SIX: Shared + intention exclusive
  • • Enable efficient lock checking

Lock Granularity Levels

Different levels of locking granularity offer various trade-offs:

Fine-Grained
  • • Field/Column level
  • • Row level
  • • High concurrency
  • • More overhead
Coarse-Grained
  • • Table level
  • • Database level
  • • Low concurrency
  • • Less overhead

Locking Protocols

Different protocols ensure zsW and prevent deadlocks:

Basic 2PL: Growing and shrinking phases
Strict 2PL: Hold exclusive locks until commit
Rigorous 2PL: Hold all locks until commit
dlj Ordering: Order by transaction timestamps