October 24, 2014

SELECT LOCK IN SHARE MODE and FOR UPDATE

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

In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR UPDATE are more than hints. Behavior will be different from normal SELECT statements. Here is 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 beause Innodb can only lock current version of row. Think about 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 multiversioning.

Lets also think how these modifiers can be user and what do we expect from them in practice. LOCK IN SHARE MODE is actually often used to bypass multiversioning 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 update 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 chose to use SELECT FOR UPDATE. This will ensure you get write locks for all rows at once which reduces chance of deadlocks – lock will not need to be upgraded when 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 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 oppinion few rather important peices missing in Locking implementation of Innodb are:

Lock table Innodb can lock tables but it will still need to set row level locks which is 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 any 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 victum scheduling At this point transaction which made least updates is killed to resolve deadlock. Which means if transaction takes a lot of locks but does not do much updates it may never have 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 good start.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Xaprb says:

    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?

  2. peter says:

    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” :)

  3. Xaprb says:

    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/

  4. peter says:

    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).

  5. Xaprb says:

    “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.

  6. peter says:

    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 :)

  7. Meghan says:

    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

  8. peter says:

    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.

  9. Meghan says:

    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!

  10. Bo says:

    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

  11. peter says:

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

    even though I agree it would be handy sometimes.

  12. Mahesh says:

    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

  13. darseq says:

    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.

  14. darseq says:

    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

  15. darseq says:

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

  16. peter says:

    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.

  17. darseq says:

    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.

  18. peter says:

    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.

  19. NBW says:

    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.

  20. darseq says:

    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.

  21. pavel says:

    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

  22. pratap says:

    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.

  23. peter says:

    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.

  24. darseq says:

    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.

  25. darseq says:

    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.

  26. Victor says:

    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.

  27. darseq says:

    Interesting, but I think the update should actually succeed.
    Did you test with mysql.exe in two console (dos?) boxes?
    Using mysql ensures that you operate directly on the server
    with no other software layers in between. Also I find the
    syntax is_active=is_active rather odd. Always test using the
    most simple case. So do something like is_active=’0′ or so.

    I don’t know about GET_LOCK/RELEASE_LOCK.

    p.s: did you know that a
    ‘SELECT id into tmp from mf_banners where id=192 LOCK IN SHARE MODE’
    together with ‘REPEATABLE_READ’ suffices to get serializability?

  28. Victor says:

    Darseq> I have tested from two console boxes with mysql 5.0.21 on freebsd6 and linux. I also thought that update should succeed, but it does not! It waits for some table level locks which were made by second SELECT FOR UPDATE which in turn waits for X lock. That is why undetectable deadlock happens. But my opinion that that is a bug. Just try yourself (may be i missed something) – start 2 transactions from two consoles and make any SELECT FOR UPDATE for the SAME ROW in both transactions. Second transaction will wait for the lock, then from the first transaction try to UPDATE that row. In my case it blocks both transaction until timeout and it is very very strange! Can anyone explain me why? Or tell me that it is not working just for me because of my stupidness.

  29. peter says:

    Victor,

    I would suggest to try it with recent MySQL 5.0 version and if it still happens report it at bugs.mysql.com

  30. Victor says:

    Problem solved with mysql 5.0.56.

  31. Ben says:

    I don’t think I understand. I thought a deadlock was when two tasks wanted access to a resource that the other had already locked. When something asks for a lock and can not get it because something else has the lock was called lock contention.

    Also, when you say: “Lock table Innodb can lock tables but it will still need to set row level locks which is memory and CPU overhead. For some bulk operations it would be more efficient to use table locks.”, I am curious as to why, if the whole table is locked, row level locks are still needed.

  32. peter says:

    Ben,

    If one thread just tries to lock the resouce which is already locked lock wait happens. If however the locking thread holds resources other threads is waiting for (most simple case) wait would happen forever and it is called deadlock.

    Why Innodb locks row level locks always is because it is designed this way :)

  33. darseq says:

    Ben,

    A deadlock is like Peter explains, but basically you are saying the same.
    So to combine both explanations just consider two transactions A en B and
    one table with rows r1 and r2 in it. Transactions A locks r1, and
    transaction B locks r2, then transaction A wants to lock r2 but has to
    wait to get it, and finally transaction B wants to lock r1 but also has
    to wait. In this scenario transaction A and B are waiting for eachother
    and neither one can ever proceed which is called a deadlock.

    As to why table locks are more efficient is because of this:
    When a transaction wants to access a table it first checks if there is
    a table lock sitting on it. Only after doing this check, row locks come
    into play. So there are actually two levels of locking, first the table
    lock, only then the row locks. Checking only a single table lock is
    less overhead then having to check multiple row locks.

  34. darseq says:

    Ben, just to clarify some things, copied from the mysql manual:

    The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET AUTOCOMMIT = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if AUTOCOMMIT=1, to help old applications avoid unnecessary deadlocks.

  35. Gaditano says:

    Hi everybody,

    I have an a problem and I cannot resolve.

    I don’t know how I can resolve this:

    Transaction 1

    START TRANSACTION;

    UPDATE sginfra_actas SET act_estado_id = ‘9’ WHERE act_id = ‘3713’
    (Affected Row = 1)

    Other statments….

    UPDATE sginfra_actas SET act_estado_id = ‘9’ WHERE act_id = ‘3713’
    (Affected Row = 0)

    I’m in the same transactions.
    Why there is a lock?

    Thansk

    Gaditano

  36. darseq says:

    The first update statement results in a lock for other transactions.
    Since these update statements are in the same transaction, the second
    update statement does not block. However because the row was already
    updated with the first update statement, an identical update will
    affect zero rows because the update has already been made.

  37. jan says:

    is this code what im doing is correct..?
    im using codeigniter framework of this one…engine use is iNNodb
    [code]
    db->simple_query('SET AUTOCOMMIT=0');
    $lockquery = "LOCK TABLE costumer_prof WRITE";

    $db->insert('costumer_prof',$valueAdded);
    if($db->trans_status() === FALSE) //is this ok to used trans_status without trans_start or trans_begin..?
    {
    $db->trans_rollback()
    return 1;
    }
    else
    {
    $db->trans_commit();
    return 0;
    }
    $lockquery = "UNLOCK TABLE";
    }
    ?>
    [/code]
    am i implementing the right way tosh lock table or better to use [code]$lockquery = "LOCK TABLE costumer_prof IN SHARE MODE";[/code]

    hope this thread is still active..i really appreciate for those who reply …i’ll acknowledge it:)

  38. darseq says:

    Hi Jan.

    I do not know which underlying driver the codeigniter framework
    uses so I cannot answer the question of whether you should
    explicitely execute a ‘begin transaction’ or not. You can always
    find out of course by just running two transactions simulatiously
    and see for how long the locks are held.
    As for your second question, whether you should use a WRITE or
    READ lock the rule is as follows: If you update/insert (write) a
    table, then also use a WRITE lock to prevent deadlocks. If you
    would use a READ lock instead, serializability is still
    guaranteed but a deadlock can easily occur:
    transaction A:
    Read record 2 (READ LOCK)
    transaction B:
    Read record 2 (READ LOCK)
    transaction A:
    Write record 2 (blocks)
    transaction B:
    Write record 2 (blocks too, deadlock).

    If you use WRITE locks, this situation can
    never occur.

  39. jan says:

    thanks darseq for the reply..i thought this thread is not already active…mistaken..:).

    okay…as i read your #34 comment..you say that this will not use transaction…as what mysql manual said also..i’ll just try it up..what you said.:)..i’ll just test it..

    im using WRITE LOCK for the insert..another question

    table1 is in a WRITE LOCK,.(insert a data only) so there will be no other user can access that table1 until it is not releases a lock…what if there is another user access that table1 also doing an insert data.so he can’t access the table1..?..so i think that would be the answer..i just want to clear up everything..im in a doubt situation..:)..

    is there a way that i can temporarily stored the data insert by another user..because the table1 will be access by not too many users..(possible there will be a simultaneous execution will happen.)..im just being pessimistic to this one..
    ?
    what is the difference if i use share mode/exclusive mode and using WRITE or read instead..?..

    sorry to ask you many question..:(..but please help me to this one.

  40. darseq says:

    Most DBMS systems use:
    – shared locks (SLOCK) for a read lock.
    – exclusive locks (XLOCK) for a write lock.
    A lock compatibility matrix tells you which
    locks are granted and which are not when
    two (or more) transactions execute. Just do
    a search for ‘lock compatibility, matrix’
    with google.

    So suppose you have two transactions again:
    transaction A: XLOCK table1, update record 6
    transaction B: update record 6
    I would just experiment again to find out whether
    B blocks on A even when B does not set a lock.

  41. darseq says:

    By the way, there is also something called an update
    lock (ULOCK) which is useful when inserting/updating
    a table record while others can still read the record
    even if they use SLOCKS on the same record. But these
    locks are used for records. I do not think that innodb
    implements update locks for tables but I am not sure
    on this.

  42. jan says:

    there is no ULOCK in innodb…:(…

    okay..i’ll just read more about lock compatibility…so it means to say ill be using shared/exclusive rather than write or read…

    is it ok ill put the lock on the query for example this one

    SELECT pep_id, pep_name FROM person WHERE id= $id LOCK IN SHARE MODE;

    OR ill just do it like this

    function InsertData {

    $query = “LOCK TABLE person IN SHARE MODE”;

    $query = INSERT INTO person(pep_id, pep_name) VALUES (‘1′,’John’);
    }

    Thanks Again for the reply Darseq:)

  43. darseq says:

    Locks can be set on multiple levels of granularity.
    If one chooses to lock tables, all other users are
    blocked for as long as the transaction takes to
    finish its job. If one chooses to lock records
    instead, other records in the same table can still
    be accessed, but access time may be slower (the
    dbms requires more resources to lock records than
    to lock a single table).

    To summarize: If you transactions are fast and no
    user interaction happens in between, it is probably
    best to quickly lock the entire table and do the
    work. If transactions may be slow or user dialogs
    are placed in between, you may be better of using
    records locks. If using records locks, than innodb
    knows about two kind of locks: Exclusive and shared.
    Last time I checked, innodb does not support record
    ‘update’ locks (which I think is kind of flawed).

  44. darseq says:

    So the practical explaination is:

    SELECT … FOR UPDATE sets a XLOCK on the accessed record.
    SELECT … LOCK IN SHARE MODE sets a SLOCK on the accessed record.

    In both cases you will get serializability, but to improve
    concurrency, one should only set a XLOCK if the transaction
    actually updates/writes to the record.

  45. jan says:

    i appreaciate so much your help darseq.:)

    i’ll now continue my coding ..i’ve understand it clearly now…:)…

    if i will encounter a problem..i will post again here..:)…

    hope it would be a success^_^..

  46. darseq says:

    No problem, Keep in mind also that the
    transaction isolation level must be set
    to at least repeatable read to ensure
    serializability. Lower levels allow for
    more concurrency but then you will have
    to check whether your transactions keep
    the database in a consistent state. What
    is considered ‘consistent’ is defined by
    you as a programmer.

  47. Jan says:

    darseq,,

    i’ve tried EXCLUSIVE and SHARED LOCK , and i found out..it wont work..i kinda get this error [#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN SHARE MODE' at line 1]

    my syntax is correct i use this one…LOCK TABLE hotels IN SHARE MODE and LOCK TABLE hotels IN EXCLUSIVE MODE…

    ..i test my code in xampp…try to query it.

    and when i replace it with this one ..LOCK TABLE hotels WRITE..it works …also with READ..so strange….

    i’ve read one of this article it says

    so the transactional locks are converted into
    standard READ|WRITE locks with a warning and then work as expected from the conventional
    locks (or, if sql_mode is set to STRICT*, an attempt to acquire a transactional lock
    returns an error).

    heres the link if you want to see it..
    http://bugs.mysql.com/bug.php?id=46663

  48. Jan says:

    i got it…it says their

    LOCK TABLES … IN [EXCLUSIVE|SHARE] MODE syntax does not exist in 5.1

    the version im using now is 5.1 :)…what a mess im doing with my code…its not the code is the problem..my version…lol..:)

  49. Jan says:

    another problem i encounter

    $this->db->query(‘SET AUTOCOMMIT=0′);

    $this->db->query(“LOCK TABLES hotels READ, location WRITE”);

    $this->db->query(“SELECT hotel_id FROM hotels WHERE hotel_id = 1 “);
    $query = $this->db->get();

    $this->db->query(“UPDATE location SET location_id = 5 WHERE location_id = 1″);

    $this->db->query(“UNLOCK TABLES”);

    i can’t access to my hotels table..in my SELECT statement..please help to resolve this one..

  50. Mikhail says:

    Hi.

    I need to select count of specific rows from table, then insert a new record with one field calculated using that value (something like auto_increment but for groups of records). Following queries doesn’t always work:

    T1: BEGIN;
    T2: BEGIN;

    T1: SELECT COUNT(*) INTO @for_later_insert_t1 FROM table1 WHERE field1 = ‘not_existing_const’ AND field2 like ‘const_prefix_%’ FOR UPDATE; — (table1 has a key – field1, field2) selects 0.

    T2: SELECT COUNT(*) INTO @for_later_insert_t2 FROM table1 WHERE field1 = ‘not_existing_const’ AND field2 like ‘const_prefix_%’ FOR UPDATE; — waits.

    T1: INSERT INTO table1 SET field1 = ‘not_existing_const’, field2 = ‘const_prefix_1′, field3 = @for_later_insert_t1;
    T1: COMMIT;

    T2: — unblocks, @for_later_insert_t2 == 1
    T2: INSERT INTO table1 SET field1 = ‘not_existing_const’, field2 = ‘const_prefix_1′, field3 = @for_later_insert_t2;
    T2: COMMIT;

    They doesn’t work:
    * if field2 like ‘const_prefix_%’ is replaced with field2 = ‘const_prefix_1′ (SELECT … FOR UPDATE in that case isn’t locked, and insert causes deadlock).
    * If the SELECT … FOR UPDATE in one transaction is using different key.

    However those queries work if records with field1 = ‘not_existing_const’, field2 = ‘const_prefix_1′ already exist.
    Why deadlock occurs if there are no such records?
    Is it safe to retry the failed transaction? Or transaction is not meant to solve such problems and better use GET_LOCK()/RELEASE_LOCK()?

  51. Mikhail, please use our forums for asking questions such as this.

Speak Your Mind

*