MySQL Bugs: #58689: MyISAM Selects locks inserts inside procedure only

Bug #58689	MyISAM Selects locks inserts inside procedure only
Submitted:	3 Dec 2010 6:02	Modified:	31 Jul 2012 1:44
Reporter:	sam munkes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 5.6.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	insert, locks, myisam, PROCEDURE, SELECT

Description:
Hi all,

We have a large MyISAM table to which rows get inserted to the bottom of the table only.

While doing some benchmarks, i realized that selects do not (always) lock other inserts to that same table. However, when the inserts are coming from a stored procedure/function they will by locked by the select.

Why is that?

MySql Version: 5.0.51 running on Window Server 2K3

Here is the profile output:

Insert Direct:

    (initialization)     0.0000432
    checking permissions 0.0000074
    Opening tables       0.0000077
    System lock          0.0000032
    Table lock           0.0000025
    init                 0.000021
    update               0.0002365
    end                  0.0000382
    query end            0.000002
    freeing items        0.0000057
    closing tables       0.0000022
    logging slow query   0.0000005

Insert via Procedure:

    (initialization) 0.0000285
    Opening tables   0.0004325
    System lock      0.0000022
    Table lock       0.0002957
    checking permissions 0.0000047
    Opening tables   0.000004
    System lock      0.0000017
    Table lock       3.2365122
    init             0.0000422
    update           0.000251
    end              0.0000025
    query end        0.000003
    closing tables   0.00004
    query end        0.0000074
    freeing items    0.0000074
    logging slow query 0.000001
    cleaning up      0.5790915

Why does the procedure open and "Table lock" twice?
Thank you.

How to repeat:
    CREATE TABLE Foo (
       ID INT NOT NULL AUTO_INCREMENT,
       Bar VARCHAR(200),
       PRIMARY KEY(ID)) ENGINE=MyISAM;

   /*INSERT into Foo 10M rows*/
    SELECT RAND()
    FROM information_schema.COLUMNS g, 
    information_schema.COLUMNS g2,
    information_schema.COLUMNS g3;

    DELIMITER $$
    
    DROP PROCEDURE IF EXISTS InsertProc$$
    
    CREATE PROCEDURE InsertProc(IN vBar VARCHAR(255))
    BEGIN
    	INSERT Foo(Bar) VALUES (vBar);
    END$$
    
    DELIMITER ;

Run the following query:

    SELECT Count(*) FROM Foo WHERE INSTR(Bar, 'abcdefg') > 0;

While that Select is running, open a new connection and run the following insert query:

    INSERT Foo(Bar) VALUES ('xyz1234');
That Insert will run and return right away, However if i run the following query:

    CALL InsertProc('xyz1234');

Now the query locks and waits for the select to complete.

Thank you for the report.

Verified as described.

Test case for MTR:

 CREATE TABLE Foo (
       ID INT NOT NULL AUTO_INCREMENT,
       Bar VARCHAR(200),
       PRIMARY KEY(ID)) ENGINE=MyISAM;

INSERT into Foo (Bar)
    SELECT RAND()
    FROM information_schema.COLUMNS g, 
    information_schema.COLUMNS g3;
	
INSERT into Foo (Bar)
    SELECT RAND()
    FROM information_schema.COLUMNS g, 
    information_schema.COLUMNS g3;
	
INSERT into Foo (Bar) select Bar from Foo;
INSERT into Foo (Bar) select Bar from Foo;
INSERT into Foo (Bar) select Bar from Foo;
INSERT into Foo (Bar) select Bar from Foo;

DELIMITER $$;
    
    DROP PROCEDURE IF EXISTS InsertProc$$
    
    CREATE PROCEDURE InsertProc(IN vBar VARCHAR(255))
    BEGIN
    	INSERT Foo(Bar) VALUES (vBar);
    END$$
    
DELIMITER ;$$

--send SELECT Count(*) FROM Foo WHERE INSTR(Bar, 'abcdefg') > 0

sleep 1;
connect (addconroot, localhost, root,,);
connection addconroot;
select now();
INSERT Foo(Bar) VALUES ('xyz1234');
select now();
CALL InsertProc('xyz1234');
select now();

Noted in 5.7.0 changelog.

Concurrent inserts were blocked by selects if the inserts were
generated from within a stored procedure.