Description:
Even if RBR is used or log-bin is disabled, Stored function and trigger require prelock all tables which they manipulate within the codes.
I understand that no locking in Stored Procedures and locking in Stored Functions and triggers is a design for SBR, because SPs are replicated statment by statement, while SFs and triggers are replicated by call. However this behaviour is for SBR only not for RBR or non-logbin.
How to repeat:
From the result of the following test cases for SP and SF, it shows that SF gets the lock table for all tables on 3 cases, but SP doesn't.
(1) Enable binary logging with SBR
(2) Enable up binary logging with RBR
(3) Disable binary logging
Details:
mysql> SHOW CREATE TABLE t1;
+-------+----------------------------------------------
| Table | Create Table
+-------+----------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` char(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t2;
+-------+-----------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------+
| t2 | CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` char(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE FUNCTION sf;
+----------+----------+---------------------------------+
| Function | sql_mode | Create Function |
+----------+----------+-----------------------------------------------------------------------------------------+
| sf | | CREATE DEFINER=`root`@`localhost` FUNCTION `sf`(a int) RETURNS int(11)
begin insert into t1 values (a,'xxx'); insert into t2 values (a,'xxx'); return a; end |
+----------+----------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE PROCEDURE sp;
+-----------+----------+-----------------------------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure |
+-----------+----------+-----------------------------------------------------------------------------------------+
| sp | | CREATE DEFINER=`root`@`localhost` PROCEDURE `sp`()
begin
insert into t1 values(10,'aa');
insert into t2 values(11,'aa');
end |
+-----------+----------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
***(1) Enable binary logging with SBR******
*************************************************
mysql> SHOW VARIABLES LIKE '%bin%';
+-------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------+------------+
| binlog_cache_size | 32768 |
| binlog_format | STATEMENT |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
(snip)
***(1)-1. Stored Procedure*****************
o THREAD 1
mysql> LOCK TABLES t2 READ;
Query OK, 0 rows affected (0.00 sec)
o TREAD 2
mysql> call sp();
(WAITING until UNLOCK TABLES)
o THREAD 3
mysql> SHOW FULL PROCESSLIST;
+----+------+-----------+------+---------+------+--------+--------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+--------+--------------------------------+
| 1 | root | localhost | test | Query | 21 | Locked | insert into t2 values(11,'aa') |
| 2 | root | localhost | test | Sleep | 1626 | | NULL |
| 3 | root | localhost | test | Sleep | 60 | | NULL |
| 4 | root | localhost | test | Query | 0 | NULL | SHOW FULL PROCESSLIST |
+----+------+-----------+------+---------+------+--------+--------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t1;
+------+------+
| c1 | c2 |
+------+------+
| 10 | aa |
+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;
(WAITING until UNLOCK TABLES)
o SUMMARY
SP can insert into t1, and wait only for inserting into t2.
***(1)-2. Stored Function*****************
mysql> DELETE FROM t1;DELETE FROM t2;
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
o THREAD 1
mysql> LOCK TABLES t2 READ;
Query OK, 0 rows affected (0.00 sec)
o THREAD 2
mysql> SELECT sf(1);
(WAITING until UNLOCK TABLES)
o THREAD 3
mysql> SHOW FULL PROCESSLIST;
+----+------+-----------+------+---------+------+--------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+--------+-----------------------+
| 1 | root | localhost | test | Query | 4 | Locked | SELECT sf(1) |
| 2 | root | localhost | test | Sleep | 1903 | | NULL |
| 3 | root | localhost | test | Sleep | 35 | | NULL |
| 4 | root | localhost | test | Query | 0 | NULL | SHOW FULL PROCESSLIST |
+----+------+-----------+------+---------+------+--------+-----------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t1;
(WAITING until UNLOCK TABLES)
o SUMMARY
SF trys to lock t1 and t2 at the same time,
and waits for inserting t1 even if another thread locks onlyt2.
***(2) Enable up binary logging with RBR******
*************************************************
mysql> SHOW VARIABLES LIKE '%bin%';
+-------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------+------------+
| binlog_cache_size | 32768 |
| binlog_format | ROW |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
(snip)
***(2)-1. Stored Procedure ****************
Same as (1)-1
***(2)-2. Stored Function *****************
Same as (1)-2
***(3) Disable binary logging *************
*************************************************
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
***(3)-1. Stored Procedure ****************
Same test and same result as (1)-1
***(3)-2. Stored Function *****************
Same test and same result as (1)-2
Suggested fix:
With RBR or without log-bin, Stored function and Trigger should be executed without prelock.
