DBMS Concurrency Control: Locking & Timestamp-Based Protocols

By : Fiona Brown Fiona Brown
Hours Updated

⚡ Smart Summary

Concurrency Control in DBMS manages simultaneous transactions so they run accurately without violating data integrity. It prevents anomalies such as lost updates and dirty reads using lock-based, two-phase, timestamp-based, and validation-based protocols that guarantee serializable results.

  • 👥 Core Purpose: Concurrency control lets many transactions touch shared data at once while keeping the database consistent.
  • ⚠️ Anomalies Prevented: Lost update, dirty read, non-repeatable read, and incorrect summary are the four problems it stops.
  • 🔒 Lock-Based: Shared and exclusive locks control whether a data item can be read or written by others.
  • 🔁 Two-Phase Locking: A growing phase acquires locks and a shrinking phase releases them, which guarantees serializability.
  • ⏱️ Timestamp-Based: Older transactions get priority, ordering conflicting operations by a system timestamp.
  • ✅ Validation-Based: Optimistic control works on local copies, validating only before the write phase.
  • 🎯 Goal: Maximum concurrency with minimum overhead, resilient to site and communication failures.
Read More

Locking and Timestamp Schedulers in DBMS

What is Concurrency Control?

Concurrency Control in a Database Management System is a procedure for managing simultaneous operations without conflicting with each other. It ensures that database transactions are performed concurrently and accurately to produce correct results without violating the data integrity of the respective database.

Concurrent access is quite easy if all users are just reading data, as there is no way they can interfere with one another. However, any practical database has a mix of READ and WRITE operations, and so concurrency becomes a challenge.

DBMS concurrency control is used to address such conflicts, which mostly occur in a multi-user system. Concurrency control is therefore one of the most important elements for the proper functioning of a database where two or more transactions execute simultaneously and require access to the same data. It works hand in hand with transaction management, which defines the units of work that concurrency control must interleave safely.

Potential Problems of Concurrency

Here are some issues you are likely to face without proper DBMS concurrency control:

  • Lost Updates occur when multiple transactions select the same row and update it based on the value selected.
  • Uncommitted dependency (dirty read) occurs when a second transaction selects a row that has been updated by another transaction that has not yet committed.
  • Non-Repeatable Read occurs when a second transaction accesses the same row several times and reads different data each time.
  • Incorrect Summary occurs when one transaction takes a summary over the value of all instances of a repeated data item while a second transaction updates a few of those instances. The resulting summary does not reflect a correct result.

Why Use a Concurrency Method?

Reasons for using a concurrency control method in DBMS:

  • To apply isolation through mutual exclusion between conflicting transactions.
  • To resolve read-write and write-write conflict issues.
  • To preserve database consistency by constantly enforcing execution constraints.
  • To control the interaction among concurrent transactions, achieved using concurrency-control schemes.
  • To help ensure serializability.

Example

Assume that two people go to electronic kiosks at the same time to buy a movie ticket for the same movie and the same show time.

However, there is only one seat left for that show in the theatre. Without concurrency control, it is possible that both moviegoers will end up purchasing a ticket. Concurrency control does not allow this. Both moviegoers can still access the information in the movie seating database, but concurrency control provides a ticket only to the buyer who completes the transaction process first.

Concurrency Control Protocols

Different concurrency control protocols offer different trade-offs between the amount of concurrency they allow and the overhead they impose. The main concurrency control techniques in DBMS are:

  • Lock-Based Protocols
  • Two-Phase Locking Protocol
  • Timestamp-Based Protocols
  • Validation-Based Protocols

Each is examined in turn below, starting with the most widely used, lock-based protocols.

RELATED ARTICLES

Lock-Based Protocols

Lock-based protocols in DBMS are a mechanism in which a transaction cannot read or write a data item until it acquires an appropriate lock. Lock-based protocols help eliminate the concurrency problem by locking or isolating a particular data item to a single transaction.

A lock is a data variable associated with a data item that signifies which operations can be performed on it. Locks help synchronize access to database items by concurrent transactions. All lock requests are made to the concurrency-control manager, and transactions proceed only once the lock request is granted.

Binary Locks: A binary lock on a data item can be in either a locked or an unlocked state.

Shared/Exclusive: This locking mechanism separates locks based on their use. If a lock is acquired to perform a write operation, it is called an exclusive lock.

1. Shared Lock (S): A shared lock is also called a read-only lock. With a shared lock, the data item can be shared between transactions, because none of them has permission to update the item. For example, if two transactions are reading a person’s account balance, the database lets them read by placing a shared lock. If another transaction wants to update that balance, the shared lock prevents it until the reading is over.

2. Exclusive Lock (X): With an exclusive lock, a data item can be read as well as written. It is exclusive and cannot be held concurrently on the same data item. An X-lock is requested using the lock-x instruction. For example, when a transaction needs to update an account balance, it is allowed by placing an X-lock; a second transaction that wants to read or write is then prevented.

3. Simplistic Lock Protocol: This allows transactions to obtain a lock on every object before beginning an operation. Transactions may unlock the data item after finishing the write operation.

4. Pre-claiming Locking: This protocol evaluates operations and creates a list of the data items needed to begin execution. When all locks are granted, the transaction executes, and all locks are released once its operations are over.

Starvation: Starvation is the situation when a transaction waits for an indefinite period to acquire a lock. Reasons include a poorly managed waiting scheme for locked items, a resource leak, or the same transaction being selected as a victim repeatedly.

Deadlock: Deadlock refers to a situation where two or more processes wait for each other to release a resource, forming a circular chain.

Two-Phase Locking (2PL) Protocol

The Two-Phase Locking Protocol, also known as 2PL, is a method of concurrency control that ensures serializability by applying a lock to transaction data, which blocks other transactions from accessing the same data simultaneously.

The Two-Phase Locking protocol allows each transaction to make a lock or unlock request in two steps:

  • Growing Phase: in this phase a transaction may obtain locks but may not release any locks.
  • Shrinking Phase: in this phase a transaction may release locks but may not obtain any new lock.

Two phase locking growing and shrinking phases

It is true that 2PL offers serializability. However, it does not ensure that deadlocks do not happen. In the diagram above, local and global deadlock detectors search for deadlocks and resolve them by resuming transactions to their initial states.

Strict Two-Phase Locking Method

Strict 2PL is almost the same as 2PL. The only difference is that Strict-2PL never releases a lock after using it. It holds all locks until the commit point and releases them all at once when the process is over.

Centralized 2PL

In Centralized 2PL, a single site is responsible for the lock management process. It has only one lock manager for the entire DBMS.

Primary Copy 2PL

In the Primary Copy 2PL mechanism, many lock managers are distributed to different sites, and a particular lock manager is responsible for managing the lock for a set of data items. When the primary copy is updated, the change is propagated to the slaves.

Distributed 2PL

In this mechanism, lock managers are distributed to all sites and are responsible for managing locks for data at that site. If no data is replicated, it is equivalent to Primary Copy 2PL. The communication costs of Distributed 2PL are quite a bit higher than those of Primary Copy 2PL.

Timestamp-Based Protocols

The Timestamp-based protocol in DBMS is an algorithm that uses the system time or a logical counter as a timestamp to serialize the execution of concurrent transactions. It ensures that every conflicting read and write operation is executed in timestamp order.

The older transaction is always given priority in this method. It uses system time to determine the timestamp of the transaction, and it is the most commonly used concurrency protocol. Lock-based protocols manage the order between conflicting transactions when they execute; timestamp-based protocols manage conflicts as soon as an operation is created.

Example:

Suppose there are three transactions T1, T2, and T3.
T1 has entered the system at time 0010
T2 has entered the system at 0020
T3 has entered the system at 0030
Priority will be given to transaction T1, then T2 and lastly T3.

Advantages:

  • Schedules are serializable, just like 2PL protocols.
  • No waiting for the transaction, which eliminates the possibility of deadlocks.

Disadvantages: Starvation is possible if the same transaction is restarted and continually aborted.

Validation-Based Protocol

The Validation-based protocol in DBMS, also known as the optimistic concurrency control technique, is a method to avoid concurrency conflicts in transactions. In this protocol, local copies of the transaction data are updated rather than the data itself, which results in less interference during execution.

The validation-based protocol is performed in three phases:

  1. Read Phase
  2. Validation Phase
  3. Write Phase

Read Phase

In the read phase, data values can be read by a transaction, but write operations or updates are only applied to the local data copies, not the actual database.

Validation Phase

In the validation phase, the data is checked to ensure that applying the updates will not violate serializability.

Write Phase

In the write phase, the updates are applied to the database if validation is successful; otherwise the updates are discarded and the transaction is rolled back.

Comparison of Concurrency Control Protocols

The four protocol families make different bets about how often transactions actually conflict. The table below summarises where each fits.

Protocol Approach Deadlock Best when
Lock-Based Pessimistic, locks before access Possible Conflicts are frequent
Two-Phase Locking Pessimistic, growing and shrinking phases Possible Serializability is required
Timestamp-Based Orders by timestamp Deadlock-free Ordering matters, waiting is costly
Validation-Based Optimistic, validate before write Deadlock-free Conflicts are rare

In short, lock-based and 2PL protocols assume conflict is common and prevent it up front, while timestamp and validation protocols assume conflict is rare and resolve it only when it appears.

Characteristics of a Good Concurrency Protocol

An ideal concurrency control mechanism has the following objectives:

  • It must be resilient to site and communication failures.
  • It allows the parallel execution of transactions to achieve maximum concurrency.
  • Its storage mechanisms and computational methods should be modest to minimize overhead.
  • It must enforce some constraints on the structure of the atomic actions of transactions.

FAQs

A shared lock permits concurrent reads but no writes, so several transactions can hold it. An exclusive lock permits reading and writing and cannot be shared, so only one transaction holds it.

No. 2PL guarantees serializability but not freedom from deadlock. Two transactions can still wait on each other’s locks, so a separate detection or timeout mechanism is still needed.

When conflicts are rare. Validation-based control avoids lock overhead and lets transactions run freely, checking only at commit. Under heavy contention it wastes work through frequent rollbacks.

AI studies past workloads to predict which transactions will conflict, then recommends an isolation level or lock granularity that raises throughput while keeping results serializable.

It never makes a transaction wait. A conflicting operation is either allowed by timestamp order or the transaction is aborted and restarted, so no circular wait can form and deadlock cannot occur.

Summarize this post with: