| Bug #59410 | read uncommitted: unlock row could not find a 3 mode lock on the record | ||
|---|---|---|---|
| Submitted: | 11 Jan 2011 5:52 | Modified: | 5 Apr 2011 9:00 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.6.2 | OS: | Any |
| Assigned to: | Vasil Dimov | CPU Architecture: | Any |
| Tags: | regression | ||
[11 Jan 2011 5:52]
Shane Bester
[11 Jan 2011 5:52]
MySQL Verification Team
doesn't happen in 5.5.9 so this is a new regression in mysql-trunk
[11 Jan 2011 7:12]
Valeriy Kravchuk
Verified on 32-bit Ubuntu 10.04:
openxs@ubuntu:~/dbs/trunk$ bin/mysql --no-defaults -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.2-m5-valgrind-max-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%isolation%';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.03 sec)
mysql> create table `t1`(`a` int)engine=innodb;
Query OK, 0 rows affected (0.37 sec)
mysql> insert into `t1` values (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select 1 from `t1` where `a` <> any (
-> select 1 from `t1` where `a` <> 1 for update)
-> for update;
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.11 sec)
mysql> exit
Bye
openxs@ubuntu:~/dbs/trunk$ tail data/ubuntu.err
110111 9:09:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110111 9:09:45 InnoDB: Compressed tables use zlib 1.2.3
110111 9:09:46 InnoDB: Initializing buffer pool, size = 128.0M
110111 9:09:47 InnoDB: Completed initialization of buffer pool
110111 9:09:47 InnoDB: highest supported file format is Barracuda.
110111 9:09:48 InnoDB: 1.2.1 started; log sequence number 2177805
110111 9:09:49 [Note] Event Scheduler: Loaded 0 events
110111 9:09:49 [Note] /home/openxs/dbs/trunk/bin/mysqld: ready for connections.
Version: '5.6.2-m5-valgrind-max-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
110111 9:10:44 InnoDB: Error: unlock row could not find a 3 mode lock on the record
[11 Jan 2011 12:53]
Marko Mäkelä
Yes, we can easily print the current statement in lock_rec_unlock(), with something like this: size_t stmt_len; … /* Ignore stmt_len, because it should not hurt to see more context (the tail of a multi-statement) in the error message. */ ut_print_timestamp(stderr); fprintf(stderr, " InnoDB: Error: unlock row could not" " find a %lu mode lock on the record\n" "InnoDB: current statement: %s\n", (ulong) lock_mode, innobase_get_stmt(trx->mysql_thd, &stmt_len)); I did not succeed in tracking down the cause yet. There apparently are two or three row_prebuilt_t structs at play. I have to work backwards, try to figure out what causes the prebuilt->new_rec_locks to be set and where the lock is disappearing. This should be a simple bug, because the table is constant during the query.
[14 Jan 2011 23:11]
MySQL Verification Team
Another testcase with different lock mode error in mysql-trunk:
drop table if exists g67;
create table g67(`a` char(1),`b` int)engine=innodb;
insert into g67 values('0',0);
set transaction isolation level read uncommitted;
start transaction;
set @a=(select b from g67 where
(select 1 from g67 where a group by @a=b)
group by @a:=b);
110115 1:10:36 InnoDB: Error: unlock row could not find a 2 mode lock on the record
[16 Jan 2011 21:22]
Marko Mäkelä
I tried the latter test case with and without set optimizer_switch='index_condition_pushdown=off,mrr=off,mrr_cost_based=off'; and it triggered the warning in both cases.
[30 Jan 2011 16:59]
Bugs System
Pushed into mysql-trunk 5.6.2 (revid:vasil.dimov@oracle.com-20110130165639-1pr3opz839b98q5j) (version source revid:vasil.dimov@oracle.com-20110130165522-m0o6al0pn5ig9kv3) (merge vers: 5.6.2) (pib:24)
[28 Mar 2011 18:01]
Vasil Dimov
During
select 1 from `t2` where `a` <> any (
select 1 from `t2` where `a` <> 1 for update)
for update;
on unpatched mysql-trunk-innodb as of vasil.dimov@oracle.com-20110328091558-szb1uh03isp9fhxv from all places where DB_SUCCESS_LOCKED_REC is returned only this one is executed:
2162 static
2163 enum db_err
2164 lock_rec_lock(
...
2190 /* We try a simplified and faster subroutine for the most
2191 common cases */
2192 switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
2193 case LOCK_REC_SUCCESS:
2194 return(DB_SUCCESS);
2195 case LOCK_REC_SUCCESS_CREATED:
2196 return(DB_SUCCESS_LOCKED_REC);
the breakpoint gets hit 10 times, will attach gdb output shortly
[28 Mar 2011 18:01]
Vasil Dimov
gdb output
Attachment: gdboutput.txt (text/plain), 18.57 KiB.
[5 Apr 2011 9:00]
Vasil Dimov
Fixed in vasil.dimov@oracle.com-20110405083646-6rfog7rdnp3r5efc
[5 Jan 2012 21:02]
John Russell
Added to changelog: Corrected a condition that produced an InnoDB message in the error log, unlock row could not find a 3 mode lock on the record. This situation could occur with a combination of a subquery and a FOR UPDATE clause under the READ UNCOMMITTED isolation level. The fix also improves the debuggability of such messages by including the original SQL statements that caused them.
