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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SESSION1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tst values(1); Query OK, 1 row affected (0.00 sec) SESSION2: mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> select * from tst; Empty set (0.01 sec) #Session2 does not see any rows as transaction was not commited yet. SESSION1: mysql> commit; Query OK, 0 rows affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in share mode; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> select * from tst for update; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec) #Standard SELECT does not see rows while SELECT for UPDATE and LOCK IN SHARE MODE sees it. |
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.
Comments (56)
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?
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” 🙂
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/
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).
“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.
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 🙂
Hi Peter, How can a join of tables in same order can result in access of rows in different order. Would you please explain?
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
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.
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!
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
There is no way to check if row is locked in MySQL so far,
even though I agree it would be handy sometimes.
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
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.
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
And I misspelled your name too is see now.
Peter, Baron…..easy to become confused 🙂
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.