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.
mysql> create table t1(c1 int not null auto_increment primary key)Engine=InnoDB;
Query OK, 0 rows affected (0.14 sec)
mysql> insert into t1 values(),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.12 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into t1 values(),(),(),(),(),(),(),(),(),();
Query OK, 10 rows 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 |
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(10);
ERROR 1062 (23000): Duplicate entry ‘10′ for key ‘PRIMARY’
|
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where c1=10 for update;
|
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.
View Venu Anuganti's profile
CNET Review