Concurrency control techniques
By: Syed Pir Zada
Concurrency
• Accessing the same piece of data by multiple
transactions/users at the same time is called
concurrency.
• A situation in which two or more users access
the same piece of data at the same time.
• Concurrency is common in multiuser
environment.
• Concurrent access may rise to some serious
problems.
2
Concurrency problems
• 1. Lost update problem.
– When two transactions updates the same data concurrently. The last
update overwrites the previous update which results in loss of data.
Time T1 Stock T2
9:00 100
10:30 Read stock =100 Read stock =100
10:31 Update stock
=stock - 90
10:32 10
10:33 Update stock =
stock +30
Stock = 130
T2 update is lost which is overwritten by T1. 3
Concurrency problems
• 2. uncommitted dependency problem
– One transaction updates certain data before other
transaction rollbacks update on the same data.
Time Transaction 1 Transaction 2
T1 - Update qty to 150
T2 Retrieve Qty -
T3 - Rollback
4
Concurrency problem
• 3. Inconsistent analysis problem
• When a transaction reads several values and another
transaction updates some of them during the execution of the
first transaction.
5
Concurrency control techniques
• Some of the main techniques used to control
concurrent execution of transactions are
based on the concept of locking data items.
• A lock is a variable associated with a data item
that describes the status of the item with
respect to possible operations that can be
applied to it.
6
Types of locks
• Binary lock:
– A binary lock can have two states or values: locked
and unlocked or 1 and 0.
– A lock is associated with each data item X. if the
value of the lock on X is 1, item X cannot be
accessed by another transaction.
– If the value of the lock on X is 0, the item can be
accessed when requested.
7
Binary locks
• Two operations lock_item and unlock_item are
used with binary locks.
• A transaction requesting access to an item X by
first issuing a lock_item(X) operation.
• If lock(X)=1 the transaction is forced to wait.
• If lock(X)=0 it is set to 1 (lock the item) and the
transaction is allowed to access item X.
• When the transaction is completed it uses an
unlock_item(X) operation which sets lock(X) to 0
so that X may be accessed by other transaction.
8
• A lock table is maintained for all those items which have
been locked.
• The dbms has a lock manager subsystem to keep track of
and control access to locks.
• If the simple binary lock system is used every transaction
must obey the following rules.
– A transaction T must issue the operation lock_item(X) before
any read_item(X) or write_item(X).
– A transaction T must issue the operation unlock_item(X) after all
read and write operations are completed in T.
– A transaction T will not issue a lock_item(X) item(X) is already
locked.
– A transaction T will not issue an unlock_item(X) operation unless
it already holds the lock on item(X)
9
Shared lock and exclusive lock
• In binary locks at most one transaction can hold a lock
on a given item.
• We should allow several transactions to access the
same item(X) if they all access X for reading purposes
only.
• Shared lock / Read lock:
– Many transactions are allowed to only read the item
simultaneously.
• Exclusive lock/Write lock:
– When a transaction access an item for writing purpose,
exclusive lock is applied. In exclusive lock other
transactions are not allowed to access the locked item.
10
