Row Locking With MySQL

Mysql_connect v/s Mysql_pconnect

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.


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;


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.

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.

  • Netram Dhakar
    December 17, 2011 at 12:49 pm

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

  • Row Locking With MySQL « nazimphp
    February 20, 2012 at 5:09 pm

    […] Source: http://localhost/xpertdev/2011/11/row-locking-with-mysql/ Share this:TwitterFacebookLike this:LikeBe the first to like this post. […]

  • Lord Matt
    June 11, 2012 at 3:14 pm

    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.

  • quocduan
    March 15, 2013 at 9:48 pm

    this better if has few example. Thanks!

  • Revan
    August 1, 2013 at 12:28 pm

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

  • Md.Nurul Osman
    Md.Nurul Osman
    June 18, 2015 at 11:17 am

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

  • Howard
    May 14, 2020 at 3:38 am

    In the Pre Check Before Read Locking section, step 2 says that ‘the query must be in a transactions’, yet in your example, you don’t show how to create the transaction. I’m interested in this because I want to put two SELECT statements in a single transaction and prevent other users of my database from changing the single row of the tables joined together by the first SELECT statement or delete the any of those rows before running the second SELECT statement. Can I just put a ‘START TRANSACTION;’ statement before the two SELECT statements and then follow them with ‘END TRANSACTIONS;’?


Leave a Reply

Your email address will not be published. Required fields are marked *