This is a bit surprise when we encountered a case where InnoDB is not releasing its row lock when there is an error condition within the transaction. And I verified with Falcon, Oracle, SQL Server and Sybase; all seemed to work as expected.
For example; just open a transaction in a session and execute a error statement (lets say duplicate key) and on the other new session try to get a row lock on the same record (use where clause with FOR UPDATE) and you will notice that InnoDB blocks on this statement until you issue a explicit rollback or commit. But remember there is nothing happened on the first session other than duplicate error on that row. So, InnoDB should implicitly unlock the row when there is an error; and looks like it is not doing that.
Here is the scenario:
First create a single column table and populate some rows (lets say 20 rows in
this case) on any version of MySQL/InnoDB.
|
1 2 3 |
mysql> <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> t1(c1 <span style="color: #0000ff">int</span> <span style="color: #0000ff">not</span> <span style="color: #0000ff">null</span> auto_increment <span style="color: #0000ff">primary</span> <span style="color: #0000ff">key</span>)Engine=InnoDB; Query OK, 0 <span style="color: #0000ff">rows</span> affected (0.14 sec) mysql> insert <span style="color: #0000ff">into</span> t1 <span style="color: #0000ff">values</span>(),(),(),(),(),(),(),(),(),(); Query OK, 10 <span style="color: #0000ff">rows</span> affected (0.12 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> insert <span style="color: #0000ff">into</span> t1 <span style="color: #0000ff">values</span>(),(),(),(),(),(),(),(),(),(); Query OK, 10 <span style="color: #0000ff">rows</span> affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 |
and then execute the following statements; first in session-I and then in session-II and notice that session-II select statement hangs till you explicitly release the transaction in session-I.
| Session-I | Session-II | ||||
|
|
Even though am not really sure whether this is a bug or feature in InnoDB (I suspect this as a bug); but Oracle, SQL Server or Sybase will not block the select and releases the lock on the duplicate error. Even Falcon engine in MySQL 6.0 does seem to release the lock appropriately.
It’s a feature of innodb to roll back the statement that caused the error and not the entire transaction in some cases. Things like deadlocks will rollback the entire transaction but duplicate key and other errors that are detected in the sql layer will only rollback the current transaction.
There are some details here http://dev.mysql.com/doc/refman/5.0/en/innodb-error-handling.html
The doc says “A duplicate-key error rolls back the SQL statement, if you have not specified the IGNORE option in your statement. “; but apparently thats not the case.
The problem is even on READ COMMITTED I get blocked; It can be relaxed for REPEATABLE READ
Locking in innodb is such that innodb doesn’t know which statements set a lock, only which transactions. Since the transaction isn’t rolled back locks associated with it will still be valid until it’s either rolled back or committed.
I would say it is a bug if you are in autocommit mode. If you are using transaction mode it is trivial whether the DB releases the lock or not before the txn is either or commited.
I would say it is a bug if you are in autocommit mode. If you are using transaction mode it is trivial whether the DB releases the lock or not before the txn is either rolledback or commited.
Venu,
thank you for noticing this. I replied in the forum:
on the REPEATABLE READ level, the user who has executed INSERT … and received a duplicate key error, now knows that a duplicate exists. It makes sense to keep the lock on the duplicate, as then the execution is serializable for the user who received the duplicate key error. REPEATABLE READ in InnoDB is a serializable level, except for consistent read SELECTs.
On the READ COMMITTED level, I do not see any reason for InnoDB to keep the lock on the duplicate. It would make sense to release it.
I have now filed:
http://bugs.mysql.com/bug.php?id=36801
Best regards,
Heikki
[…] Venu Anuganti wrote about an odd locking-problem with MySQLs InnoDB-Engine which I verified on a […]