Weird SELECT when table has the primary key

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* init */ CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT);
/* init */ INSERT INTO t (c1, c2) VALUES (1, 1);

/* t1 */ BEGIN;
/* t1 */ UPDATE t SET c1=2, c2=2;
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t;  -- t2 blocked
/* t1 */ COMMIT;  -- t2 unblocked
/* t2 */ SELECT * FROM t;
/* t2 */ COMMIT;

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

The result of SELECT statement in the second transaction should get an empty set.

3. What did you see instead (Required)

The result of SELECT statement in the second transaction is (1, 1).

It is weird that SELECT statement is not empty only if table has the primary key.

4. What is your TiDB version? (Required)

+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v5.4.0 |
+--------------------+

[morgo]

Verified as described (with tikv + tidb):

tidb> /* t2 */ DELETE FROM t;  -- t2 blocked
Query OK, 1 row affected (6.47 sec)

tidb> select * from t;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

In snapshot isolation (advertised as repeatable-read), changes introduced after the transaction started are not supposed to be visible. Repeating the same test in MySQL, the select returns zero rows.

[zyguan]

Duplicated with #23966
It's suggested to use select for update within a transaction context to avoid such an issue.

[sayJason]

Thank you for your reply.
However, it is different from the case below.

/* init */ CREATE TABLE t (c1 INT, c2 INT);
/* init */ INSERT INTO t (c1, c2) VALUES (1, 1);

/* t1 */ BEGIN;
/* t1 */ UPDATE t SET c1=2, c2=2;
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t;  -- t2 blocked
/* t1 */ COMMIT;  -- t2 unblocked
/* t2 */ SELECT * FROM t; -- Empty Set
/* t2 */ COMMIT;

If the column c1 is not the primary key, the SELECT statement in t2 gets an empty set.
Could you please explain why changes introduced by t2 are not visible by t2 when the table with a primary key ?

[cfzjywxk]

Thank you for your reply. However, it is different from the case below.

/* init */ CREATE TABLE t (c1 INT, c2 INT);
/* init */ INSERT INTO t (c1, c2) VALUES (1, 1);

/* t1 */ BEGIN;
/* t1 */ UPDATE t SET c1=2, c2=2;
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t;  -- t2 blocked
/* t1 */ COMMIT;  -- t2 unblocked
/* t2 */ SELECT * FROM t; -- Empty Set
/* t2 */ COMMIT;

If the column c1 is not the primary key, the SELECT statement in t2 gets an empty set. Could you please explain why changes introduced by t2 are not visible by t2 when the table with a primary key ?

@sayJason

If column c1 is not the primary key, there would be a hidden primary key column named _tidb_rowid for it. The data records inside t1 is:

mysql> select *, _tidb_rowid from t;
+------+------+-------------+
| c1   | c2   | _tidb_rowid |
+------+------+-------------+
|    1 |    1 |           1 |
+------+------+-------------+
1 row in set (0.00 sec)

So this time the delete statement would deletes this row whose row id is 1, this operation is saved into the memory buffer of this transaction. The next time when snapshot read is used, the result row returnd from the snapshot also has row id 1, they are merged so empty set is returned to the client finally. This is different if column c1 is the primary key, the update statement actually deletes the first row with row id 1 and insert another new row with row id 2.

More information could be referenced in this issue.

[zyguan]

@sayJason The reason is as @cfzjywxk described above. The issue occurs if you use clustered index, you may also try the following SQL.

/* init */ CREATE TABLE t (c1 INT PRIMARY KEY NONCLUSTERED, c2 INT);
/* init */ INSERT INTO t (c1, c2) VALUES (1, 1);

/* t1 */ BEGIN;
/* t1 */ UPDATE t SET c1=2, c2=2;
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t;  -- t2 blocked
/* t1 */ COMMIT;  -- t2 unblocked
/* t2 */ SELECT * FROM t; -- empty set
/* t2 */ COMMIT;

[sayJason]

If column c1 is not the primary key, there would be a hidden primary key column named _tidb_rowid for it. The data records inside t1 is:

mysql> select *, _tidb_rowid from t;
+------+------+-------------+
| c1   | c2   | _tidb_rowid |
+------+------+-------------+
|    1 |    1 |           1 |
+------+------+-------------+
1 row in set (0.00 sec)

So this time the delete statement would deletes this row whose row id is 1, this operation is saved into the memory buffer of this transaction. The next time when snapshot read is used, the result row returnd from the snapshot also has row id 1, they are merged so empty set is returned to the client finally. This is different if column c1 is the primary key, the update statement actually deletes the first row with row id 1 and insert another new row with row id 2.

More information could be referenced in this issue.

Thank you for your explanation.
But I am a little confused. Do you mean that with clustered index, delete operation is not saved into the memory buffer, so select statement returns the row (1, 1) from snapshot read ?

[sayJason]

@sayJason The reason is as @cfzjywxk described above. The issue occurs if you use clustered index, you may also try the following SQL.

/* init */ CREATE TABLE t (c1 INT PRIMARY KEY NONCLUSTERED, c2 INT);
/* init */ INSERT INTO t (c1, c2) VALUES (1, 1);

/* t1 */ BEGIN;
/* t1 */ UPDATE t SET c1=2, c2=2;
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t;  -- t2 blocked
/* t1 */ COMMIT;  -- t2 unblocked
/* t2 */ SELECT * FROM t; -- empty set
/* t2 */ COMMIT;

I also tested several data types and set them as primary key. These cases return empty set after delete statement. Only when the primary key is an INTEGER, the clustered index is enabled by default, select statement return the row that should be deleted.

[zyguan]

delete operation is not saved into the memory buffer, so select statement returns the row (1, 1) from snapshot read ?

In your case, tidb marks (2, 2) as deleted, however, the select statement uses snapshot read and sees (1, 1), they cannot be merged by union scan currently.

[sayJason]

delete operation is not saved into the memory buffer, so select statement returns the row (1, 1) from snapshot read ?

In your case, tidb marks (2, 2) as deleted, however, the select statement uses snapshot read and sees (1, 1), they cannot be merged by union scan currently.

I got it. Thank you for your explanation.