Pessimistic Transaction: SELECT returns old snapshot after UPDATE to the same value

[winddd]

Bug Report

1. Minimal reproduce step (Required)

1. Prepare a TiDB cluster (tested on v8.5.4) with the default Pessimistic Transaction mode and Repeatable Read isolation level.
2. Create a test table and insert initial data:

   CREATE TABLE t (id INT, val INT);
   INSERT INTO t VALUES (1, 1);
   

3. Open two terminal sessions (Session A and Session B).
4. Session A:

   BEGIN;
   SELECT * FROM t;   -- Output: id:1, val:1
   

5. Session B:

   UPDATE t SET val = 10 WHERE id = 1;
   COMMIT;
   

6. Session A:

   UPDATE t SET val = 10 WHERE id = 1; -- Result: Query OK, 1 row affected (or 0 rows changed), Rows matched: 1
   

7. Session A:

   SELECT * FROM t;
   

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

Since the update by Step 6 succeeded, Session A should see the updated value val = 10 in Step 7.

3. What did you see instead (Required)

I saw val = 1 in Step 7.

If I changed the val = 20 in Step 6, then Step 7 would be able to see his own write val=20.

4. What is your TiDB version? (Required)

Release Version: v8.5.4
Edition: Community
Git Commit Hash: e4e814fdc0afe9c3a6e5e96f129d83df802ab820
Git Branch: HEAD
UTC Build Time: 2025-11-26 15:53:39
GoVersion: go1.23.12
Race Enabled: false
Check Table Before Drop: false
Store: tikv

This should be similar to the MariaDB bug: https://jira.mariadb.org/browse/MDEV-26642, which was fixed in their recent versions.

[ti-chi-bot[bot]]

Welcome @winddd! It looks like this is your first issue to pingcap/tidb 🎉

[jebter]

@cfzjywxk PTAL

[cfzjywxk]

@winddd Thanks for the feedback.

The likely cause of this issue is that in step 7, the executor—even when it is a PointGetExec—performs a snapshot read that does not retrieve the value of the previously locked key from the pessimistic lock cache.

For point-get snapshot reads, a straightforward fix would be to attempt to check the pessimistic lock cache for any updated values. For coprocessor snapshot reads, it becomes more complex, potentially requiring consideration of pessimistic lock cache merge operations within the UnionScan executor.