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

Bug #48306	InnoDB: Error: unlock row could not find a 4 mode lock on the record
Submitted:	26 Oct 2009 12:10	Modified:	26 Oct 2009 16:20
Reporter:	Kim Carlsen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.39, 5.1.40	OS:	Any (MacOS + Freebsd)
Assigned to:		CPU Architecture:	Any
Tags:	locks innodb error function

Description:
This bug might be related to Bug #41756.

When using a user created function in a sub sub sub query the following error is logged to mysql error log.

091026 13:00:04  InnoDB: Error: unlock row could not find a 4 mode lock on the record

This didnt happen with mysql 5.0.58 and appeared after upgrade. Im not sure if the error message is critical or not, but its definitely not nice to have in the error log. 

This error has been repeated on two different freebsd servers. 

How to repeat:
mysql = 5.1.39
Storage engine = innodb;
transaction-isolation = READ-COMMITTED;
binlog_format = ROW;

DELIMITER $$
CREATE FUNCTION test_function() RETURNS DATETIME
DETERMINISTIC NO SQL
BEGIN
    RETURN NOW();
END
$$
DELIMITER ;

CREATE TABLE table_a (
   id int(11) PRIMARY KEY,
   data varchar(20),
   dtm datetime
);

INSERT INTO table_a
     VALUES (1, "A", now()),
            (2, "B", now()+interval 1 month);

SELECT (SELECT 1
          FROM table_a b
         WHERE b.id = b.id
           AND b.dtm = (SELECT 1
                          FROM table_a c
                         WHERE c.id = b.id
                           AND c.dtm <= test_function()))
The select query will log the following error in the mysql error log.
091026 13:00:04  InnoDB: Error: unlock row could not find a 4 mode lock on the record
If the test_function() is changed to use now() instead, the error disappears. 

mysql = 5.1.39
Storage engine = innodb;
transaction-isolation = READ-COMMITTED;
binlog_format = MIXED;    <--- format changed

DELIMITER $$
CREATE FUNCTION test_function() RETURNS DATETIME
DETERMINISTIC NO SQL
BEGIN
    RETURN NOW();
END
$$
DELIMITER ;

CREATE TABLE table_a (
   id int(11) PRIMARY KEY,
   data varchar(20),
   dtm datetime
);

INSERT INTO table_a
     VALUES (1, "A", now()),
            (2, "B", now()+interval 1 month);

SELECT (SELECT 1
          FROM table_a b
         WHERE b.id = b.id
           AND b.dtm = (SELECT 1
                          FROM table_a c
                         WHERE c.id = b.id
                           AND c.dtm <= now()))          <--- function changed
The select query will log the following error in the mysql error log.
091026 13:00:04  InnoDB: Error: unlock row could not find a 4 mode lock on the record
If the now() is changed to use "2009-10-26 00:00:00" instead, the error disappears.

For some reason I can not repeat this with recent 5.1.41 from bzr on Mac OS X. Please, check if this problem is repeatable with 5.1.40.

Hi, 

I just tried to install mysql 5.1.40 on a mac
% uname -a
Darwin mac0005 10.0.0 Darwin Kernel Version 10.0.0: Fri Jul 31 22:47:34 PDT 2009; root:xnu-1456.1.25~1/RELEASE_I386 i386

/opt/local/bin/mysql5 --version
mysql5  Ver 14.14 Distrib 5.1.40, for apple-darwin10.0.0 (i386) using readline 6.0

It also reports, when above is run. 
091026 15:10:57  InnoDB: Error: unlock row could not find a 4 mode lock on the record

Did you remember to set isolation-level?

I was able to repeat on Windows Vista 64-bit:

c:\dbs>c:\dbs\5.1\bin\mysqld --defaults-file=c:\dbs\5.1\my.ini --standalone --console
091026 12:47:24 [Note] Plugin 'FEDERATED' is disabled.
091026 12:47:25  InnoDB: Started; log sequence number 0 44233
091026 12:47:25 [Note] Event Scheduler: Loaded 0 events
091026 12:47:25 [Note] c:\dbs\5.1\bin\mysqld: ready for connections.
Version: '5.1.41-Win X64-debug-log'  socket: ''  port: 3510  Source distribution
091026 12:48:46  InnoDB: Error: unlock row could not find a 4 mode lock on the record

Verified as described.

Duplicate of http://bugs.mysql.com/bug.php?id=41756.

While working on Bug#41756 I was unable to reproduce warnings in the error log using the test case from this bug report.