MySQL Bugs: #53674: InnoDB: Error: unlock row could not find a 4 mode lock on the record

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

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);

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

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.

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.

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.

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).

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.

The bug that Marko noticed is a serious one. InnoDB's locking may 'leak' in a semi-consistent read.

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

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

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

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

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

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

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

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

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:alik@sun.com-20100615080431-gu5icn0anrt47dsx) (pib:16)

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.

hm, the error message in 5.1.48 is gone, but i am not certain it's really fixed in 5.1.48...

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)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:alik@sun.com-20100723121827-3bsh51m5sj6g4oma) (pib:18)

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)

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)

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)

Already documented in the 5.1.49 changelog; no new changelog entries required. setting back to Closed state.