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-
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-
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.
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.
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.
Timestamp ordering protocol works as follows −
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.