GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

InnoDB insert lock

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB insert lock

    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

    Comment

    Working...
    X