Hello friends today we will discuss about another interesting topic that is concurrency control techniques. .So we will start the post without wasting time. Here in this post we are going to discuss concurrency control techniques.
Already you know what are concurrency issues happen in a database. If you don’t know about it you can go through that article again. Here’s the post. Concurrency problems. Know we know we have different conflicts in a transaction schedules as well as. If you couldn’t read the post. This is the link to that post. Transaction schedules. In that article we discussed about the conflicts. We saw because of some conflicts we can’t make the schedule serializable.
What are concurrency control techniques
In the real world what we except from database is work with different tasks parallelly and give good user experience to users. If a database cannot process multiple transactions parallelly and users have to wait until one is finish the task. That means it is not that much useful. What we are trying to do is giving ability to work parallelly to the transactions by removing these conflicts.
There are few mechanisms we built to omit this read and write conflicts in a transaction schedule. We call them as concurrency control techniques. Examples for concurrency control techniques are Locking and Time stamping. In this article we are going to discuss about locking concurrency control techniques. Because locking is a huge concept. We can see different different locking mechanisms. Therefore today we will discuss about locking concurrency control technique. If you want to learn about timestamping. Here is the post. Timestamping in database.
Locking mechanism work like a pass. As an real world example, think you want to meet your friend who was hospitalized. To visit the patient hospital issue a pass. In order to visit the patient we need to get the pass. Like that locking act like a pass to a variable. If a operation in a transaction need to perform that operation. Then it has to put a lock to that variable. When we put a lock others can’t come inside. That’s happen here also. When a transaction put a lock to variable others can’t do anything to that variable. This prevent earlier discussed concurrency issues.
There are two types of locking. They are binary lock, shared/exclusive lock. If we gave a definition to the locking mechanism. Locking a process which secures permission to read or write a data item for a transaction.
This is a one type of locking. Earlier I told you there are several types of locking mechanisms are available. So this is one. Here we maintain two locks. There are shared lock and exclusive lock. Now we will see what they are,
Read Lock (S) – Read lock is also known as shared lock. In short we denote it as S lock. Transaction have to put this type of lock if they want to read a data item. They can perform read operation but not write operations. Two transactions can have two read locks or shared locks to same data item since read read we don’t have conflicts.
Write Lock (X) – Write lock is also known as exclusive lock. In short we denote it as X lock. If a transaction wants to perform write operation. Then that transaction has to put write lock to that data item. Two transactions cannot have two exclusive locks to the same data item. Because write operation leads to conflicts.
In locking we prefer something call lock matrix. That depicts how we can put locks. Let’s look at the matrix.
If we take the first cell it denotes that two transactions can have shared lock to the same variable. When one transaction put exclusive lock to a data item another transaction cannot put read lock or exclusive to the same data item.
Conversion of Locks
Now we know how a transaction can acquire locks. There are to types of lock conversions. They are lock upgrade and lock downgrade. Lock conversion means changing the state of locks. By so far I hope you have understood exclusive lock has more power than read lock. When we discussed how read and write operations perform in the databases. We have discussed from write operation we automatically perform read operation. Likewise from exclusive lock we can perform read operations as well as.
When a transaction firstly put a shared lock. If that transaction wants to perform write operation then that transaction has to take exclusive lock. Since exclusive lock has more power than the shared lock. A transaction changing shared lock to exclusive lock for same data item we call as lock upgrade. Lock downgrade means changing exclusive lock to shared lock for a data item. As a summary in simple word lock upgrade means S lock -> X lock and lock downgrade means X lock -> S lock.
Two phase locking protocol
Two phase locking is also another mechanism in locking. Here what happen is we divide transaction in to two phases. Those two phases are growing phase and shrinking phase. You already know about lock conversion now. Applying that knowledge a transaction acquire locks and doing lock upgrade we can call that phase as growing phase. If a transaction releasing it’s locks and downgrading the locks it has then we can call it as shrinking phase.
Two phase locking Algorithms
We can see variations of two phase locking. They are Strict 2PL, rigorous 2PL, Conservative 2PL. 2PL means the two phase locking. Let’s see one by one what these things are. Firstly we will look at strict 2PL. In this locking mechanism a transaction is not going to release any of it’s exclusive locks until it commits or abort.
Rigorous two phase locking is also similar like strict 2PL. In strict 2PL transaction didn’t release only exclusive locks. Rigorous two phase locking transactions does not release both exclusive and shared locks until it’s commit or abort.
In conservative two phase has a different mechanism. That is transaction acquire all the locks at the beginning of the transaction execution. It’s like this. Transactions knows it’s operations and what are the data items. So according to that at the beginning of the execution it will put all the necessary locks it’s need to perform operations for the required data items. Conservative 2PL can leads to one issue that is starvation.
When a transaction acquire all the locks it needs for the data items at the beginning. The chance another transaction can acquire lock to the same item is zero. That transaction cannot take a lock until first transaction releases the lock. Therefore other transaction has to wait. If the lock acquired transaction execution time is a longer one. Then other transaction has to wait also a longer time. Longer waiting time and sometimes that transaction may never get time even to start the execution. Waiting longer time to acquire locks for a data items we can call as starvation.
Dead locks is also another limitation or issue arise because of locking. Here what happen is two transaction or more cannot completes the execution process due to they put lock to each others data items. Let’s see an example. It is easy to understand the concept. Firstly look at the operations.
Now we will see the locks it will acquire.
|X Lock (X)|
|X Lock (Y)|
|Cannot acquire S lock to Y wait|
|Cannot acquire S lock to X wait|
Here you can see T2 cannot commit because it needs a lock to Y data item. It cannot be taken because already T1 has a lock for Y and that is a exclusive lock. As same as T1 cannot go to the commit point. because of T2 acquire exclusive lock for X data items which is T1 needs to perform read operation. Although T1 cannot acquire it. This situation we known as dead lock. Conservative 2PL can prevent dead locks but it will leads to the starvation.
Then we have come to the end of the article. Today we have discussed about locking technique which is a concurrency control technique. If you have any doubts regarding locking please comment and let us know. We can write another post about it help you to clear your doubts. If you want article about any other topics you can comment us and let us know. Please give us a comment if this post helps you to improve your knowledge. Share it among friends to help them also. We will meet again soon. Till then goodbye!