MySQL has several types of Table Storage Engines. Each types can be used based on need. Like if your database has large number of READ operation rather than INSERT and UPDATE then its better to use MyISAM.
But for large number of INSERT and UPDATE operations its better to use the InnoDB. InnoDB follows the ACID property using the transaction which consist of the features like commit, rollback and crash-recovery.
InnoDB also support locking and consistent read for multi user concurrency and performance.
In this article I am going to cover Row Locking with MySQL.
Main reason to use these locking is to handle the concurrent requests in proper way. This is the must required features when you are dealing with important data just like financial details. Let’s check with an example:
1) Suppose we have two concurrent users tries to access the same row. First user tries to deduct the user’s account balance and second user tries to check the account balance to perform some action.
2) So if first request has not placed the lock then it will allow second transaction to read the row and go ahead with its process.
3) But with this locking mechanism second user will able to get the record until first user commit or rollback the transaction.
You need to make sure about few points to make the row locking work.
1) First of your table’s storage engine must be set as InnoDB.
2) Your row locking query must executes after starting the transaction.
Note: MyISAM does not supports row level locking.
You can perform Row Locking in MySQL in two ways.
Any lock placed with the FOR UPDATE will not allow other transactions to read, update or delete the row. Other transaction can read this rows only once first transaction get commit or rollback.
Any lock placed with LOCK IN SHARE MODE will allow other transaction to read the locked row but it will not allow other transaction to update or delete the row.
Other transaction can update or delete the row once the first transaction gets commit or rollback.
So once the first transaction commit or rollback then second transaction which is waiting for first transaction to finish will get an updates row rather than the old one.
This way we can serve the fresh data to the user and can handle the concurrent request in better way.
It is better to use row locking mechanism if your database has high volume of insert and update statements. But few thing to keep in mind is that your table storage is set as InndoDB and your query must executes after starting the transaction.
Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.
View all posts by: Avinash