MySQL Bugs: #31352: Write locking in stored functions with INSERT and SELECT statemenst

Bug #31352	Write locking in stored functions with INSERT and SELECT statemenst
Submitted:	2 Oct 2007 18:55	Modified:	20 Oct 2007 16:17
Reporter:	Adam Miel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any (Linux, Windows)
Assigned to:		CPU Architecture:	Any
Tags:	myisam, Stored Functions

Description:
In my heavy used mysql server I use many stored functions which are unfortunately locking our database. These functions use only SELECT and INSERT statements, and they are locking. I have turned on MYISAM concurrent_insert variable. In INNODB engine is OK. I prefer using MYISAM because when I tried to use INNODB there were even bigger problems with server load.

How to repeat:
Here is an example of such locking:

DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (id INT PRIMARY KEY, a1 INT) ENGINE=MYISAM;
INSERT INTO test1 VALUES(1, 1);
INSERT INTO test1 VALUES(2, 1);

DROP FUNCTION IF EXISTS test1;
CREATE FUNCTION test1() RETURNS INT READS SQL DATA
BEGIN
    DECLARE v_tmp INT;
    SELECT a1 INTO v_tmp FROM test1 WHERE id = 1 LIMIT 1;
    SELECT SLEEP(10) INTO v_tmp;

    INSERT INTO test1 VALUES(3, 1);
    RETURN 1;
END ;

DROP FUNCTION IF EXISTS test2;
CREATE FUNCTION test2() RETURNS INT
BEGIN
    DECLARE v_tmp INT;
    SELECT a1 INTO v_tmp FROM test1  WHERE id = 2 LIMIT 1;
    RETURN 1;
END;

-- first: (it will hang for 10 seconds)
SELECT test1();

-- then in another client execute (it will wait for the test1 to end, and it should not)
SELECT test2();

It does not lock when there is no "INSERT INTO" in test1 function

the same code rewritten to stored procedures does not lock (in fact in SP it never locks. Even when there are update statemets in test1.)

Suggested fix:
Stored functions with INSERT and SELECT statements should not lock when concurrent_insert is on

Thank you for the report.

Verified as described.

This is a limitation of pre-locking that should be clarified in the documentation.
When a stored function or trigger is used, pre-locking algorithm takes the most conservative lock on the table that may be required to execute the trigger/function.

In the example above the function needs TL_READ and TL_CONCURRENT_INSRT locks on the table, so TL_CONCURRENT_INSERT lock is acquired. Note, it is acquired at start of statement, no lock upgrades are performed when the statement is running. 
Two TL_CONCURRENT_INSERT locks are incompatible with each other, and thus the second SELECT blocks. 
InnoDB does not expose this behavior since it downgrades all table level locks to TL_WRITE_ALLOW_WRITE.

Understand, sorry I didn't know that concurrent inserts are locking
Tahank you