mysql - MyISAM Selects locks inserts inside procedure only -

we have large myisam table rows inserted bottom of table only.

while doing benchmarks, realized selects not (always) lock other inserts same table. however, when inserts coming stored procedure/function locked select.

why that?

to demonstrate behavior:

create table foo (    id int not null auto_increment,    bar varchar(200),    primary key(id)) engine=myisam;  --insert foo 10m rows   delimiter $$  drop procedure if exists insertproc$$  create procedure insertproc(in vbar varchar(255)) begin     insert foo(bar) values (vbar); end$$  delimiter ; 

run following query:

select count(*) foo instr(bar, 'abcdefg') > 0; 

while select running, open new connection , run following insert query:

insert foo(bar) values ('xyz1234'); 

that insert run , return right away, if run following query:

call insertproc('xyz1234'); 

now query locks , waits select complete.

mysql version: 5.0.51 running on window server 2k3

thank you.

-- update here 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      0.5790915 

why procedure open , "table lock" twice?

this issue submitted bug: http://bugs.mysql.com/bug.php?id=58689