Row Locking With MySQL

  • Avinash
  • 7
  • Nov 27, 2011
  • Database

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.

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.

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:

; 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;
  ; 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;

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:

; 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;
  ; 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;

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.

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.

Related Posts

Written by Avinash

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:

  • http://www.comingsoon.com Netram Dhakar

    It’s good but little short …………….

  • Pingback: Row Locking With MySQL « nazimphp

  • http://lordmatt.co.uk Lord Matt

    This was exactly what I was looking for. Ironically I was so stunned by the very cool copy code JavaScript that for ten minutes I forgot what I was originally here to find out. Cheers.

  • Pingback: Metode Lock-Record Pada DBMS Berbasis Web | FranZEast

  • quocduan

    this better if has few example. Thanks!

  • Revan

    Very nice post and very useful……….. But you could have post an example for this.

  • Md.Nurul Osman

    how can i freeze some records for ever for not allowing edit or delete