If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > InnoDB locks whole table when doing an update

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
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?
__________________
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

Last edited by shammat; 12-05-12 at 02:03.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On