Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    2,811

    InnoDB locks whole table when doing an update

    My understanding was that InnoDB is doing row-level locking for updates. However this does not seem to always be the case when updating a range of rows.

    Assume the following setup:

    Code:
    create table foo
    (
       id integer not null primary key, 
       c1 integer not null
    ) engine=innodb;
    
    insert into foo
    (id, c1)
    values
    ( 1, 10),
    ( 2, 11),
    ( 3, 12),
    ( 4, 13),
    ( 5, 14),
    ( 6, 15),
    ( 7, 16),
    ( 8, 17),
    ( 9, 18),
    (10, 19);
    
    commit;
    Now I start two different sessions by invoking the mysql commandline client.

    In the first window I do the following:
    Code:
    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>
    mysql> update foo
        -> set c1 = c1 + 1
        -> where id between 1 and 4;
    Query OK, 4 rows affected (0.00 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    
    mysql>
    Then in the second window, I run a similar statement, only updating the other rows of the table:
    Code:
    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>
    mysql> update foo
        ->   set c1 = c1 + 2
        -> where id between 5 and 10;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    mysql>
    mysql>
    The second statement will not be successful until I commit the first transaction. Does this mean that InnoDB is using a table level lock when I update a range of rows?

    Note that the column that is being updated is not indexed, so this can't be a problem with gap locking or something similar (that's one reason I changed the isolation level to "read committed" as apparently the default leve "repeatable read" is more "vulnerable" against index locking)

    The two updates run fine if I only update a single row.

    What am I missing here?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    By changing the transaction isolation level to read committed this is introducing index locking. Here is what the manual says:

    For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because “phantom rows” must be blocked for MySQL replication and recovery to work.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

  3. #3
    Join Date
    Nov 2003
    Posts
    2,811
    Hmm, my understanding was, that the gap lock is only done when using the default level "REPEATABLE READ" but not with READ COMMITTED

    I got this from this blog posting: http://www.mysqlperformanceblog.com/...dbs-gap-locks/

    Quote Originally Posted by MySQL performance blog
    If you have lot of gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways:

    1- Change the ISOLATION level to READ COMMITTED.
    So is there any way to get this working without one transaction being blocked by the other?
    Last edited by shammat; 12-05-12 at 02:03.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    Yes create a primary key on the table. Then only ranges or individual rows will be locked.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

  5. #5
    Join Date
    Nov 2003
    Posts
    2,811
    Quote Originally Posted by it-iss.com View Post
    Yes create a primary key on the table. Then only ranges or individual rows will be locked.
    There already is a primary key defined (see the CREATE TABLE statement in my initial post)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •