Question

Q6: To provide a high degree of transaction concurrency in DBMS and ensuring that all schedules are conflict, recoverable and cascadeless, we need some concurrency control mechanisms for that purpose. Briefly explain three concurrency control mechanisms with real-world application for each of those mechanism.

Answer 1

In Database concurrency control ensures that multiple transactions are executed simultaneously while maintaining the ACID properties of the transactions and serializability in the schedules.

While multiple transactions are executed simultaneously It is highly important to maintain the order of execution of those transactions.

Concurrency Control Techniques are :-

1.Lock Based Protocol

2.Time Stamping Protocol

3. Validation Based Protocol

1.Lock Based Protocol:-

Locking-based concurrency control protocols use the concept of locking data items. A lock is a variable associated with a data item that determines whether read/write operations can be performed on that data item.

It is further categorised into two types :-

i).One Phase Locking- In this method, each transaction locks an item before use and releases the lock as soon as it has finished using it.

ii). Two Phase Locking- It divides the execution phase into three parts.

i) In the first part, when the execution of the transaction starts, it seeks permission for the lock it requires.

ii) In the second part, the transaction acquires all the locks. The third phase is started as soon as the transaction releases its first lock.

iii).In the third part, the transaction cannot demand any new locks. It only releases the acquired locks.

Lock is attained Lock is released T Begin T End Time

Above Fig Showing Two Phase Locking Protocol

2.Time Stamping Protocol:-

A timestamp is a unique identifier given by DBMS to a transaction that represents the transaction’s start time.These algorithms ensure that transactions commit in the order dictated by their timestamps. An older transaction should commit before a younger transaction, since the older transaction enters the system before the younger one.

It follow three rules to ensure serializability :-

i) Access Rule − When two transactions try to access the same data item simultaneously, for conflicting operations, priority is given to the older transaction. This causes the younger transaction to wait for the older transaction to commit first.

ii) Late Transaction Rule − If a younger transaction has written a data item, then an older transaction is not allowed to read or write that data item. This rule prevents the older transaction from committing after the younger transaction has already committed.

iii) Younger Transaction Rule − A younger transaction can read or write a data item that has already been written by an older transaction.

3. Validation Based Protocol:-

The transactions are executed in three phase in this protocol read, validation and write phase.Validation phase is also known as optimistic concurrency control technique.The optimistic approach requires neither locking nor time stamping techniques.

In this Protocol first changes are made to the temporary database and after sucessfull validation the changes are written to the permanent database.

(i) During read phase, the transaction reads the database, executes the needed computations and makes the updates to a private copy of the the database values. All update operations of the transactions are recorded in a temporary update file, which is not accessed by the remaining transactions.

(ii) During the validation phase, the transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database. If the validation test is positive, the transaction goes to a write phase. If the validation test is negative, he transaction is restarted and the changes are discarded.

(iii) During the write phase, the changes are permanently applied to the database.

Real life examples;-

Validation Based Protocol is basically used in banking systems to maintain the bank accounts transcations.It is used to maintain the credit and debit of the accounts.

Locking and Time Stamp protocols are used in purchasing movie tickets.This will ensure that at one seat only one person can allocate at a time.

Please Comment if you have any Query.