Row Locking With MySQL

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.

[gads]

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.

Why this needed?

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.

Pre Check Before Read Locking

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.

[gads]

How to Perform?

You can perform Row Locking in MySQL in two ways.

1) SELECT … FOR UPDATE

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.

Example Query:

[cc lang=”mysql”]
; This Query will not allow other transaction to
; Read the row with id=10
; It will not allow update or delete too.
SELECT * FROM table_name WHERE id=10 FOR UPDATE;
[/cc]

2) LOCK IN SHARE MODE

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.

Example Query:

[cc lang=”mysql”]
; This Query will not allow other transaction to
; Update or Delete the row with id=10
; It will allow read to other transaction.
SELECT * FROM table_name WHERE id=10 LOCK IN SHARE MODE;
[/cc]

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.

[gads]

This way we can serve the fresh data to the user and can handle the concurrent request in better way.

Conclusion

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.