# InnoDB insert lock

**URL:** <https://forums.percona.com/t/innodb-insert-lock/442>\
**Category:** Other MySQL® Questions\
**Created:** [September 8, 2007, 6:46am UTC](https://forums.percona.com/t/innodb-insert-lock/442 "2007-09-08T06:46:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![latrommi](https://avatars.discourse-cdn.com/v4/letter/l/f14d63/32.png) [@latrommi](https://forums.percona.com/u/latrommi)\
**Post date:** [September 8, 2007, 6:46am UTC](https://forums.percona.com/t/innodb-insert-lock/442/1 "2007-09-08T06:46:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![SabreCEO](https://avatars.discourse-cdn.com/v4/letter/s/b19c9b/32.png) [@SabreCEO](https://forums.percona.com/u/SabreCEO)\
**Post date:** [September 19, 2007, 6:50pm UTC](https://forums.percona.com/t/innodb-insert-lock/442/2 "2007-09-19T18:50:13Z")

</div>

-------- 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
