Bug #40746 SQL locks more rows that we asked to lock

• Submitted: 14 Nov 2008 15:03
• Modified: 14 Nov 2008 15:37
• Reporter: Bob Hansen
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.67
• OS: Microsoft Windows (XP SP2)
• CPU Architecture: Any
• Tags: Lock Subset Too Much Locked

[14 Nov 2008 15:03] Bob Hansen

Description:
I have a table with a two column primary key. When I lock a subset of the data, a larger subset actually becomes locked. This does not become apparent though until you INSERT a record for a kid outside of the intended upset, at which time you get a timeout error.

For example, the primary key is StuRefNum, RecordNum combination. If I lock student 3, student 7 and 10 may also somehow get locked. I can lock student 7, but as soon as I go to add a new record for student 7, I get a timeout error. Big problem.

How to repeat:
Setup table and data:

CREATE TABLE `HealthHistory` (
  `StuRefNum` int(10) unsigned NOT NULL default '0',
  `RecordNum` smallint(5) unsigned NOT NULL default '0',
  `RecordType` smallint(6) default NULL,
  `Flag` smallint(6) default NULL,
  `FieldA` char(9) default NULL,
  `FieldB` char(9) default NULL,
  `Date` date default NULL,
  `Grade` char(3) default NULL,
  `Comment` char(51) default NULL,
  PRIMARY KEY  (`StuRefNum`,`RecordNum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `HealthHistory` (`StuRefNum`,`RecordNum`,`RecordType`,`Flag`,`FieldA`,`FieldB`,`Date`,`Grade`,`Comment`) VALUES 
 (104050209,1,12,0,'','','2008-11-13','10',''),
 (104050209,2,12,0,'','','2008-11-13','10',''),
 (161050209,1,12,0,'','','2008-11-13','11',''),
 (161050209,2,12,0,'','','2008-11-13','11',''),
 (162050209,1,12,0,'','','2008-11-13','11',''),
 (211079109,1,12,0,'','','2008-11-13','11',''),
 (211079109,2,12,0,'','','2008-11-13','11',''),
 (770069109,1,12,0,'','','2008-11-13','9',''),
 (770069109,2,12,0,'','','2008-11-13','9',''),
 (880089109,1,12,0,'','','2008-11-13','9',''),
 (880089109,2,12,0,'','','2008-11-13','9','');

Open two MySQL Command Line Clients:

On Client #1:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT RecordType,Flag,FieldA,FieldB,Date,Grade,Comment FROM HealthHistor
y WHERE StuRefNum='211079109' FOR UPDATE;
+------------+------+--------+--------+------------+-------+---------+
| RecordType | Flag | FieldA | FieldB | Date       | Grade | Comment |
+------------+------+--------+--------+------------+-------+---------+
|         12 |    0 |        |        | 2008-11-13 | 11    |         |
|         12 |    0 |        |        | 2008-11-13 | 11    |         |
+------------+------+--------+--------+------------+-------+---------+
2 rows in set (0.00 sec)

On Client #2:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT RecordType,Flag,FieldA,FieldB,Date,Grade,Comment FROM HealthHistor
y WHERE StuRefNum='162050209' FOR UPDATE;
+------------+------+--------+--------+------------+-------+---------+
| RecordType | Flag | FieldA | FieldB | Date       | Grade | Comment |
+------------+------+--------+--------+------------+-------+---------+
|         12 |    0 |        |        | 2008-11-13 | 11    |         |
+------------+------+--------+--------+------------+-------+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO HealthHistory (StuRefNum,RecordNum) VALUES ('162050209','3');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show warnings;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1205 | Lock wait timeout exceeded; try restarting transaction |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Something is getting locked that shouldn't be getting locked under the hood. Or I'm doing something very wrong, however unobvious.

[14 Nov 2008 15:37] Valeriy Kravchuk

Sorry, but this is expected and documented behaviour. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html:

"In row-level locking, InnoDB uses an algorithm called next-key locking. InnoDB performs the row-level locking in such a way that when it searches or scans an index of a table, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index record locks.

In addition, the next-key lock that InnoDB sets on an index record affects the “gap” before that index record. If a user has a shared or exclusive lock on record R in an index, another user cannot insert a new index record immediately before R in the index order. (A gap lock refers to a lock that locks only a gap before some index record.)"

Your case, exactly.