[YSQL] Serializable isolation SELECT, DELETE, SELECT inconsistency for PK

[jaki]

There is a somewhat specific circumstance that causes a serializable isolation transaction to break serializability.

Session A	Session B
CREATE TABLE t (i int PRIMARY KEY)
INSERT INTO t VALUES (1), (2), (3)
BEGIN ISOLATION LEVEL SERIALIZABLE
<stmt>
	DELETE FROM t WHERE i = 2
<stmt>
COMMIT

<stmt>	PostgreSQL 11	Yugabyte
SELECT * FROM t	3 rows; 3 rows	3 rows; 2 rows
SELECT i FROM t WHERE i = 1	1 row; 1 row	1 row; 1 row
SELECT i FROM t WHERE i = 2	1 row; 1 row	1 row; fail
SELECT i FROM t WHERE i > 1	2 rows; 2 rows	2 rows; 1 row
SELECT i FROM t	3 rows; 3 rows	3 rows; 2 rows

The failure in the one case above is of the following form:

ERROR:  Operation failed. Try again.: Transaction aborted: 3740fc3e-704a-42f0-9dd8-79606e282a2f

The rest of the cases (except the second) are phantom reads, a violation to serializability.

Changing the table schema to be (i int) makes things behave as in issue #2698. Changing the table schema to be (i int PRIMARY KEY, j int) makes no difference.

Related: #2823.

[jaki]

Closed by commit 385d2c5. The behavior is now as follows:

<stmt>	PostgreSQL 11	Yugabyte
SELECT * FROM t	3 rows; 3 rows	3 rows; fail
SELECT i FROM t WHERE i = 1	1 row; 1 row	1 row; 1 row
SELECT i FROM t WHERE i = 2	1 row; 1 row	1 row; fail
SELECT i FROM t WHERE i > 1	2 rows; 2 rows	2 rows; fail
SELECT i FROM t	3 rows; 3 rows	3 rows; fail

This is expected behavior for Yugabyte serializable isolation transactions, even though it differs from vanilla PostgreSQL.