Concurrency control in Database System

When concurrent transactions were made by several users at the same time, it is likely to unintentionally create some kind of collisions. According to experts, there are three most common concurrent transaction execution problems: lost update, uncommitted dependency, and inconsistency analysis problem. 

3 types of problem caused by concurrency:

1.  The lost update problem. It refers to the situation where a completed-update-operation by one user is overridden by another user. Example: The table 1 below shows the real life scenario, where there are two users which are represented by T3 and T4 columns, when actions of update carried out simultaneously, the balance could goes wrong in the end.

Table 1 Lost update problem

2. The uncommitted dependency problem. It is sometimes known as the dirty read problem, where a transaction is permitted to retrieve, or even update, a value that has been updated by another transaction that has not yet committed. 

Example: Table 2 shows the stages in the transaction history:

1. At time T1, transaction2 updates the value of x. E.g. change from zero to 1.
2. At time T2, transaction1 read the data, the value is 1.
3. At time T3, transaction2 rolls back, setting the field value back to 0.

The uncommitted dependency problem occurs when transaction 1 still using the old value even after transaction 2 had rolled back the data. In another word, transaction1 is using the phantom update value, this can results to a wrong data.

Table 2 Uncommitted dependency problem

3. The inconsistent analysis problem. Inconsistent analysis occurs when a second transaction accesses the same row several ties and reads different data each time. Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. Also, inconsistent analysis involves multiple reads (two or more) of the same row and each time the information is changed by another transaction; thus, the term non-repeatable read (1).

Example: Figure 1 below shows the example happened in real life, as we can see from the grand total, it is supposed to be 175. However, the calculation gone wrong due to the transaction reads several values from a database while a second transaction updates some of them.

Figure 1. Inconsistent analysis problem Source: http://webdocs.cs.ualberta.ca

Concurrency control technique:

The purpose of concurrency control is to preserve database consistency and to resolve read-write conflicts. There are several techniques for concurrency control, such as locking methods, time-stamp methods, and optimistic methods. In this question, we are discussing lock-based protocols.

Lock-based protocols is a technique to maintain consistency, restrict modification of the data item by any other transaction which is presently accessed by some transaction. In order to write the value of a data item, the transaction gave to obtain the lock.

There are two types of lock, namely shared (S) lock, and Exclusive (X) lock. Shared lock is used for read operation such as when SELECT statement is executed; whereas exclusive (X) lock is used for write-operations such as INSERT, UPDATE, and DELETE.

Example: Figure 2 below shows an example three transactions using 2PL. Firstly, transaction 1 executes the operation of B=B+A, transaction 2 executes the operation of C=A+B, and transaction 3 execute the operation of Print C. The concurrency control is necessary because of all three transactions are accessing the data A, B, and C. To prevent data conflict, each transaction is executed according to the 2PL protocol.

Figure 2 Concurrency control by using 2PL Source: CUBRID