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.