Why Concurrency Control Is Needed:

      Concurrency control is the process of managing simultaneous execution of transactions (managing simultaneous operations on the database) such as queries, updates, inserts, deletes and so on in a multiprocessing database system without having them interfere with one another.

[• Prevents interference when two or more users are accessing database simultaneously and at least one is updating data.

• Although two transactions may be correct in themselves, interleaving of operations may produce an incorrect result.]

When multiple transactions execute concurrently in an uncontrolled or unrestricted manner, then it might lead to several problems. Such problems are called as concurrency problems.

For example: In airline reservations database in which a record is stored for each airline flight. Each record includes the number of reserved seats on that flight as a named (uniquely identifiable) data item, among other information.

Above figure 21.2(a) shows a transaction T1 that transfers N reservations from one flight whose number of reserved seats is stored in the database item named X to another flight whose number of reserved seats is stored in the database item named Y.

Figure 21.2(b) shows a simpler transaction T2 that just reserves M seats on the first flight (X) referenced in transaction T1.

To simplify our example, we do not show additional portions of the transactions, such as checking whether a flight has enough seats available before reserving additional seats.

When a database access program is written, it has the flight number, flight date, and the number of seats to be booked as parameters; hence, the same program can be used to execute many different transactions, each with a different flight number, date, and number of seats to be booked.

For concurrency control purposes, a transaction is a particular execution of a program on a specific date, flight, and number of seats.

In Figure 21.2(a) and (b), the transactions T1 and T2 are specific executions of the programs that refer to the specific flights whose numbers of seats are stored in data items X and Y in the database.

Concurrency problems are classified into four types. They are

1.    Dirty Read Problem

2.    Unrepeatable Read Problem

3.    Lost Update Problem

4.    Phantom read Problem

This property of DBMS allows many transactions to access the same database at the same time without interfering with each other. The primary goal of concurrency is to ensure the atomicity of the execution of transactions in a multi-user database environment. Concurrency controls mechanisms attempt to interleave (parallel) READ and WRITE operations of multiple transactions so that the interleaved execution yields results that are identical to the results of a serial schedule execution.

Lost Update Problem: 

                              This problem occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database items incorrect.

In other words, if transactions T1 and T2 both read a record and then update it, the effects of the first update will be overwritten by the second update.

Lost update problem occurs when two or more transactions are updating same data items simultaneously. The transaction (among the concurrent transactions) that commits at last (lately) will decide the final value of that particular data item.

Suppose that transactions T1 and T2 are submitted at approximately the same time, and suppose that their operations are interleaved as shown in Figure 21.3(a); then the final value of item X is incorrect because T2 reads the value of X before T1 changes it in the database, and hence the updated value resulting from T1 is lost.

Temporary Update (or dirty Read) Problem: This problem occurs when one transaction updates a database item and then the transaction fails for some reason (see Section 21.1.4).Meanwhile, the updated item is accessed (read) by another transaction before it is changed back to its original value.

When one transaction can see intermediate results of another transaction before it has committed.

Figure 21.3(b) shows an example where T1 updates item X and then fails before completion, so the system must change X back to its original value. Before it can do so, however, transaction T2 reads the temporary value of X, which will not be recorded permanently in the database because of the failure of T1. The value of item X that is read by T2 is called dirty data because it has been created by a transaction that has not completed and committed yet; hence, this problem is also known as the dirty read problem.

Incorrect Summary / Inconsistent Analysis problem:

                                                                         If one transaction is calculating an aggregate summary function on a number of database items while other transactions are updating some of these items, the aggregate function may calculate some values before they are updated and others after they are updated.

For example, suppose that a transaction T3 is calculating the total number of reservations on all the flights; meanwhile, transaction T1 is executing. If the interleaving of operations shown in Figure 21.3(c) occurs, the result of T3 will be off by an amount N because T3 reads the value of X after N seats have been subtracted from it but reads the value of Y before those N seats have been added to it.

Unrepeatable Read Problem: Another problem that may occur is called unrepeatable read, where a transaction T reads the same item twice and the item is changed by another transaction T between the two reads. Hence, T receives different values for its two reads of the same item. This may occur, for example, if during an airline reservation transaction, a customer inquires about seat availability on several flights. When the customer decides on a particular flight, the transaction then reads the number of seats on that flight a second time before completing the reservation, and it may end up reading a different value for the item.

Phantom Read Problem: If a transaction reads some variable from buffer and when it reads the same variable later, it finds that the variable actually does not exist, then this problem is known as Phantom Read Problem.

Why Recovery Is Needed   Next Blog