DBMS Concurrency Control
Concurrency Control is the management procedure that is required for
controlling concurrent execution of the operations that take place on a
database.
But before knowing about concurrency control, we should know about
concurrent execution.
Concurrent Execution in DBMS
o In a multi-user system, multiple users can access and use the
same database at one time, which is known as the concurrent
execution of the database. It means that the same database is
executed simultaneously on a multi-user system by different
users.
o While working on the database transactions, there occurs the
requirement of using the database by multiple users for
performing different operations, and in that case, concurrent
execution of the database is performed.
o The thing is that the simultaneous execution that is performed
should be done in an interleaved manner, and no operation should
affect the other executing operations, thus maintaining the
consistency of the database. Thus, on making the concurrent
execution of the transaction operations, there occur several
challenging problems that need to be solved.
Problems with Concurrent Execution
In a database transaction, the two main operations
are READ and WRITE operations. So, there is a need to manage these
two operations in the concurrent execution of the transactions as if
these operations are not performed in an interleaved manner, and the
data may become inconsistent. So, the following problems occur with
the Concurrent Execution of the operations:
,Problem 1: Lost Update Problems (W - W
Conflict)
The problem occurs when two different database transactions perform
the read/write operations on the same database items in an
interleaved manner (i.e., concurrent execution) that makes the values
of the items incorrect hence making the database inconsistent.
For example:
Consider the below diagram where two transactions TX and TY, are
performed on the same account A where the balance of account A is
$300.
o
o At time t1, transaction TX reads the value of account A, i.e., $300
(only read).
o At time t2, transaction TX deducts $50 from account A that
becomes $250 (only deducted and not updated/write).
o Alternately, at time t3, transaction T Y reads the value of account A
that will be $300 only because TX didn't update the value yet.
o At time t4, transaction TY adds $100 to account A that becomes
$400 (only added but not updated/write).
, o At time t6, transaction TX writes the value of account A that will be
updated as $250 only, as TY didn't update the value yet.
o Similarly, at time t7, transaction T Y writes the values of account A,
so it will write as done at time t4 that will be $400. It means the
value written by TX is lost, i.e., $250 is lost.
Hence data becomes incorrect, and database sets to inconsistent.
Dirty Read Problems (W-R Conflict)
The dirty read problem occurs when one transaction updates an item
of the database, and somehow the transaction fails, and before the
data gets rollback, the updated database item is accessed by another
transaction. There comes the Read-Write Conflict between both
transactions.
For example:
Consider two transactions TX and TY in the below diagram performing
read/write operations on account A where the available balance in
account A is $300:
o
o At time t1, transaction TX reads the value of account A, i.e., $300.
o At time t2, transaction TX adds $50 to account A that becomes
$350.
o At time t3, transaction TX writes the updated value in account A,
i.e., $350.
Concurrency Control is the management procedure that is required for
controlling concurrent execution of the operations that take place on a
database.
But before knowing about concurrency control, we should know about
concurrent execution.
Concurrent Execution in DBMS
o In a multi-user system, multiple users can access and use the
same database at one time, which is known as the concurrent
execution of the database. It means that the same database is
executed simultaneously on a multi-user system by different
users.
o While working on the database transactions, there occurs the
requirement of using the database by multiple users for
performing different operations, and in that case, concurrent
execution of the database is performed.
o The thing is that the simultaneous execution that is performed
should be done in an interleaved manner, and no operation should
affect the other executing operations, thus maintaining the
consistency of the database. Thus, on making the concurrent
execution of the transaction operations, there occur several
challenging problems that need to be solved.
Problems with Concurrent Execution
In a database transaction, the two main operations
are READ and WRITE operations. So, there is a need to manage these
two operations in the concurrent execution of the transactions as if
these operations are not performed in an interleaved manner, and the
data may become inconsistent. So, the following problems occur with
the Concurrent Execution of the operations:
,Problem 1: Lost Update Problems (W - W
Conflict)
The problem occurs when two different database transactions perform
the read/write operations on the same database items in an
interleaved manner (i.e., concurrent execution) that makes the values
of the items incorrect hence making the database inconsistent.
For example:
Consider the below diagram where two transactions TX and TY, are
performed on the same account A where the balance of account A is
$300.
o
o At time t1, transaction TX reads the value of account A, i.e., $300
(only read).
o At time t2, transaction TX deducts $50 from account A that
becomes $250 (only deducted and not updated/write).
o Alternately, at time t3, transaction T Y reads the value of account A
that will be $300 only because TX didn't update the value yet.
o At time t4, transaction TY adds $100 to account A that becomes
$400 (only added but not updated/write).
, o At time t6, transaction TX writes the value of account A that will be
updated as $250 only, as TY didn't update the value yet.
o Similarly, at time t7, transaction T Y writes the values of account A,
so it will write as done at time t4 that will be $400. It means the
value written by TX is lost, i.e., $250 is lost.
Hence data becomes incorrect, and database sets to inconsistent.
Dirty Read Problems (W-R Conflict)
The dirty read problem occurs when one transaction updates an item
of the database, and somehow the transaction fails, and before the
data gets rollback, the updated database item is accessed by another
transaction. There comes the Read-Write Conflict between both
transactions.
For example:
Consider two transactions TX and TY in the below diagram performing
read/write operations on account A where the available balance in
account A is $300:
o
o At time t1, transaction TX reads the value of account A, i.e., $300.
o At time t2, transaction TX adds $50 to account A that becomes
$350.
o At time t3, transaction TX writes the updated value in account A,
i.e., $350.
