SELECT LOCK IN SHARE MODE and SELECT FOR UPDATE

Baron wrote a nice article comparing locking hints in MySQL and SQL Server.

In MySQL/Innodb SELECT LOCK IN SHARE MODE and SELECT FOR UPDATE are more than hints. The behavior will be different from normal SELECT statements. Here is a simple example:

What is happening? SELECT for UPDATE and LOCK IN SHARE MODE modifiers effectively run in READ-COMMITTED isolation mode even if current isolation mode is REPEATABLE-READ. This is done because Innodb can only lock the current version of the row. Think about a similar case and row being deleted. Even if Innodb would be able to set locks on rows which no more exist – would it do any good for you? Not really – for example, you could try to update the row which you just locked with SELECT FOR UPDATE but this row is already gone so you would get quite unexpected error updating the row which you thought you locked successfully. Anyway, it is done this way for good all other decisions would be even more troublesome. This complexity is what you have to pay for multi-versioning.

Let’s also think how these modifiers can be useful and what do we expect from them in practice. LOCK IN SHARE MODE is actually often used to bypass multi-versioning and make sure we’re reading most current data, plus to ensure it can’t be changed. This, for example, can be used to read set of the rows, compute new values for some of them and write them back. If we would not use LOCK IN SHARE MODE we could be in trouble as rows could be updated before we write new values to them and such update could be lost. Note I said some of them. If you want to read set of rows and modify all of them you may choose to use SELECT FOR UPDATE. This will ensure you get write locks for all rows at once which reduces the chance of deadlocks – lock will not need to be upgraded when an update happens. SELECT FOR UPDATE also blocks access to the data using LOCK IN SHARE MODE. So by using these two modifiers, you may effectively implement instant data invalidation – using SELECT FOR UPDATE to quickly lock data which is no more correct so it is not used while you recompute it. Note it also works if LOCK IN SHARE MODE is used with selects – standard selects are run in a non-locking mode which means they never lock any rows and just use old row versions if they were updated.

All said above applies to default REPEATABLE-READ mode. With different isolation modes, there could be some differences but logic stills the same.

So these hints are very powerful and helpful for application development but should be used wisely. Do not assume you can simply add SELECT FOR UPDATE to your select and reduce deadlocks if you’re updating selected rows. As query results may chance you need to access how it affects your application and perform changes required.

What is missing in Innodb locking.? In my opinion, few rather important pieces missing in Locking implementation of Innodb are:

Lock table Innodb can lock tables but it will still need to set row-level locks which are memory and CPU overhead. For some bulk operations, it would be more efficient to use table locks. As I tested it really takes some resources.

Unlocking non matched rows Imagine you’re running DELETE FROM USERS WHERE NAME LIKE “%Heikki%”; How many rows do you think will be locked? Actually, all of them, not only ones which are matched by like because locks are taken on Innodb level before MySQL performs like matching, and row is not unlocked if it does not match.

Smarter deadlock victim scheduling At this point transaction which made least updates is killed to resolve deadlock. Which means if a transaction takes a lot of locks but does not do many updates it may never have the chance to complete. The best example would be
INSERT INTO MyISAMTable SELECT * FROM INNODBTable; – A lot of shared locks on Innodb table but no updates. Supporting MySQL hints
“HIGH_PRIORITY” and “LOW_PRIORITY” would probably be a good start.

Share this post

Comments (56)

  • Xaprb

    Your demonstration of multiversioning is very helpful.

    You said “Do not assume you can simply add SELECT FOR UPDATE to your select and reduce deadlocks if you’re updating selected rows. As query results may chance you need to access how it affects your application and perform changes required.”

    If I do a SELECT FOR UPDATE and then update some of the rows, how can I get a deadlock? If the SELECT completes, it means I was able to get write locks on each row returned, so I should not have any lock contention with other transactions, right?

    August 6, 2006 at 4:49 pm
  • peter

    If you do SELECT FOR UPDATE you can get deadlock while SELECT FOR UPDATE is running, if other transaction for example locking same rows in different order. However you will not get deadlock on row updates themselves as they do not add any locks.

    Also this action might not be only one performed by transaction, this is why I’m saying “reduce” rather than “eliminate” 🙂

    August 7, 2006 at 12:51 am
  • Xaprb

    OK, that’s what I thought you meant (I thought about it more after commenting). The approach I’m taking at my current employer is to try to get everything reading and updating the rows in the same order. Usually it’s not a problem, because the ones that happen out of order are programs that get something to do, do a bunch, then update the rows back one at a time — it’s just a matter of sorting them before the update loop (they are typically processed out of PK order because they are processed in some other order, such as priority).

    I wanted to make sure, given that the first query is a SELECT FOR UPDATE and it succeeds, there’s not some other sneaky way to get into a deadlock 🙂 These programs are running only two queries: SELECT FOR UPDATE and then the update. So as far as I can tell, they won’t deadlock with other instances of themselves, though they might deadlock with something else that locks rows out of order.

    I’ve written more about this in my article about the “little known” way to create a deadlock: http://www.xaprb.com/blog/2006/08/03/a-little-known-way-to-cause-a-database-deadlock/

    August 7, 2006 at 1:03 pm
  • peter

    Updating data in the same order is good strategy anyway.
    Yes you’re right SELECT FOR UPDATE should not deadlock with other instances if MySQL executes them same way (which it might not).

    August 8, 2006 at 5:35 am
  • Xaprb

    “if MySQL executes them same way (which it might not)”

    Does that mean “if they do not use the same index” ? If they use the same index, they should lock rows in the same order, right? From what I understand, each row has a pointer to the next row, so a query that is scanning an index will always scan it in the same order, no matter what physical order the data is in.

    Sorry for the 20 questions, but I want to understand this thoroughly 🙂 The InnoDB internals manual sometimes helps, but other times not so much.

    August 8, 2006 at 12:10 pm
  • peter

    Yes. What I meant it could be full table scan if range is large or index range if range is small. Or join order of tables can be different if you execute the join. Well… for joins it is actually even more complicated – even join of tables in same order can result in rows accessed in different order and so in deadlock.

    But these are all exceptions rather than common case 🙂

    August 8, 2006 at 12:16 pm
    • Sunny Gupta

      Hi Peter, How can a join of tables in same order can result in access of rows in different order. Would you please explain?

      April 3, 2018 at 10:57 pm
  • Meghan

    I’ve been searching for a solution to this issue for a while and stumbled across this post during my efforts. I thought perhaps you could provide an answer. I have an application that works with a flat table that will be used by multiple users. If one users is working with a record, I’d like to be able to display a message to the effect of “This record is currently in use” when another users attempts to access that same record. Is this possible?

    Thanks,
    Meghan

    December 4, 2006 at 1:26 pm
  • peter

    Meghan,

    MySQL/Innodb does not have an option to check if given row is locked other than trying to lock it and catching lock wait timeout.

    In your case you could probably have a flag to make the row as “being worked on”.

    If it is especially good idea if locks are long term (and this is probably the only case in which you would like to display the message) – having transaction open while user is entering something in dialog box is not a good idea.

    December 4, 2006 at 1:36 pm
  • Meghan

    Yes, the locks are somewhat long-term and it would be useful to tie a locked row to a particular user. Thanks for the insight!

    December 4, 2006 at 2:44 pm
  • Bo

    Hello. Yes I have the same problem as Meghan. I would like to see if a row is locked or not BEFORE I try to lock it. Im using DotNetConnector from mysql website and the ONLY way to see if an exception occred is to se if there is an exception. Is what realy the only way? Seems to me that using an exception to indicate that you where not able to write data is not a good way to tell the developer that it went wrong. What about a simple boolean? true if yes, it went ok -and false if not. If an internal error happens, of course -throw the exception. I know that the ExecuteOneQuery() is returning a number indicating if the write (update) went good or bad -of it also throws an exception.

    Seems wrong to me

    Is there an other way to check if the row that you want to update it locked? Without exceptions?

    Thanks,
    Bo

    July 5, 2007 at 4:50 am
  • peter

    There is no way to check if row is locked in MySQL so far,

    even though I agree it would be handy sometimes.

    July 5, 2007 at 7:34 am
  • Mahesh

    Hi

    I have four tables
    t1

    t2

    t3

    t4

    I want to retrive information from t2 and insert/update on t3 and t4
    but this process depends on t1.

    What i have done

    1.retrive infromation from table t2
    2.Inside while loop i write condition for LOCKING
    3.
    if(mysql_query(“LOCK TABLE t1 WRITE”)){

    Insert / update in t3 and t4

    update/insert t1

    mysql_query(“UNLOCK TABLES”);
    }

    4.End while loop

    But Its not going inside if(mysql_query(“LOCK TABLE t1 WRITE”)){

    So my code not working properly

    databse are innodb
    I want to execute row level lock

    Please Help on this issue ASAP
    I am looking for your reply

    Thanks

    August 2, 2007 at 10:10 pm
  • darseq

    Hi Baron,

    You say there is no “Unlocking non matched rows” feature. The example you give is:

    DELETE FROM USERS WHERE NAME LIKE “%Heikki%”;

    First of all, mysql will not be able to use indices so indeed all rows are locked.
    But….. This is exactly what you expect. Why? Well suppose that someone else wants
    to insert a new row with NAME set to: “fooHeikkibar”. To allow for serializability,
    the insert statement should block. To make this clear, suppose that the USERS table
    is considered consistent when there is an even number of records (or zero records)
    that have a NAME with Heikki in it. No consider the following schedule:

    BEGIN BEGIN
    [nothing happens here] INSERT “fooHeikkibar”
    DELETE WHERE NAME LIKE “%Heikki%”;
    [nothing happens here] INSERT “barHeikkifoo”
    END END

    In this schedule, the database is not consistent anymore because there will be one
    record remaining in the USERS table that contains the substring Heikki.

    October 20, 2007 at 6:17 am
  • darseq

    Why is my text formatting being messed up?
    I will put the two transactions below each other.
    sigh…

    TIMESTAMP – STATEMENT
    ———————

    T1 – BEGIN
    T2 – [nothing happens here]
    T3 – DELETE WHERE NAME LIKE “%Heikki%”;
    T4 – [nothing happens here]
    T5 – END

    T1 – BEGIN
    T2 – INSERT “fooHeikkibar”
    T3 – [nothing happens here]
    T4 – INSERT “barHeikkifoo”
    T5 – END

    October 20, 2007 at 6:25 am
  • darseq

    And I misspelled your name too is see now.
    Peter, Baron…..easy to become confused 🙂

    October 20, 2007 at 6:29 am
  • peter

    Darseq,

    There is question of locking and isolation mode. First if DELETE runs after first insert it would have to wait on the second transaction to commit, even if it only locks matched rows. So in fact DELETE can’t complete in the example above before insert transaction commits.

    October 20, 2007 at 8:37 am
  • darseq

    Hi Peter,

    You are right, I was mistaking. Of course the DELETE blocks on the INSERT.
    But I think my point still stands. I will assume that the isolation mode
    is to be fully SERIALIZABLE. Then consider this scenario with transactions
    TR1 and TR2:

    TRANSACTION – STATEMENT
    ———————————————————————

    TR1 – BEGIN
    TR1 – DELETE FROM USERS WHERE NAME LIKE “%Heikki%”;

    TR2 – BEGIN
    TR2 – INSERT INTO USERS VALUES(“HeikkiTuuri”);
    TR2 – END

    TR1 – SELECT NAME FROM USERS WHERE NAME LIKE “%Heikki%” LOCK IN SHARE MODE
    TR1 – END

    If the DELETE statement of TR1 would not lock all records, then the INSERT
    statement of TR2 may actually succeed without blocking (depending on the
    initial contents of the USERS table). In my example the SELECT of TR2
    uses a SHARE LOCK (I agree that this is a question of locking). Now the
    DELETE of TR1 does not ‘see’ record “HeikkiTuuri”, whereas the SELECT of
    TR1 does. This is in contraction with the REPEATABLE READ property that
    any serializable execution should have.

    October 20, 2007 at 12:21 pm
  • peter

    darseq,

    Indeed if you have SERIALIZABLE isolation or REPEATABLE-READS you will need to have those locks set, this is similar to why Innodb sets next-key locks in the indexes now.

    For most applications however READ-COMMITED is enough and when locks can be avoided.

    October 20, 2007 at 2:08 pm
  • NBW

    Hi,

    I am working on solving an issue where I am running into some deadlocking. Essentially things look like this (MySQL 5.0.45/Windows/Connector-J-5.1.5):

    Transaction-1
    ————-
    SELECT p FROM assets WHERE asset.id=12

    various other select statements on this and other tables
    ….

    UPDATE assets SET x=1, y=2, z=3 WHERE asset.id=12

    Transaction-2
    ————–
    UPDATE assets SET mod_date=NOW() WHERE asset.id=12

    It appears that the deadlock is occurring because Transaction-1 is upgrading from a S to an X lock but Transaction 2 is already waiting for an X lock on assets since it came in between the SELECT and UPDATE statements in Transaction-1 and hence the deadlock condition.

    The MySQL 5.0 Reference Guide has the following advice:

    Another way to serialize transactions is to create an auxiliary “semaphore” table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion. Note that the InnoDB instant deadlock detection algorithm also works in this case, because the serializing lock is a row-level lock.

    This doesn’t make a lot of sense to me and I gave it a try and it didn’t fix the deadlocking so I am assuming I am misunderstanding things.

    Essentially I added a new table with 1 row (containing a single TinyInt), then as the first statement for every transaction there is an UPDATE semaphore SET lock=1;

    Since this is a row level lock, I fail to see how it will serialize the _entire_ transaction beyond this update and indeed it didn’t work in that fashion.

    Can anyone offer any insight/advice on this topic?

    TIA.

    November 20, 2007 at 8:22 pm
  • darseq

    Hi NBW

    The way I see it is as follows: You enter a transaction and issue the
    UPDATE semaphore SET lock=1 statement. Now when another transaction tries
    to do the same, it waits for the earlier transaction to release the lock.
    That is when the transaction executes the commit statement which is the
    final statement of each transaction. For this to work of course AUTOCOMMIT
    should be disabled. In this way deadlocks can indeed never occur. This is
    because the UPDATE statement sets an EXCLUSIVE lock on the semaphore row.

    November 21, 2007 at 1:41 am
  • pavel

    DO NOT EVER DO THIS:

    update SOMETABLE as fn set fn.counter = (select count(*) from SOMEOTHERTABLE as p where p.X = fn.X LOCK IN share mode);

    Good luck with your locked mysql

    December 24, 2007 at 1:47 pm
  • pratap

    In an ODBC based application, I need to lock a row while selecting it. For this
    I did use SELECT FOR UPDATE in SQLPREPARE() odbc primitive. But it is not working.
    WHat may be the reason, Ia m working on Linux.

    March 17, 2008 at 1:17 am
  • peter

    Pratap, you would do better of asking question on forum and providing complete SQL example.

    Most typical the problem is you do not have transaction open.

    March 17, 2008 at 8:26 am
  • darseq

    Hi Pratab,

    Check the following:

    – Is the transaction isolation level properly set?
    For ODBC use the ODBC API and something like:
    SQLSetConnectAttr(dbc_h,
    SQL_ATTR_TXN_ISOLATION,
    (SQLPOINTER) isolation,
    SQL_IS_INTEGER);

    – Is AUTOCOMMIT set to false?
    For ODBC use the ODBC API and something like:
    SQLSetConnectAttr(dbc_h,
    SQL_ATTR_AUTOCOMMIT,
    (SQLPOINTER)autocommit,
    SQL_IS_UINTEGER);

    – When using ODBC you do not need to specify BEGIN statements. A new transaction start automatically
    after the previous transaction commits.
    To commit a transaction in ODBC, use the API:
    SQLEndTran(SQL_HANDLE_DBC,dbc_h,SQL_COMMIT);
    If you choose to use a ‘sql passthrough’ COMMIT
    statement however, the ODBC driver itself may not
    know about your transaction ending and may not
    automatically start a new one so beware of this.

    March 18, 2008 at 4:47 am
  • darseq

    Oh yeah, and before I forget: A lock only becomes active when
    the statement is actually EXECUTED, so a PREPARE does not
    necessarily lock until the point of actually EXECUTING the
    statement.

    March 18, 2008 at 4:54 am
  • Victor

    Following code cause an undetectable deadlock when run simultaneously. (Lock wait timeout exceeded;)
    START TRANSACTION;
    SELECT id into tmp from mf_banners where id=192 FOR UPDATE;
    UPDATE mf_banners set is_active=is_active where id=192;
    COMMIT;

    And I can’t understand why.
    Transaction 1 takes X lock for record 192, then Transaction 2 try to take the same lock and can’t, so T2 waits for X lock. Then T1 tries to update record (It holds X lock for that record) and it can’t! Why???
    I have serialized access with GET_LOCK and RELEASE_LOCK and that helped to avoid lock wait timeouts, but i’am not sure that they are replication safe. Are they replication safe, especially when used inside stored procedures?
    Thank you for help.

    April 21, 2008 at 6:11 am
  • darseq