MyISAM Selects locks inserts inside procedure only

[sam munkes]

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?

To demonstrate this behavior:

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

--INSERT into Foo 10M rows

DELIMITER $$

DROP PROCEDURE IF EXISTS InsertProc$$

CREATE PROCEDURE InsertProc(IN vBar VARCHAR)
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.

MySql Version: 5.0.51 running on Window Server 2K3

Thank you.