Mastering Concurrency Control

In database systems, multiple transactions often try to access the same data simultaneously. To prevent chaos (like reading incomplete data or overwriting updates), we use Locks. The two most fundamental types are Shared (S) and Exclusive (X).

science

Lock Manager Simulator

Status: NO LOCKS

visibility Incoming Readers (Shared)

Multiple readers can access data simultaneously.

edit Incoming Writers (Exclusive)

Writers need exclusive access. No other readers or writers allowed.

info Action
Database Row #101 lock_open
group

Shared Lock (S-Lock)

For Readers

Also known as a Read Lock. It allows a transaction to read a data item but not modify it. The key feature is that multiple transactions can hold a shared lock on the same item simultaneously.

Real Life Analogy

People reading in a library

"Like a public notice board or library reading room. Many people can look at (read) the notice at the same time without interfering with each other."

  • check_circle Multiple S-Locks allowed simultaneously.
  • cancel Prevents any X-Lock (Writing) until all S-Locks are released.
edit_note

Exclusive Lock (X-Lock)

For Writers

Also known as a Write Lock. It allows a transaction to both read and update data. It is "exclusive" because if one transaction holds it, NO other transaction can hold any lock (S or X) on that item.

Real Life Analogy

Person writing in a diary

"Like writing in a personal diary. Only one person can write at a time. No one else can read or write while you are doing it."

  • check_circle Allows Read and Update operations.
  • cancel Blocks ALL other locks (S or X) completely.

grid_on The Compatibility Matrix

Current Lock \ Request Shared (S) Exclusive (X)
Shared (S)
check Granted
block Blocked
Exclusive (X)
block Blocked
block Blocked

Why this matters for Interviewers:

This matrix proves you understand throughput. Allowing multiple Shared locks increases concurrency (many users reading a viral post). Enforcing Exclusive locks ensures consistency (no two users buying the last ticket simultaneously).

Deadlock Tip: If T1 holds S and wants X, and T2 holds S and wants X, both will wait for the other to release S. This is a classic conversion deadlock.

compare_arrows Key Differences: Shared vs. Exclusive

A quick reference guide for your technical interviews.

Feature Shared Lock (S) Exclusive Lock (X)
Operation Mode visibility Read-Only edit_note Read & Write
Placement Rule Can be placed on objects that do not have an Exclusive lock. Can ONLY be placed on objects that have no locks at all.
Prevention Prevents others from updating the data. Prevents others from reading OR updating the data.
Issuance Issued when transaction wants to read an item w/o an X-lock. Issued when transaction wants to update an unlocked item.
Concurrency Any number of transactions can hold it. Only ONE transaction can hold it.
Instruction S-lock is requested using lock-S instruction. X-lock is requested using lock-X instruction.
Example Multiple transactions reading the same data. Transaction updating a table row.

Top Interview Questions

What is Lock Escalation? expand_more
Lock escalation is when the database system converts many fine-grained locks (like row locks) into a single coarse-grained lock (like a table lock). This saves memory (RAM) used to manage locks but reduces concurrency.

Example: If a transaction updates 10,000 rows in a table, the DB might replace the 10,000 exclusive row locks with one exclusive table lock to save overhead.
Explain "Update Locks" (U-Locks) and why they exist. expand_more
Update locks are a hybrid. They allow reading (like S-locks) but signal an intent to update later. Only one U-lock is allowed per item, but it can coexist with S-locks. It prevents the "conversion deadlock" where two readers both try to upgrade to writers and block each other.
What happens if a transaction holding a Shared lock crashes? expand_more
The database recovery manager will detect the session disconnect/failure and automatically release the locks. Since shared locks don't modify data, no "rollback" of data is needed for the lock itself, but the transaction context is cleaned up. If it held an Exclusive lock, the system would perform a rollback (undoing changes) before releasing the lock to ensure atomicity.