Point get may acquire unnecessary locks

[zyguan]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* init */ drop table if exists t;
/* init */ create table t (k1 int, k2 int, v int, unique key (k1));
/* init */ insert into t values (1, 1, null), (2, 2, 2);

/* t0 */ begin;
/* t1 */ begin;
/* t0 */ update t set v = 10 where (k1, v) in ((1, null)); -- 0 row affected (point get)
/* t1 */ update t set v = 11 where (k1, v) in ((1, null)); -- blocked
/* t0 */ commit;
/* t1 */ commit;

Note that the update of t1 won't be blocked if we use table scan as following.

/* t0 */ begin;
/* t1 */ begin;
/* t0 */ update t set v = 10 where (k2, v) in ((1, null)); -- 0 row affected (table scan)
/* t1 */ update t set v = 11 where (k2, v) in ((1, null)); -- won't be blocked
/* t0 */ commit;
/* t1 */ commit;

2. What did you expect to see? (Required)

Since nothing has been updated by t0, t1 shouldn't be blocked by it.

3. What did you see instead (Required)

t1 was blocked by t0 when using point get.

4. What is your TiDB version? (Required)

release-4.0 (2862cd7)

[you06]

Tested in MySQL-5.7 and MySQL-8.0, point get and scan will both be locked by a wired X lock.

[zyguan]

@you06 Yes, however, it won't be locked if we enable innodb_locks_unsafe_for_binlog on MySQL 5.7 .

/* init */ select version();
-- init >> +-----------+
-- init    | VERSION() |
-- init    +-----------+
-- init    | 5.7.31    |
-- init    +-----------+
/* init */ drop table if exists t;
-- init >> 0 rows affected
/* init */ create table t (k1 int, k2 int, v int, unique key (k1));
-- init >> 0 rows affected
/* init */ insert into t values (1, 1, null), (2, 2, 2);
-- init >> 2 rows affected
/* t0 */ begin;
-- t0 >> 0 rows affected
/* t1 */ begin;
-- t1 >> 0 rows affected
/* t0 */ update t set v = 10 where (k2, v) in ((1, null)); -- 0 row affected (table scan)
-- t0 >> 0 rows affected
/* t1 */ update t set v = 11 where (k2, v) in ((1, null)); -- won't be blocked
-- t1 >> blocked
/* t0 */ commit;
-- t0 >> 0 rows affected
-- t1 >> resumed
-- t1 >> 0 rows affected
/* t1 */ commit;
-- t1 >> 0 rows affected

[you06]

This issue exists in release-4.0 only. in (null) statement will always return null value, it'll not match any row. The following is execute plan in master and release-4.0.

• master

MySQL [test]> explain update t set v = 10 where (k1, v) in ((1, null));
+-------------------+---------+------+---------------+---------------+
| id                | estRows | task | access object | operator info |
+-------------------+---------+------+---------------+---------------+
| Update_4          | N/A     | root |               | N/A           |
| └─TableDual_6     | 8000.00 | root |               | rows:0        |
+-------------------+---------+------+---------------+---------------+
2 rows in set (0.001 sec)

• release-4.0

MySQL [test]> explain update t set v = 10 where (k1, v) in ((1, null));
+---------------------+---------+------+-----------------------+--------------------+
| id                  | estRows | task | access object         | operator info      |
+---------------------+---------+------+-----------------------+--------------------+
| Update_4            | N/A     | root |                       | N/A                |
| └─Selection_7       | 0.00    | root |                       | eq(test.t.v, NULL) |
|   └─Point_Get_6     | 1.00    | root | table:t, index:k1(k1) |                    |
+---------------------+---------+------+-----------------------+--------------------+
3 rows in set (0.001 sec)

I'll find out where the difference comes.

[you06]

However, #21061 does not solve this problem completely, if there is a condition which is possible matching row, it'll also lock the row unexpectedly. The following case still blocked in master.

/* init */ drop table if exists t;
/* init */ create table t (k1 int, k2 int, v int, unique key (k1));
/* init */ insert into t values (1, 1, null), (2, 2, 2);

/* t0 */ begin;
/* t1 */ begin;
/* t0 */ update t set v = 10 where (k1, v) in ((1, 3)); -- 0 row affected (point get)
/* t1 */ update t set v = 11 where (k1, v) in ((1, 2)); -- blocked
/* t0 */ commit;
/* t1 */ commit;

/* init */ select * from t;