Bug #48675 | Broken Phantom lock breaks ACID compliance | ||
---|---|---|---|
Submitted: | 10 Nov 2009 21:26 | Modified: | 26 Feb 2010 15:38 |
Reporter: | Bob Hansen | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.1.40, 5.0, 5.1, 5.5.99 | OS: | Any (MS Windows XP SP2, Linux) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | Broken, For Update, Gap, GROUP, lock, phantom, regression, space |
[10 Nov 2009 21:26]
Bob Hansen
[11 Nov 2009 4:35]
Valeriy Kravchuk
Thank you for the problem report. Please, send the results of: show variables like '%isolation%'; show variables like '%_bin%'; from your current environment.
[11 Nov 2009 14:31]
Bob Hansen
show variables like '%isolation%'; tx_isolation REPEATABLE-READ show variables like '%_bin%'; innodb_locks_unsafe_for_binlog OFF log_bin OFF log_bin_trust_function_creators OFF log_bin_trust_routine_creators OFF max_binlog_cache_size 4294963200 max_binlog_size 1073741824 sql_log_bin ON sync_binlog 0
[15 Jan 2010 18:32]
Bob Hansen
Can I get a status update on this issue? Thanks.
[3 Feb 2010 11:30]
Sveta Smirnova
Thank you for the feedback. I was able to repeat described behavior, but in the initial description you wrote: > // success <-- used to give timeout error But I can not repeat timeout error with SELECT ... FOR UPDATE and older versions. Is it mistake or which version did you run? Could you please try with following reduced test case: --source include/have_innodb.inc create table t1(f1 bigint, f2 bigint, primary key(f1,f2)) engine=innodb; insert into t1 values(10,12); insert into t1 values(10,13); insert into t1 values(10,14); insert into t1 values(20,2); insert into t1 values(20,3); insert into t1 values(20,4); begin; select * from t1 where f1=10 for update; connect (addconroot, localhost, root,,); connection addconroot; begin; select * from t1 where f1=20 for update; insert into t1 values(20,1);
[3 Feb 2010 14:57]
Bob Hansen
Based upon my submission, I think that the locking behavior was working correctly in version 5.1.35. I executed your request in two different MySQL Command Line Clients. ------------------------- Client 1: ------------------------- Enter password: ********* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 655 Server version: 5.1.40-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use noname Database changed mysql> create table t1(f1 bigint, f2 bigint, primary key(f1,f2))engine=innodb; Query OK, 0 rows affected (0.13 sec) mysql> insert into t1 values(10,12); Query OK, 1 row affected (0.05 sec) mysql> insert into t1 values(10,13); Query OK, 1 row affected (0.03 sec) mysql> insert into t1 values(10,14); Query OK, 1 row affected (0.05 sec) mysql> insert into t1 values(20,2); Query OK, 1 row affected (0.03 sec) mysql> insert into t1 values(20,3); Query OK, 1 row affected (0.03 sec) mysql> insert into t1 values(20,4); Query OK, 1 row affected (0.05 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where f1=10 for update; +----+----+ | f1 | f2 | +----+----+ | 10 | 12 | | 10 | 13 | | 10 | 14 | +----+----+ 3 rows in set (0.00 sec) mysql> ------------------------- Client 2: ------------------------- Enter password: ********* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 656 Server version: 5.1.40-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use noname Database changed mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where f1=20 for update; +----+----+ | f1 | f2 | +----+----+ | 20 | 2 | | 20 | 3 | | 20 | 4 | +----+----+ 3 rows in set (0.00 sec) mysql> insert into t1 values(20,1); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
[3 Feb 2010 21:52]
Sveta Smirnova
Thank you for the feedback. Verified as described, although I can not confirm regression. Thats probably not a bug at all, but documentation at http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html says: "Gap locking is not needed for statements that lock rows using a unique index to search for a unique row."
[3 Feb 2010 22:18]
Bob Hansen
I would agree with that statement. A Primary Key counts as a unique index, so when using the Primary Key to search, there should be no gap locking. In your example using a SELECT..WHERE f1=20 AND f2=3 will indeed just lock just the one record. Since we are not specifying [the entire] Primary Key, MySQL locks the records on either side of (20,3). It is NOT doing this, and that was my original concern.
[24 Feb 2010 19:47]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Modified description for gap locking: Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)
[25 Feb 2010 21:41]
Bob Hansen
Thanks guys. Will this be in 5.1.45?
[25 Feb 2010 23:34]
Paul DuBois
Bob, Will what be in 5.1.45? The documentation is updated now: http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html
[26 Feb 2010 15:38]
Bob Hansen
I'm still under question that this is correct behavior. The original issue was that we locked a gap successfully, but were not able to insert into the same gap we just locked. As stated, this is different behavior than how the server used to act. Are you saying that the old behavior was incorrect and that the new behavior was intended? User 1 locks these records: not locked - (91000438,22) locked - [gap 1] locked - (91000447,11) locked - (91000447,12) locked - (91000447,13) locked - (91000447,14) locked - (91000447,15) locked - (91000447,16) locked - (91000447,17) locked - (91000447,18) locked - (91000447,19) locked - (91000447,20) locked - (91000447,21) locked - (91000447,22) locked - (91000447,23) locked - (91000447,24) locked - (91000447,25) locked - (91000447,26) locked - (91000447,27) locked - (91000447,28) locked - (91000447,29) locked - (91000447,30) locked - (91000447,31) locked - (91000447,32) locked - (91000447,33) locked - (91000447,34) locked - [gap 2] not locked - (91040418,2) User 2 tries to lock StuRefNum 91040418. Since the primary key is (StuRefNum,RecordNum) and they do not specify RecordNum in the SELECT..FOR UPDATE then the server has no choice but to also lock the gap on each side. This is the same as is expected by the documentation "If id is not indexed or has a nonunique index, the statement does lock the preceding gap." "For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum”...this next-key lock locks only the gap following the largest index value." In this case, the SELECT..FOR UPDATE will timeout because the gap immediately before 91040418 (gap 2) is already locked by the first user. "If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order." This is how the server USED to work but now it doesn't. Now the SELECT..FOR UPDATE gives SQL_SUCCESS but a follow-up INSERT into the same gap gives SQL_ERROR. Doesn't that break the Consistency rule of ACID compliance? I think the SELECT..FOR UPDATE timing out used to be correct behavior and should be reobtained.