[YSQL] Serializable isolation SELECT, DELETE, SELECT excessive failures

[jaki]

Compared to vanilla PostgreSQL, Yugabyte seems to fail too often whenever a serializable isolation level transaction happens with an intermediate DELETE. Nothing is particularly wrong with the excessive failures. The most notable failure is on the second variant of <stmt> below, where disjoint SELECTs and DELETEs still cause failures.

Session A	Session B
CREATE TABLE t (i int)
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	fail
SELECT i FROM t WHERE i = 1	1 row; 1 row	fail
SELECT i FROM t WHERE i = 2	1 row; 1 row	fail
SELECT i FROM t WHERE i > 1	2 rows; 2 rows	fail
SELECT i FROM t	3 rows; 3 rows	fail

The "fail" above are all of the same kind. They non-deterministically fail on one of two statements:

• The DELETE:

  ERROR:  Operation failed. Try again.: 42e6ddd2-30a7-4131-86a5-c4755b605092 Conflicts with higher priority transaction: de163721-31a8-4feb-92a7-7cbda4bdab53
  

• The second <stmt>:

  ERROR:  Operation failed. Try again.: Transaction aborted: b8bde22d-b5e1-4ece-93f2-2ea312b24dd5
  

Related:

• Issue [YSQL] Support locking options in SELECT statement (SELECT FOR UPDATE etc.) #1199
• Issue Data becomes visible before it should when using FOR KEY SHARE in default isolation mode #2523
• Issue [YSQL] Serializable isolation SELECT, DELETE, SELECT inconsistency for PK #2831

[jaki]

Note that I have edited both this comment and the above comment to clean things up from a previous version of this issue that is no longer relevant.

[lhotari]

We are facing this issue with pretty basic use cases with YSQL and it's becoming a blocker. Do you need help in reproducing the issue?

[jaki]

Hi, @lhotari. Feel free to detail your use cases and issues. If the issues do not fall into any of the cases above, a repro would be particularly nice.

Note that this is not necessarily a bug because Yugabyte serializable isolation isn't the same as vanilla PostgreSQL serializable isolation. In Yugabyte, a serializable isolation transaction ensures that all of its statements could have appeared to happen on commit time. In that case, a lot of the failures become reasonable. However, the failure on the second variant of <stmt> (i.e. SELECT i FROM t WHERE i = 1) should, in theory, be avoidable.

[ndeodhar]

Bringing this to @spolitov 's attention for the issue where unrelated delete causes a conflict failure.

[spolitov]

Fixed by 385d2c5