planner, txn: select ... for update using Plan Cache can not lock data correctly in some cases

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> select @@autocommit; -- enable autocommit
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

create table t (pk int, a int, primary key(pk));  -- create a table with PK
prepare st from 'select * from t where pk=? for update';   -- prepare a PointPlan statement
set @pk=1;                                                                             
execute st using @pk;    -- execute this statement to generate a PointPlan cached in Plan Cache

-- plan of this exec-statement, Lock operations for "for update" are optimized by auto-commit
+-------------+---------+---------+------+---------------+------------------------------------------------------------+---------------+--------+------+
| id          | estRows | actRows | task | access object | execution info                                             | operator info | memory | disk |
+-------------+---------+---------+------+---------------+------------------------------------------------------------+---------------+--------+------+
| Point_Get_1 | 1.00    | 0       | root | table:t       | time:94.1µs, loops:1, Get:{num_rpc:1, total_time:42.5µs}   | handle:2      | N/A    | N/A  |
+-------------+---------+---------+------+---------------+------------------------------------------------------------+---------------+--------+------+



begin;
set @pk=1;
execute st using @pk;   -- the optimizer decided to reuse the prior PointPlan, which is incorrect.

mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      1 |
+------------------------+

Reusing this PointPlan without Lock in the second exec-statement can cause wrong results.

The correct plan for the second exec-statement should have Lock opearations:

+-------------+---------+---------+------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------+------+
| id          | estRows | actRows | task | access object | execution info                                                                                                                                               | operator info  | memory | disk |
+-------------+---------+---------+------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------+------+
| Point_Get_1 | 1.00    | 0       | root | table:t       | time:1.74ms, loops:1, lock_keys: {time:1.69ms, region:1, keys:1, slowest_rpc: {total: 0.000s, region_id: 93, store: store1, }, lock_rpc:165µs, rpc_count:1}  | handle:1, lock | N/A    | N/A  |
+-------------+---------+---------+------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------+------+

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

Shouldn't reuse the first PointPlan for the second exec-statement and the second exec-statement's plan should have Lock operations:

+-------------+---------+---------+------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------+------+
| id          | estRows | actRows | task | access object | execution info                                                                                                                                               | operator info  | memory | disk |
+-------------+---------+---------+------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------+------+
| Point_Get_1 | 1.00    | 0       | root | table:t       | time:1.74ms, loops:1, lock_keys: {time:1.69ms, region:1, keys:1, slowest_rpc: {total: 0.000s, region_id: 93, store: store1, }, lock_rpc:165µs, rpc_count:1}  | handle:1, lock | N/A    | N/A  |
+-------------+---------+---------+------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------+------+

3. What did you see instead (Required)

The second exec-statement's plan has no Lock:

+-------------+---------+---------+------+---------------+-------------------------------------------------------------+---------------+--------+------+
| id          | estRows | actRows | task | access object | execution info                                              | operator info | memory | disk |
+-------------+---------+---------+------+---------------+-------------------------------------------------------------+---------------+--------+------+
| Point_Get_1 | 1.00    | 0       | root | table:t       | time:123.7µs, loops:1, Get:{num_rpc:1, total_time:63.3µs}   | handle:1      | N/A    | N/A  |
+-------------+---------+---------+------+---------------+-------------------------------------------------------------+---------------+--------+------+

4. What is your TiDB version? (Required)

Master

[seiya-annie]

/report customer