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:
None 
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
Triage: Needs Triage: D3 (Medium)

[10 Nov 2009 21:26] Bob Hansen
Description:
In the past, a phantom lock disabled your ability to insert new rows. When a table had a Primary Key that consisted of two columns, and you did a SELECT FOR UPDATE using only one column to select, you locked an extra record on each side of the result set. This was working in 5.1.35, but is now behaving incorrectly in 5.1.40.

How to repeat:
Use the attached data set.

Lock two groups of records that are right next to each other using a phantom lock. The first locked group gets the gap on each side. The second locked group should fail because the gap is already locked. It doesn't.

Connection 1
------------
START TRANSACTION;
// success
SELECT * FROM 0809_ScheduleGrades WHERE StuRefNum='91000447' FOR UPDATE;
// success

Connection 2
------------
SELECT * FROM 0809_ScheduleGrades WHERE StuRefNum='91040418' FOR UPDATE;
// success  <-- used to give timeout error
INSERT INTO 0809_ScheduleGrades (StuRefNum,RecordNum,Courses) VALUES ('91040418','1','1050');
// timeout error

Suggested fix:
The second attempted lock should give a timeout error. If you cannot write to the gap, you shouldn't be able to lock it either.
[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.