Bug #53674 InnoDB: Error: unlock row could not find a 4 mode lock on the record
Submitted: 15 May 2010 18:28 Modified: 14 Oct 2010 15:21
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.46, 5.1.48-bzr OS:Any
Assigned to: John Russell CPU Architecture:Any

[15 May 2010 18:28] Shane Bester
Description:
InnoDB: Error: unlock row could not find a 4 mode lock on the record

How to repeat:
start server with --log-bin --innodb-locks-unsafe-for-binlog --binlog-format=mixed

drop table if exists t1;
create table t1(a int)engine=innodb;
insert into t1 values (1),(2);
start transaction;
select * from t1 for update;
select * from t1 where a=(select a from t1 where a >1);
[15 May 2010 18:34] Valeriy Kravchuk
Verified just as described with recent 5.1.48 from bzr:

...
100515 21:32:44 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.48-debug-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
100515 21:33:06  InnoDB: Error: unlock row could not find a 4 mode lock on the record
[16 May 2010 10:18] MySQL Verification Team
innodb plugin outputs this:
InnoDB: Error: unlock row could not find a 2 mode lock on the record

You can use --binlog-format=mixed --transaction-isolation=read-committed
instead of innodb_locks_unsafe_for_binlog to trigger the error messages.
[18 May 2010 8:01] Marko Mäkelä
In the test case, in lock_unlock_rec(), there is only one lock in the lock list. lock->type_mode == LOCK_REC_NOT_GAP | LOCK_REC | LOCK_X, heap_no={2,3}, as expected (heap_no=2 would be the user record (1) and heap_no=3 would be (2)). The lock mode LOCK_X does not match the lock_mode == LOCK_S.
[18 May 2010 8:25] Marko Mäkelä
Fix: we must not attempt unlock_row unless prebuilt->select_lock_type == LOCK_X, that is, the record was locked by an UPDATE, not a SELECT.
[18 May 2010 9:33] Marko Mäkelä
Better fix: in row_search_for_mysql(), leave prebuilt->new_rec_locks at 0 if no new lock was created on the record (a stronger lock had already been acquired).
[18 May 2010 11:41] Marko Mäkelä
For the record, this bug was introduced in the fix of Bug #3300, in implementing semi-consistent reads. The idea of semi-consistent read is twofold:
[1] If the rec is already locked by some other trx, do a consistent read. If that version would not match the search condition, do nothing. If it does match, wait for the lock.
[2] If the record lock was acquired and the record did not match the search condition, release the lock.

This bug is about case [2].

Another possible fix is that we suppress the error printout when unlock_row is attempted on a weaker lock than one that the transaction is holding. That is, keep the current behavior, except do not issue the warning.

But, there is another, possibly more alarming problem in semi-consistent reads (introduced in the Bug #3300 fix) is that we may attempt to unlock a row that was locked by an earlier statement. Consider the following:

create table bug53674(a int)engine=innodb;
set transaction isolation level read committed;
insert into bug53674 values (1),(2);
start transaction;
select * from bug53674 for update;
select * from bug53674 where a=(select a from bug53674 where a > 1) for update;

The second SELECT would unlock the record (1) that was locked by the first SELECT. That can be fixed by changing the type of prebuilt->new_rec_locks from ulint to lock_t*[2] and by assigning pointers to freshly acquired record locks there. In this case, the array would be {NULL,NULL} because the locks were already acquired by the first SELECT. This would require changes to the locking implementation in 5.1 so that it would tell the caller if a new lock was acquired or an old, stronger-or-equal lock reused.
[20 May 2010 12:42] Heikki Tuuri
The bug that Marko noticed is a serious one. InnoDB's locking may 'leak' in a semi-consistent read.
[2 Jun 2010 10:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109922
[2 Jun 2010 10:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109923
[2 Jun 2010 10:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109926
[2 Jun 2010 10:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109927
[2 Jun 2010 10:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109928
[2 Jun 2010 10:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109929
[2 Jun 2010 10:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109930
[2 Jun 2010 10:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109931
[15 Jun 2010 8:22] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (merge vers: 5.5.5-m3) (pib:16)
[15 Jun 2010 8:39] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:alik@sun.com-20100615080431-gu5icn0anrt47dsx) (pib:16)
[28 Jun 2010 23:38] John Russell
Here is how I will describe the fix in the change log:

Some combinations of SELECT and
SELECT FOR UPDATE statements could fail with
errors about locks, or incorrectly release a row lock during a
semi-consistent read operation.
[30 Jun 2010 19:11] MySQL Verification Team
hm, the error message in 5.1.48 is gone, but i am not certain it's really fixed in 5.1.48...
[19 Jul 2010 14:36] Bugs System
Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:vasil.dimov@oracle.com-20100704071244-3lo4okzels3kvy1p) (merge vers: 5.1.49) (pib:16)
[23 Jul 2010 12:28] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (merge vers: 5.5.6-m3) (pib:18)
[23 Jul 2010 12:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:alik@sun.com-20100723121827-3bsh51m5sj6g4oma) (pib:18)
[14 Oct 2010 8:38] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:53] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:10] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 15:21] Jon Stephens
Already documented in the 5.1.49 changelog; no new changelog entries required. setting back to Closed state.