Concurrency Control
Concurrency control is the activity of coordinating concurrent
accesses to a database in a multi-user database management
system (DBMS)
Concurrency control is the procedure in DBMS for managing simultaneous
operations without conflicting with each another. Concurrent access is quite
easy if all users are just reading data. There is no way they can interfere
with one another. Though for any practical database, would have a mix of
reading and WRITE operations and hence the concurrency is a challenge.
Concurrency control is used to address such conflicts which mostly occur
with a multi-user system. It helps you to make sure that database
transactions are performed concurrently without violating the data integrity
of respective databases.
Concurrency Control is Process of managing simultaneous operations on
the database 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.
Need Concurrency control as Several problems can occur when concurrent
transactions execute in an uncontrolled manner.
Potential problems of Concurrency
Here, are some issues which you will likely to face while using the
Concurrency Control method:
Lost Updates - 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 item incorrect.
Successfully completed update is overridden by another user.
Dirty read - 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.
Occurs when one transaction can see intermediate results of another
transaction before it has committed.
Non-Repeatable Read - Non Repeatable read occurs when a
transaction reads same row twice, and get a different value each time.
For example, suppose transaction T1 reads data. Due to concurrency,
another transaction T2 updates the same data and commit, Now if
transaction T1 rereads the same data, it will retrieve a different value.
Incorrect Summary problem - If one transaction is calculating an
aggregate summary function on a number of records while other
transactions are updating some of these records, the aggregate
function may calculate some values before they are updated and
others after they are updated.
Occurs when transaction reads several values but second
transaction updates some of them during execution of first.
Phantom Read –Phantom Read occurs when two same queries are
executed, but the rows retrieved by the two, are different. For example,
suppose transaction T1 retrieves a set of rows that satisfy some
search criteria. Now, Transaction T2 generates some new rows that
match the search criteria for transaction T1. If transaction T1 re-
executes the statement that reads the rows, it gets a different set of
rows this time.
Concurrency Control Protocols
Different concurrency control protocols offer different benefits between the
amount of concurrency they allow and the amount of overhead that they
impose.
Lock-Based Protocols -Two Phase Locking
Timestamp-Based Protocols
Validation-Based Protocols
Multiple Granularity
Multiversion Schemes
Lock-Based Protocols
A lock is a mechanism to control concurrent access to a data item
Data items can be locked in two modes :
o exclusive (X) mode. Data item can be both read as well as
written. X-lock is requested using lock-X instruction.
o shared (S) mode. Data item can only be read. S-lock is
requested using lock-S instruction.
Lock requests are made to the concurrency-control manager by the
programmer. Transaction can proceed only after request is granted.
Lock-compatibility matrix
A transaction may be granted a lock on an item if the requested lock
is compatible with locks already held on the item by other
transactions
Any number of transactions can hold shared locks on an item,
o But if any transaction holds an exclusive on the item no other
transaction may hold any lock on the item.
If a lock cannot be granted, the requesting transaction is made to
wait till all incompatible locks held by other transactions have been
released. The lock is then granted.
Example of a transaction performing locking:
T2: lock-S(A);
read (A);
unlock(A);
lock-S(B);
read (B);
unlock(B);
display(A+B)
Locking as above is not sufficient to guarantee serializability — if
A and B get updated in-between the read of A and B, the
displayed sum would be wrong.
A locking protocol is a set of rules followed by all transactions
while requesting and releasing locks. Locking protocols restrict the
set of possible schedules.
