Grokking Database Fundamentals for Tech Interviews

0% completed

​

Lock-Based Concurrency Control

What is Lock-Based Concurrency Control?

Lock-based concurrency control is a mechanism where the database restricts access to data by allowing transactions to acquire locks on the data they access. Locks ensure that no two transactions can read or modify the same piece of data simultaneously, preventing conflicts and maintaining isolation.

Why Use Locks?

Locks are used to:

  1. Prevent Conflicts: Ensure that multiple transactions don’t interfere with each other.
  2. Maintain Consistency: Preserve the integrity of data even with concurrent transactions. 3

.....

.....

.....

Like the course? Get enrolled and start learning!
Parth Sorathiya

Parth Sorathiya

· a year ago

Without intention locks, if one transaction acquires a row-level exclusive lock, and another transaction wants to acquire a table-level shared lock, the DBMS wouldn’t know whether that table-level lock would conflict — unless it scanned every row. This is inefficient.

🔧 Intention Locks Solve This:

They allow hierarchical locking while preserving compatibility and efficiency.

🧠 Example: How Intention Locks Work

Let’s say we have a table Employees, and a transaction wants to:

🔹 Transaction T1:

  • Acquire exclusive lock on a row (e.g., row 5).

It will first:

  • Place an IX lock on the table Employees.
  • Then place an X lock on row 5.

🔹 Transaction T2:

  • Wants a shared lock on the entire table (to read all rows safely).

It