Description:
The following script acquires a transactional lock after beginning transaction two different ways:
- Using an explicit BEGIN
- Using SET to disable autocommit
drop table if exists t;
create table t (i int) engine innodb;
show create table t\G
begin;
lock table t in share mode;
show warnings;
commit;
set autocommit=0;
lock table t in share mode;
show warnings;
The script works in 6.0.5, and until recently worked in 6.0.6 (current BK tree). But now it malfunctions in 6.0.6. The type of malfunction is different for the two attempts to acquire the lock, but on both cases it appears that the server is wrongly attempting to convert the lock request to a request for a non-transactional lock.
How to repeat:
Script output when it works (6.0.5):
mysql> drop table if exists t;
Query OK, 0 rows affected (0.08 sec)
mysql> create table t (i int) engine innodb;
Query OK, 0 rows affected (0.08 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql>
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table t in share mode;
Query OK, 0 rows affected (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table t in share mode;
Query OK, 0 rows affected (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
Script output when it malfunctions (current BK sources):
mysql> drop table if exists t;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t (i int) engine innodb;
Query OK, 0 rows affected (0.22 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql>
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table t in share mode;
ERROR 1615 (HY000): Cannot convert to non-transactional lock in an active transaction on 't'
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------+
| Error | 1615 | Cannot convert to non-transactional lock in an active transaction on 't' |
+-------+------+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table t in share mode;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1613 | Converted to non-transactional lock on 't' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
Description: The following script acquires a transactional lock after beginning transaction two different ways: - Using an explicit BEGIN - Using SET to disable autocommit drop table if exists t; create table t (i int) engine innodb; show create table t\G begin; lock table t in share mode; show warnings; commit; set autocommit=0; lock table t in share mode; show warnings; The script works in 6.0.5, and until recently worked in 6.0.6 (current BK tree). But now it malfunctions in 6.0.6. The type of malfunction is different for the two attempts to acquire the lock, but on both cases it appears that the server is wrongly attempting to convert the lock request to a request for a non-transactional lock. How to repeat: Script output when it works (6.0.5): mysql> drop table if exists t; Query OK, 0 rows affected (0.08 sec) mysql> create table t (i int) engine innodb; Query OK, 0 rows affected (0.08 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> lock table t in share mode; Query OK, 0 rows affected (0.00 sec) mysql> show warnings; Empty set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> lock table t in share mode; Query OK, 0 rows affected (0.00 sec) mysql> show warnings; Empty set (0.00 sec) Script output when it malfunctions (current BK sources): mysql> drop table if exists t; Query OK, 0 rows affected (0.01 sec) mysql> create table t (i int) engine innodb; Query OK, 0 rows affected (0.22 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> lock table t in share mode; ERROR 1615 (HY000): Cannot convert to non-transactional lock in an active transaction on 't' mysql> show warnings; +-------+------+--------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------+ | Error | 1615 | Cannot convert to non-transactional lock in an active transaction on 't' | +-------+------+--------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> lock table t in share mode; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1613 | Converted to non-transactional lock on 't' | +---------+------+--------------------------------------------+ 1 row in set (0.00 sec)