InnoDB insert lock

MySQL & MariaDB Other MySQL® Questions
1

Good day!

I’m using MySQL 5.0.41 and InnoDB table.
In the MySQL manuals not all absolutly clear about InnoDB table and row locks. Then I want to try it oneself.

About table and service:

  1. using INSERT DELAYED is bab idea;
  2. DELETE statement never applies for this table.

Using two sessions in the following succession I meet uncertainty about insert lock.

-------- process 1 --------
mysql> create table tbl(id int not null auto_increment, a int, b int, primary key (id),index ind_a(a)) engine=innodb;
mysql> insert tbl(a, b) values(1,1);
mysql> insert tbl(a, b) values(1,2);
mysql> insert tbl(a, b) values(2,3);
mysql> insert tbl(a, b) values(2,4);
mysql> _

All queries success.

-------- process 2 --------
mysql> set autocommit=0;
mysql> begin;
mysql> select * from tbl force index(ind_a) where a=1 for update;
(recordset output)
mysql> _

All queries success.

-------- process 1 --------
mysql> insert tbl(a, b) values(1,5);

At this time insert lock happens.

One simple question: why?
What ways I have around this lock?

THANKS!

P.S.
sorry, my English is in Alpha version.

2

-------- process 2 --------
mysql> set autocommit=0;
mysql> begin;
mysql> select * from tbl force index(ind_a) where a=1 for update;
(recordset output)
mysql> _

You’ve left the table locked that’s why its locked for process 1.

in process 2 you need to finish what you are doing and COMMIT

Then the lock will be released and process 1 can complete.

-Mark

PS INSERT DELAYED does not work with INNOdb tables