Home » DBMS

DBMS | Concurrency Control

In this article, we are going to learn about Concurrency Control in DBMS (Database Management Systems).
Submitted by Shruti Singh, on July 21, 2018

Management of concurrent transaction execution is known as “Concurrency Control”. Transaction management in DBMS handles all transaction, to ensure serializability and isolation of transaction. DBMS implement concurrency control technique so that the consistency and integrity of the database can be hold.

The processes of managing simultaneously operation such that update, create, insert, delete on the database without having an interface with one another, known as concurrency control. It can be performed by various methods such as locking technique, time stamp technique, validation based technique.

Locking Technique for Concurrency Control

Locking is a procedure which controls concurrent access of the data. If one transaction is accessing the database, a lock may be denied access to other transaction to prevent the incorrect result. Locking technique is one of the most widely used mechanisms to ensure serializability. The principle of locking technique is that a transaction must obtain read or write lock in the database before it can perform a read or write operation.

There are some basic rules for locking technique are as following:

  • If a transaction has read lock on the data item, it can read the item but can’t update it.
  • If a transaction has read lock on the data item, other transaction can read on data item but can’t write on the data item.
  • If a transaction has to write lock on the data item, it can both read and update data item.
  • If a transaction has to write lock on data item then the other transaction neither read nor write on the data item.

Mode of Locking

There are basically two types of modes in which a data item may be locked:

  1. Shared Lock
  2. Exclusive Lock

1) Shared Lock

If a transaction T has a shared lock on the data item q, then T can read but can’t write on q data item. It is denoted by (S).

2) Exclusive Lock

If transaction T has an exclusive lock on data item q, then T can read and write both on the data item. It is denoted by (X).

In order to access the data item, these conditions should be followed:

  • Transaction T must first lock it. If the data item is already locked by another transaction which is not comparable then T must wait until have been released & unlock.
  • The transaction must hold a lock on data item when it is accessed.
  • After that unlock it.

A matrix given below shows the comparability between two lock modes:

Shared (S) Exclusive(X)
S T T
X F F

Example:

Consider the following 2 transaction T(read(a), read(b)).

T1 T2
read(a)
read(b)
if a=0
then b=b+1
write(b)
read(b)
read(a)
if b=0
then a=a+1
write(a)

Find lock and unlock instruction of the transaction T1 & T2. Then observe the locking protocol? The execution of transaction is deadlock?

Solution:

Lock and unlock instruction:

T1 T2
lock S(a)
read(a)
lock X(b)
read(b)
if a=0
then b=b+1
write(b)
unlock(b)
unlock(a)
lock S(b)
read(b)
lock (a)
read(a)
if b=0
then a=a+1
write(a)
unlock(a)
unlock(b)

T1 T2
lock S(a)
lock S(b)
lock X(b)
lock X(a)

Yes, transaction has deadlock.





Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.



Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing » Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.