Why concurrency Control needed?

If transactions are executed serially, i.e., sequentially with no overlap in time, no transaction concurrency exists. However, if concurrent transactions with interleaving operations are allowed in an uncontrolled manner, some unexpected, undesirable result may occur. Here are some typical
examples:

The Lost Update Problem:

This problem occurs when two transactions that access the same database items have their operations interleaved in such a way that value of the data item written by one transaction is overlapped by another transaction and hence results to the incorrect value of the database item. Consider the example given below where two
concurrent transactions try to update inventory of product P1 simultaneously.

Example: Assume initial value of data item P1 is 300

The Dirty Read (Temporary Update) Problem:

This problem occurs when one transaction updates a database item and then the transaction fails for some reason. The updated item is accessed by another transaction before it is changed back to its original value which causes transaction T2 to have dirty value of the data item. Again consider the example where two concurrent transactions try to update
inventory of product P1 simultaneously. This example is same as above example except that its operations are interleaved in slightly different way and first transaction is failed.

Example: Assume initial value of data item P1 is 300

Log based protocol for concurrency control

One way to ensure serializability is to access the data items in mutually exclusive manner. This means while one transaction is accessing data item, no other transaction should be allowed to access the data item. Lock variables are most commonly used approach for achieving mutual exclusion. Locks are of two kinds: Binary Locks and Shared/Exclusive Locks.

Binary lock

Binary lock is a variable that can be only in two states. It is either locked or unlocked. Normally, locked state is represented by value 1 and unlocked state is represented by value 0. A distinct lock is associated with each database item x. If the value of the lock on data item x is 1, item x cannot
be accessed by a database operation that requests the item. If the value of the lock on x is 0, the item can be accessed when requested. Two operations, lock and unlock, are used with binary locking and these two operations must be implemented atomically. If the simple binary locking scheme described above is used, every transaction must obey the following rules:

1. A transaction T must issue the operation lock(x) before performing any read(x) or
   write(x) operations.
2. A transaction T must issue the operation unlock(x) after finishing all read(x) and write(x)
   operations.
3. A transaction T will not issue a lock(x) operation if the data item x is already locked by
   it.
4. A transaction T will not issue an unlock(x) operation if the data item x is not locked by it

Shared/Exclusive Lock

This type of lock is also called multiple mode lock. It is a variable that can be in any of three states:

unlocked, read-locked (shared lock) and write-locked (exclusive lock).

If a transaction acquires shared lock (read-lock) on data item x then other transactions can also acquire shared lock on data item x. But no transaction can acquire exclusive lock (write-lock) on data item x. On the other hand, if a transaction acquires exclusive lock (write-lock) on data item x then no other transactions can acquire shared/exclusive lock (read/write lock) on the data item. When shared/exclusive locking scheme discussed above is used, the system must enforce the following rules:

1. A transaction T must issue the operation read_lock(x) or write_lock(x) before performing
   read(x) operation.
2. A transaction T must issue the operation write_lock(x) before performing write(x)
   operation.
3. A transaction T must issue the operation unlock(x) after finishing all its read(x) and
   write(x) operations.
4. A transaction T will not issue a read_lock(x) operation if it already holds a shared lock
   (read-lock) or exclusive lock (write-lock) on item x.
5. A transaction T will not issue a write_lock(x) operation if it already holds a shared lock
   (read-lock) or exclusive lock (write-lock) on item x.
6. A transaction T will not issue an unlock(x) operation if it does not hold a shared lock (readlock) or exclusive lock (write-lock) on item x