DBMS Concurrency Control

DBMS Concurrency Control

It is very important to control the concurrency of transactions in a multiprogramming environment where multiple transactions can be performed simultaneously. To ensure atomicity, isolation, and serializability of concurrent transactions, we have concurrency control protocols. Protocols for regulating competition can be narrowly divided into two groups-

• Protocols Based on Lock
• Protocols based on time stamps

Lock-based Protocols

Database systems equipped with lock-based protocols use a mechanism by which data can not be read or written by any transaction until it gets an appropriate lock on it. There are two types of locks-

• Binary Locks-A lock can be in two states on a data item; it is either locked or unlocked.
• Shared / exclusive- Based on their uses, this type of locking mechanism differentiates the locks. If a lock is acquired to perform a writing operation on a data item, it is an exclusive lock. It will lead the database into an inconsistent state by allowing more than one transaction to write to the same data item. Since no data value is changed, read locks are exchanged.

Four types of lock protocols are available—

Simplistic Lock Protocol

Simplistic lock-based protocols allow transactions before a 'write' operation is performed to obtain a lock on any object. Transactions, after completing the 'write' process, can unlock the data item.

Pre-claiming Lock Protocol

Pre-claiming protocols determine their research and make a list of items of data on which they need locks. The transaction asks the system for all the locks it needs in advance before initiating an execution. If all the locks are granted, when all its operations are done, the transaction executes and releases all the locks. The transaction rolls back and waits until all the locks are granted, if all the locks are not granted.

Two-Phase Locking 2PL

This locking protocol divides a transaction's execution process into three parts. In the first part, it seeks approval for the locks it needs when the transaction starts running. The second portion is where all the locks are obtained by the transaction. The third step begins as soon as the transaction releases its first lock. In this step, no new locks can be required by the transaction; it only releases the locks acquired.

Two-phase locking has two phases, one of which is rising, in which the transaction acquires all the locks; and the second phase is shrinking, in which the locks held by the transaction are released.

A transaction must first obtain a shared (read) lock to claim an exclusive (write) lock and then upgrade it to an exclusive lock.

Strict Two-Phase Locking

Strict-2PL's first step is similar to 2PL. The transaction proceeds to execute normally after obtaining all the locks in the first process. But Strict-2PL does not release a lock after using it, in contrast to 2PL. Strict-2PL keeps all locks until the point of resolving and releases all locks at a time.

Strict-2PL does not have cascading abort as 2PL does.

Timestamp-based Protocols

The timestamp based protocol is the most widely used concurrency protocol. As a timestamp, this protocol uses either system time or a logical counter.

The order between the conflicting pairs between transactions at the time of execution is controlled by lock-based protocols, while timestamp-based protocols start working as soon as a transaction is made.

There is a timestamp associated with each transaction, and the order is determined by the age of the transaction. A transaction created at a clock time of 0002 will be older than any other transaction that followed it. Any transaction 'y' entering the system at 0004, for instance, is two seconds younger and the priority is given to the older one.

Furthermore, the latest read and write-timestamp is given for every data item. This helps the system to know when the last operation on the data item 'read and write' was performed.

Timestamp Ordering Protocol

In their conflicting read and write operations, the timestamp-ordering protocol ensures serializability among transactions. This is the responsibility of the protocol system for the execution of a conflicting pair of tasks according to the transaction timestamp values.

• The timestamp of transaction Ti is denoted as TS(Ti).
• Read time-stamp of data-item X is denoted by R-timestamp(X).
• Write time-stamp of data-item X is denoted by W-timestamp(X).

Timestamp ordering protocol works as follows −

• If a transaction Ti issues a read(X) operation −
• If TS(Ti) < W-timestamp(X)
  • Operation rejected.
• If TS(Ti) >= W-timestamp(X)
  • Operation executed.
• All data-item timestamps updated.
• If a transaction Ti issues a write(X) operation −
• If TS(Ti) < R-timestamp(X)
  • Operation rejected.
• If TS(Ti) < W-timestamp(X)
  • Operation rejected and Ti rolled back.
• Otherwise, operation executed.

Thoma's Write Rule

This rule states if TS(Ti) < W-timestamp(X), then the operation is refused and Ti is rolled back.

In order to make the schedule view serializable, time-stamp ordering rules can be modified.

The 'write' operation itself is ignored instead of making Ti roll back.