I am assuming you have gone through transactions and locks before starting this.
Before starting this , let’s discuss an example simple database transaction:
Case:Supppose there are two sessions which are trying to add a discount of 1000 of order value is greater than 4500.
After completion of this if you try to check the state of database, you will get
dirty data .
Only a discount of 1000 should be given but in this case you end up giving 2000 discount. That means only transaction is not the solution of every problem. Now the question arrives is how to make this thread safe ? The answer is locking.
Row level locking:
Shared (S) locks: It permits a transaction to read a row. If transaction of one session has got this lock no transaction of other session can apply X lock on the same row. We can also say that no transaction of some other session can update that row.
Exclusive (X) locks: It permits a transaction to update or delete a row. If transaction of one session has got this lock , no transaction of some other session can apply S or X lock on the same row. In a simple way we can say that other session can’t update that row but it can only read that row without asking for any lock.
If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:
A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
A request by T2 for an X lock cannot be granted immediately.
If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.
Table level locking:
InnoDB supports multiple granularity locking which permits coexistence of row locks and locks on entire tables. The idea behind intention locks is for a transaction to indicate which type of lock (shared or exclusive) it will require later for a row in that table. There are two types of intention locks used in InnoDB:
Intention shared (IS): Allows requestor to lock rows in S or IS mode.
Intention exclusive (IX): Allows requestor to explicitly lock rows in X, S, IX or IS mode.
FOR UPDATE & LOCK IN SHARE MODE in mysql
select .. from .. where .. FOR UPDATE
select .. from .. where .. LOCK IN SHARE MODE
The whole target of this post is to let you know about these two things. Both of these are used with select query. FOR UPDATE locks the entire table in IS mode and the selected rows in S mode. LOCK IN SHARE MODE locks the entire table in IX mode and selected rows in X mode.
Please check the following example for deep understanding.
MySQL must maintain locks on every row it updates until the transaction commits so it can roll them all back at once if the transaction fails or is cancelled.
For any doubts please check this question on Stack Overflow .comments powered by Disqus