CODEWITHSUNDEEP
mysqltransactionslockingmariadb
JCZ
JCZ

Reputation: 430

FOR UPDATE with IN clause causes LOCK WAIT TIMEOUT

I am facing the LOCK WAIT TIMEOUT issues when using the SELECT ... FOR UPDATE statement.

I have prepared the scenario which I do not understand. Could you tell me why the first three blocks are executed immediately, but the last one waits?

Thanks.

-- I'm using MariaDb

-- T1 is the mysql terminal window 1
-- T2 is the mysql terminal window 2

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL
) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_bin';

INSERT INTO test SET name='foo';

T2: START TRANSACTION;
T1: START TRANSACTION;
T1: SELECT * FROM test WHERE id IN (1) FOR UPDATE;
T2: INSERT INTO test SET name='foo'; -- executed immediately

T2: START TRANSACTION;
T1: START TRANSACTION;
T1: SELECT * FROM test WHERE id IN (1,2) FOR UPDATE;
T2: INSERT INTO test SET name='foo'; -- executed immediately

T2: START TRANSACTION;
T1: START TRANSACTION;
T1: SELECT * FROM test WHERE id IN (1,2,3) FOR UPDATE;
T2: INSERT INTO test SET name='foo'; -- executed immediately

T2: START TRANSACTION;
T1: START TRANSACTION;
T1: SELECT * FROM test WHERE id IN (1,2,3,4) FOR UPDATE;
T2: INSERT INTO test SET name='foo'; -- waits for T1 to commit

T2: commit;
T1: commit;

Upvotes: 2

Views: 3102

Answers (2)

RandomSeed
RandomSeed

Reputation: 29769

InnoDB row-level locks are index-locks in reality. A less documented feature* is: if the locking SELECT does not (or cannot) use a (user-defined) index, then only the default clustered index can be used, and the entire table becomes locked.

It is very likely that the optimizer decided that the index is not useful to check the "long" list of IN() parameters (probably because most of the table needs to be scanned). This whould lead to the unexpected side-effect you detected.

This hypothesis can be confirmed by checking the execution plan.

Upvotes: 3

koriander
koriander

Reputation: 3258

I think you are assuming that "select for update" only locks the rows for UPDATE. However, the locking also applies to INSERT, because an insert can insert a record that falls under the original "select". The documentation states that it "locks the rows and any associated index entries".

http://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html

Upvotes: -1

Related Questions