MySQL Bugs: #37578: Execute Stored Functions Without Prelocks

Bug #37578	Execute Stored Functions Without Prelocks
Submitted:	23 Jun 2008 7:00	Modified:	25 Jun 2008 13:28
Reporter:	Akiko Yonemura	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1, 5.0, 6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	replication, stored function

Description:
Currently, stored functions require prelocks for all tables which they manipulate within the codes. This causes a lock contention when the same table is being modified at the same time, which will slow down stored function execution. In order to speed it up, stored functions should be executed without prelocks.

Now, MySQL logs function names instead of statements which are really executed. This makes MySQL acquire table locks for the tables used by the stored function in order to serialize the binary log. Thus, prelocks are necessary though.

However, MyQL 5.1 implements RBR which records all changes in the order which they are really modified. When RBR is used, prelocks are likely to be unnecessary any more.

How to repeat:
N/A

Suggested fix:
Allow to log all statements issued within the stored function to the binary log instead of function names when RBR is used.

Many thanks for writing a feature request.

Do you mean locking on slave or on master?

Hello,

Thank you for your reply.

I mean a pre-locking by a stored function on both master and slave.
However the real problem here is that the prelock of stored function causes the low performance. Thus I may say that avoiding prelock on master is more important than on slave because we usually concern about the performance more during any processes on master server.

The prelock by the stored function seems to be related to Row-based replication, then I filed it as a replication.

Akiko

Bug #37731 is set as duplicate of this bug here.

Even if RBR is used or log-bin is disabled, Stored function and trigger require prelock all tables which they manipulate within the codes.
 
With RBR or without log-bin, Stored function and Trigger should be executed without prelock.

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

Thanks,
Akiko

I'm seeing deletes of large amounts of data from within a stored procedure taking 100 times (10000%) longer than the equivalent code run directly from the mysql prompt.  Could this be related?

See also bug #62540. I doubt these are duplicates though.