UNIT-V DBMS Concurrency Control
UNIT-V
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
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 TY 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 TY 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.
AD
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
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.
o
Then at time t4, transaction TY reads account A that
will be read as $350.
o
Then at time t5, transaction TX rollbacks due to server
problem, and the value changes back to $300 (as initially).
o
But the value for account A remains $350 for transaction TY as
committed, which is the dirty read and therefore known as the Dirty Read Problem.
Unrepeatable Read Problem (W-R
Conflict)
Also known as Inconsistent Retrievals Problem
that occurs when in a transaction, two different values are read for the same
database item.
For example:
Consider two transactions, TX and TY,
performing the read/write operations on account A, having an available balance
= $300. The diagram is shown below:
o
At time t1, transaction TX reads the value from account
A, i.e., $300.
o
At time t2, transaction TY reads the value from account
A, i.e., $300.
o
At time t3, transaction TY updates the value of account
A by adding $100 to the available balance, and then it becomes $400.
o
At time t4, transaction TY writes the updated value,
i.e., $400.
o
After that, at time t5, transaction TX reads the
available value of account A, and that will be read as $400.
o
It means that within the same transaction TX, it reads two
different values of account A, i.e., $ 300 initially, and after updation made
by transaction TY, it reads $400. It is an unrepeatable read and is
therefore known as the Unrepeatable read problem.
Thus, in order to maintain consistency in the database and avoid such
problems that take place in concurrent execution, management is needed, and
that is where the concept of Concurrency Control comes into role.
Concurrency Control
Concurrency Control is the working concept that is required for
controlling and managing the concurrent execution of database operations and
thus avoiding the inconsistencies in the database. Thus, for maintaining the
concurrency of the database, we have the concurrency control protocols.
Concurrency Control Protocols
The concurrency control protocols ensure the atomicity, consistency, isolation,
durability and serializability of
the concurrent execution of the database transactions. Therefore, these
protocols are categorized as:
o
Lock Based Concurrency Control Protocol
o
Time Stamp Concurrency Control Protocol
o
Validation Based Concurrency Control Protocol
We will understand and discuss each protocol one by one in our next
sections.
Lock-Based Protocol
In this type of protocol, any transaction cannot read or write data
until it acquires an appropriate lock on it. There are two types of lock:
1. Shared lock:
o
It is also known as a Read-only lock. In a shared lock, the data item
can only read by the transaction.
o
It can be shared between the transactions because when the transaction
holds a lock, then it can't update the data on the data item.
2. Exclusive lock:
o
In the exclusive lock, the data item can be both reads as well as
written by the transaction.
o
This lock is exclusive, and in this lock, multiple transactions do not
modify the same data simultaneously.
AD
There are four types of lock
protocols available:
1. Simplistic lock protocol
It is the simplest way of locking the data while transaction. Simplistic
lock-based protocols allow all the transactions to get the lock on the data
before insert or delete or update on it. It will unlock the data item after
completing the transaction.
2. Pre-claiming Lock Protocol
o
Pre-claiming Lock Protocols evaluate the transaction to list all the
data items on which they need locks.
o
Before initiating an execution of the transaction, it requests DBMS for
all the lock on all those data items.
o
If all the locks are granted then this protocol allows the transaction
to begin. When the transaction is completed then it releases all the lock.
o
If all the locks are not granted then this protocol allows the
transaction to rolls back and waits until all the locks are granted.
3. Two-phase locking (2PL)
o
The two-phase locking protocol divides the execution phase of the
transaction into three parts.
o
In the first part, when the execution of the transaction starts, it
seeks permission for the lock it requires.
o
In the second part, the transaction acquires all the locks. The third phase
is started as soon as the transaction releases its first lock.
o
In the third phase, the transaction cannot demand any new locks. It only
releases the acquired locks.
There are two phases of 2PL:
Growing phase: In the growing phase, a new lock on the data
item may be acquired by the transaction, but none can be released.
Shrinking phase: In the shrinking phase, existing lock held by
the transaction may be released, but no new locks can be acquired.
In the below example, if lock conversion is allowed then the following
phase can happen:
1.
Upgrading of lock (from S(a) to X (a)) is allowed in growing phase.
2.
Downgrading of lock (from X(a) to S(a)) must be done in shrinking phase.
Example:
The following way shows how unlocking and locking work with 2-PL.
Transaction T1:
o
Growing phase: from step 1-3
o
Shrinking phase: from step 5-7
o
Lock point: at 3
Transaction T2:
o
Growing phase: from step 2-6
o
Shrinking phase: from step 8-9
o
Lock point: at 6
4. Strict Two-phase locking
(Strict-2PL)
o
The first phase of Strict-2PL is similar to 2PL. In the first phase,
after acquiring all the locks, the transaction continues to execute normally.
o
The only difference between 2PL and strict 2PL is that Strict-2PL does
not release a lock after using it.
o
Strict-2PL waits until the whole transaction to commit, and then it
releases all the locks at a time.
o
Strict-2PL protocol does not have shrinking phase of lock release.
It does not have cascading abort as 2PL does.
Timestamp Ordering Protocol
o
The Timestamp Ordering Protocol is used to order the transactions based
on their Timestamps. The order of transaction is nothing but the ascending
order of the transaction creation.
o
The priority of the older transaction is higher that's why it executes
first. To determine the timestamp of the transaction, this protocol uses system
time or logical counter.
o
The lock-based protocol is used to manage the order between conflicting
pairs among transactions at the execution time. But Timestamp based protocols
start working as soon as a transaction is created.
o
Let's assume there are two transactions T1 and T2. Suppose the
transaction T1 has entered the system at 007 times and transaction T2 has
entered the system at 009 times. T1 has the higher priority, so it executes
first as it is entered the system first.
o
The timestamp ordering protocol also maintains the timestamp of last
'read' and 'write' operation on a data.
Basic Timestamp ordering protocol works as follows:
1. Check the following condition whenever a transaction Ti issues
a Read (X) operation:
o
If W_TS(X) >TS(Ti) then the operation is rejected.
o
If W_TS(X) <= TS(Ti) then the operation is executed.
o
Timestamps of all the data items are updated.
AD
2. Check the following condition whenever a transaction Ti issues
a Write(X) operation:
o
If TS(Ti) < R_TS(X) then the operation is rejected.
o
If TS(Ti) < W_TS(X) then the operation is rejected and Ti is rolled
back otherwise the operation is executed.
Where,
TS(TI) denotes the timestamp of the transaction Ti.
R_TS(X) denotes the Read time-stamp of data-item X.
W_TS(X) denotes the Write time-stamp of data-item X.
Advantages and Disadvantages
of TO protocol:
o
TO protocol ensures serializability since the precedence graph is as
follows:
o
TS protocol ensures freedom from deadlock that means no transaction ever
waits.
o
But the schedule may not be recoverable and may not even be cascade-
free
Validation Based Protocol
Validation phase is also known as optimistic concurrency control
technique. In the validation based protocol, the transaction is executed in the
following three phases:
1.
Read phase: In this phase, the transaction T is read and executed. It is used
to read the value of various data items and stores them in temporary local
variables. It can perform all the write operations on temporary variables
without an update to the actual database.
2.
Validation phase: In this phase, the temporary variable value will be validated
against the actual data to see if it violates the serializability.
3.
Write phase: If the validation of the transaction is validated, then the
temporary results are written to the database or system otherwise the
transaction is rolled back.
Here each phase has the following different timestamps:
Start(Ti): It contains the time when Ti started its
execution.
Validation (Ti): It contains the time
when Ti finishes its read phase and starts its validation phase.
Finish(Ti): It contains the time when Ti finishes its
write phase.
o
This protocol is used to determine the time stamp for the transaction
for serialization using the time stamp of the validation phase, as it is the
actual phase which determines if the transaction will commit or rollback.
o
Hence TS(T) = validation(T).
o
The serializability is determined during the validation process. It
can't be decided in advance.
o
While executing the transaction, it ensures a greater degree of
concurrency and also less number of conflicts.
o
Thus it contains transactions which have less number of rollbacks.
Thomas write Rule
Thomas Write Rule provides the guarantee of serializability order for
the protocol. It improves the Basic Timestamp Ordering Algorithm.
The basic Thomas write rules are as follows:
o
If TS(T) < R_TS(X) then transaction T is aborted and rolled back, and
operation is rejected.
o
If TS(T) < W_TS(X) then don't execute the W_item(X) operation of the
transaction and continue processing.
o
If neither condition 1 nor condition 2 occurs, then allowed to execute
the WRITE operation by transaction Ti and set W_TS(X) to TS(T).
If we use the Thomas write rule then some serializable schedule can be
permitted that does not conflict serializable as illustrate by the schedule in
a given figure:
Figure: A Serializable Schedule that is not Conflict
Serializable
In the above figure, T1's read and precedes T1's write of the same data
item. This schedule does not conflict serializable.
Thomas write rule checks that T2's write is never seen by any
transaction. If we delete the write operation in transaction T2, then conflict
serializable schedule can be obtained which is shown in below figure.
Figure: A Conflict Serializable Schedule
Multiple Granularity
Let's start by understanding the meaning of granularity.
Granularity: It is the size of data item allowed to lock.
Multiple Granularity:
o
It can be defined as hierarchically breaking up the database into blocks
which can be locked.
o
The Multiple Granularity protocol enhances concurrency and reduces lock
overhead.
o
It maintains the track of what to lock and how to lock.
o
It makes easy to decide either to lock a data item or to unlock a data
item. This type of hierarchy can be graphically represented as a tree.
For example: Consider a tree which has four levels of
nodes.
o
The first level or higher level shows the entire database.
o
The second level represents a node of type area. The higher level
database consists of exactly these areas.
o
The area consists of children nodes which are known as files. No file
can be present in more than one area.
o
Finally, each file contains child nodes known as records. The file has
exactly those records that are its child nodes. No records represent in more
than one file.
o
Hence, the levels of the tree starting from the top level are as
follows:
1.
Database
2.
Area
3.
File
4.
Record
AD
In this example, the highest level shows the entire database. The levels
below are file, record, and fields.
There are three additional lock modes with multiple granularity:
Intention Mode Lock
Intention-shared (IS): It contains explicit locking at a lower level
of the tree but only with shared locks.
Intention-Exclusive (IX): It contains explicit
locking at a lower level with exclusive or shared locks.
Shared & Intention-Exclusive (SIX): In this lock, the node
is locked in shared mode, and some node is locked in exclusive mode by the same
transaction.
Compatibility Matrix with Intention Lock Modes: The below table
describes the compatibility matrix for these lock modes:
It uses the intention lock modes to ensure serializability. It requires
that if a transaction attempts to lock a node, then that node must follow these
protocols:
o
Transaction T1 should follow the lock-compatibility matrix.
o
Transaction T1 firstly locks the root of the tree. It can lock it in any
mode.
o
If T1 currently has the parent of the node locked in either IX or IS
mode, then the transaction T1 will lock a node in S or IS mode only.
o
If T1 currently has the parent of the node locked in either IX or SIX
modes, then the transaction T1 will lock a node in X, SIX, or IX mode only.
o
If T1 has not previously unlocked any node only, then the Transaction T1
can lock a node.
o
If T1 currently has none of the children of the node-locked only, then
Transaction T1 will unlock a node.
Observe that in multiple-granularity, the locks are acquired in top-down
order, and locks must be released in bottom-up order.
o
If transaction T1 reads record Ra9 in file Fa,
then transaction T1 needs to lock the database, area A1 and
file Fa in IX mode. Finally, it needs to lock Ra2 in
S mode.
o
If transaction T2 modifies record Ra9 in file Fa,
then it can do so after locking the database, area A1 and file
Fa in IX mode. Finally, it needs to lock the Ra9 in
X mode.
o
If transaction T3 reads all the records in file Fa, then
transaction T3 needs to lock the database, and area A in IS mode. At last, it
needs to lock Fa in S mode.
o
If transaction T4 reads the entire database, then T4 needs to lock the
database in S mode.
Recovery with Concurrent
Transaction
o
Whenever more than one transaction is being executed, then the
interleaved of logs occur. During recovery, it would become difficult for the
recovery system to backtrack all logs and then start recovering.
o
To ease this situation, 'checkpoint' concept is used by most DBMS.
As we have discussed checkpoint in Transaction Processing Concept of this tutorial, so you can go
through the concepts again to make things more clear.
Comments
Post a Comment