mysql does't release the lock even if the row does not match where clause

haoyu wang 0
Viewed 20

In mysql document https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html. It says

SELECT ... FOR UPDATE and SELECT ... FOR SHARE statements that use a unique index acquire locks for scanned rows, and release the locks for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause).

But in practice, it does not work as I expected. Here is my test.

Create a new Table Person.

CREATE TABLE `Persion` (
  `id` bigint NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Persion_name_IDX` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Insert following data.

id name age
10 bob 19
11 cat 20

This is my sql statement.

//transaction 1
start transaction;
SELECT  * from test.Persion p  where id IN (10,11) and age=19  for UPDATE;

//transaction 2
UPDATE test.Persion set name='tx6' where id=11;

In transaction 1, when I execute the select...for update sql, it should not lock the id=11 record, because it does't match the age=19 condition. So my second transaction should not be blocked. But in fact, it is!

So I want to know what happend. Did I understand the document correct?

transactions mysql
1 Answers

I read the documentation the same way you do, and I think it's unclear or incorrect. I repeated your test and I got the same result you did — the row with id 11 remained locked by the first session.

Then I tested your case after setting the following in the first session:

SET transaction_isolation='READ-COMMITTED';

This allows the second session to update the row.

This makes me think locking follows the same rule for updates in READ-COMMITTED isolation level, described on this page: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

READ COMMITTED

Using READ COMMITTED has additional effects:

  • For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.
Bill Karwin0
Related