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
Triage: Triaged: D3 (Medium)

[23 Jun 2008 7:00] Akiko Yonemura
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.
[23 Jun 2008 9:11] Susanne Ebrecht
Many thanks for writing a feature request.

Do you mean locking on slave or on master?
[25 Jun 2008 13:23] Akiko Yonemura
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
[30 Jun 2008 7:37] Susanne Ebrecht
Bug #37731 is set as duplicate of this bug here.
[30 Jun 2008 13:00] Akiko Yonemura
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
[15 Aug 2008 20:36] Kevin Regan
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?
[28 Sep 2011 19:49] Sveta Smirnova
See also bug #62540. I doubt these are duplicates though.