Concurrency Control

GDC THANA SEMESTER 6 TH

LECT. ZUBAIR KHAN
Concurrency Control
Concurrency Control in Database Management System is a procedure of managing
simultaneous operations without conflicting with each other. It ensures that
Database transactions are performed concurrently and accurately to produce correct
results without violating data integrity of the respective Database.

Several problems that arise when numerous transactions execute simultaneously in a

random manner are referred to as concurrency control problems.
Concurrency Control
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, it would have a
mix of READ and WRITE operations and hence the concurrency is a challenge.

DBMS Concurrency Control is used to address such conflicts, which mostly occur with
a multi-user system. Therefore, Concurrency Control is the most important element
for proper functioning of a Database Management System where two or more
database transactions are executed simultaneously, which require access to the same
data.
 Concurrent Execution in DBMS
•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.

•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.

•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.
 Potential problems of Concurrency
• Lost Updates occur when multiple transactions select the same row and update the row based on the value selected

• Uncommitted dependency issues occur when the second transaction selects a row which is updated by another
transaction

• Non-Repeatable Read occurs when a second transaction is trying to access the same row several times and reads
different data each time.

• Incorrect Summary issue occurs when one transaction takes summary over the value of all the instances of a
repeated data-item, and second transaction update few instances of that specific data-item. In that situation, the
resulting summary does not reflect a correct result.
Why use Concurrency method?
Reasons for using Concurrency control method is DBMS:

•To apply Isolation through mutual exclusion between conflicting transactions

•To resolve read-write and write-write conflict issues

•To preserve database consistency through constantly preserving execution obstructions

•The system needs to control the interaction among the concurrent transactions. This control is
achieved using concurrent-control schemes.

•Concurrency control helps to ensure serializability

Example of Database Concurrency
For example, when one user is changing data but has not yet saved (committed)
that data, then the database should not allow other users who query the same
data to view the changed, unsaved data. Instead the user should only view the
original data.
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. Following are the Concurrency Control techniques in DBMS:

2.Two Phase 3.Timestamp- 4.Validation-

1.Lock-Based
Locking Based Based
Protocols
Protocol Protocols Protocols
Lock-based Protocols
Lock Based Protocols in DBMS is a mechanism in which a transaction cannot Read or Write
the data until it acquires an appropriate lock. Lock based protocols help to eliminate the
concurrency problem in DBMS for simultaneous transactions by locking or isolating a
particular transaction to a single user.

A lock is a data variable which is associated with a data item. This lock signifies that
operations that can be performed on the data item. Locks in DBMS help synchronize access to
the database items by concurrent transactions.
Lock-based Protocols
There are 3 phases of this protocol
1.Taking the lock control and accessing the data of the transaction

2.Complete the transaction execution

3.Release lock
Lock-based Protocols
Binary Locks: A Binary lock on a data item can either locked or unlocked states.

Shared/exclusive: This type of locking mechanism separates the locks in DBMS based on
their uses. If a lock is acquired on a data item to perform a write operation, it is called an
exclusive lock.
1. Shared Lock (S):
A shared lock is also called a Read-only lock. With the shared lock, the data item can be shared

between transactions. This is because you will never have permission to update data on the

data item.

For example, consider a case where two transactions are reading the account balance of a

person. The database will let them read by placing a shared lock. However, if another

transaction wants to update that account’s balance, shared lock prevent it until the reading

process is over.
1. Shared Lock (S):
For example, consider a case where initially A=100 and there are two transactions

which are reading A. If one of transaction wants to update A, in that case other

transaction would be reading wrong value. However, Shared lock prevents it from

updating until it has finished reading.

1. Shared Lock (S):
•Another transaction that tries to read the same data is permitted to read, but a transaction that tries to
update the data will be prevented from doing so until the shared lock is released.

•Shared lock is also called read lock, used for reading data items only.

•Shared locks support read integrity. They ensure that a record is not in process of being updated during
a read-only request.

•Shared locks can also be used to prevent any kind of updates of record.

•It is denoted by Lock-S.

•S-lock is requested using Lock-S instruction.

2. Exclusive Lock (X):
With the Exclusive Lock, a data item can be read as well as written. This is exclusive and can’t

be held concurrently on the same data item. X-lock is requested using lock-x instruction.

Transactions may unlock the data item after finishing the ‘write’ operation.

For example, when a transaction needs to update the account balance of a person. You can

allows this transaction by placing X lock on it. Therefore, when the second transaction wants

to read or write, exclusive lock prevent this operation.

 2. Exclusive Lock (X):
• When a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the
data.

• This lock remains in place until the transaction holding the lock issues a commit or rollback.

• They can be owned by only one transaction at a time.

• With the Exclusive Lock, a data item can be read as well as written. Also called write lock.

• Any transaction that requires an exclusive lock must wait if another transaction currently owns an exclusive lock or a shared lock
against the requested resource.

• They can be owned by only one transaction at a time.

• It is denoted as Lock-X.

• X-lock is requested using Lock-X instruction.

2. Exclusive Lock (X):
For example, consider a case where initially A=100 when a transaction needs to deduct
50 from A. We can allow this transaction by placing X lock on it. Therefore, when the
any other transaction wants to read or write, exclusive lock prevent it.
Lock Compatibility Matrix :
Lock Compatibility Matrix controls whether multiple
transactions can acquire locks on the same resource at the same
time. If a resource is already locked by another transaction, then
a new lock request can be granted only if the mode of the
requested lock is compatible with the mode of the existing lock.
Lock Compatibility Matrix :
Lock Compatibility Matrix :
•If a resource is already locked by another transaction, then a new lock
request can be granted only if the mode of the requested lock is compatible
with the mode of the existing lock.

•Any number of transactions can hold shared locks on an item, but if any
transaction holds an exclusive lock on item, no other transaction may hold
any lock on the item.
3. Simplistic Lock Protocol

This type of lock-based protocols allows transactions to obtain a lock on every object before
beginning operation. Transactions may unlock the data item after finishing the ‘write’
operation.

4. Pre-claiming Locking

Pre-claiming lock protocol helps to evaluate operations and create a list of required data
items which are needed to initiate an execution process. In the situation when all locks are
granted, the transaction executes. After that, all locks release when all of its operations are
over.
Starvation

Starvation is the situation when a transaction needs to wait for an indefinite period to acquire
a lock.

Following are the reasons for Starvation:

• When waiting scheme for locked items is not properly managed

• In the case of resource leak

• The same transaction is selected as a victim repeatedly

Deadlock

Deadlock refers to a specific situation where two or more processes are waiting for each other
to release a resource or more than two processes are waiting for the resource in a circular
chain.
Two Phase Locking
GDC THANA SEMESTER 6 TH
LECT. ZUBAIR KHAN
Two Phase Locking Protocol
Two Phase Locking Protocol also known as 2PL protocol is a method of concurrency

control in DBMS that ensures serializability by applying a lock to the transaction data

which blocks other transactions to access the same data simultaneously. Two Phase

Locking protocol helps to eliminate the concurrency problem in DBMS.

Two Phase Locking Protocol
This locking protocol divides the execution phase of a transaction into three different parts.

• In the first phase, when the transaction begins to execute, it requires permission for the

locks it needs.

• The second part is where the transaction obtains all the locks. When a transaction releases

its first lock, the third phase starts.

• In this third phase, the transaction cannot demand any new locks. Instead, it only releases

the acquired locks.

Two-Phase Locking protocol
The Two-Phase Locking protocol allows each transaction to make a lock or unlock request in
two steps:

Growing Phase: In this phase a transaction can only acquire locks but cannot release any lock.

The point when a transaction acquires all the locks it needs is called the Lock Point.

Shrinking Phase: In this phase a transaction can only release locks but cannot acquire any.
Timestamp based
Protocol
GDC THANA SEMESTER 6 TH
LECT. ZUBAIR KHAN
Timestamp based Protocol
Timestamp based Protocol in DBMS is an algorithm which uses the System Time or Logical
Counter as a timestamp to serialize the execution of concurrent transactions. The Timestamp-
based protocol ensures that every conflicting read and write operations are executed in a
timestamp order.

The older transaction is always given priority in this method. It uses system time to determine the
time stamp of the transaction. This is the most commonly used concurrency protocol.

Lock-based protocols help you to manage the order between the conflicting transactions when
they will execute. Timestamp-based protocols manage conflicts as soon as an operation is created.
 Example
Suppose there are there transactions T1, T2, and T3.
1. T1 has entered the system at time 0010
2. T2 has entered the system at 0020
3. T3 has entered the system at 0030

Priority will be given to transaction T1, then transaction T2

and lastly Transaction T3.

Validation based Protocol
Validation based Protocol in DBMS also known as Optimistic
Concurrency Control Technique is a method to avoid concurrency
in transactions. In this protocol, the local copies of the transaction
data are updated rather than the data itself, which results in less
interference while execution of the transaction.
Validation based Protocol
The Validation based Protocol is performed in the following three
phases:

✓Read Phase

✓Validation Phase

✓Write Phase
Validation based Protocol
Read Phase

In the Read Phase, the data values from the database can be read by a transaction
but the write operation or updates are only applied to the local data copies, not the
actual database.

Validation Phase

In Validation Phase, the data is checked to ensure that there is no violation of

serializability while applying the transaction updates to the database.