[YSQL] Snapshot isolation [FOR] SHARE row locks conflict

[jaki]

The row locks SHARE and KEY SHARE should not conflict. However, they often do in snapshot isolation. They may also conflict in serializable isolation, but I have not tested them thoroughly.

Session A	Session B
CREATE TABLE t (<schema>)
INSERT INTO t VALUES (1), (2), (3)
BEGIN ISOLATION LEVEL REPEATABLE READ
SELECT '(setting read point)'
	BEGIN ISOLATION LEVEL REPEATABLE READ
	SELECT '(setting read point)'
<stmt1>
	<stmt2>
COMMIT
	COMMIT

<schema>	<stmt1>	<stmt2>	PostgreSQL 11	Yugabyte
i int PRIMARY KEY	SELECT * FROM t FOR <lock>	SELECT * FROM t FOR <lock>	success	first COMMIT fails
i int PRIMARY KEY	SELECT * FROM t WHERE i = 1 FOR <lock>	SELECT * FROM t WHERE i = 2 FOR <lock>	success	success
i int PRIMARY KEY	SELECT * FROM t WHERE i = 2 FOR <lock>	SELECT * FROM t WHERE i = 2 FOR <lock>	success	<stmt2> fails OR first COMMIT fails
i int	SELECT * FROM t FOR <lock>	SELECT * FROM t FOR <lock>	success	first COMMIT fails
i int	SELECT * FROM t WHERE i = 1 FOR <lock>	SELECT * FROM t WHERE i = 2 FOR <lock>	success	<stmt2> fails OR first COMMIT fails
i int	SELECT * FROM t WHERE i = 2 FOR <lock>	SELECT * FROM t WHERE i = 2 FOR <lock>	success	<stmt2> fails OR first COMMIT fails

Behavior should be the same between the locks SHARE and KEY SHARE.

Failure on <stmt2> is of the following form:

ERROR:  Operation failed. Try again.: b9c713c2-32fd-4f2d-97a6-14ef5f3c3ed8 Conflicts with higher priority transaction: efbc2bd1-14be-470b-8436-c2e1511df4f6

Failure on the first COMMIT is of the following form:

ERROR:  Error during commit: Operation expired: Transaction expired: 25P02

[kmuthukk]

This is another test case for the related issue (based on an issue reported by Konstantin in slack).

https://gist.github.com/kmuthukk/0778609b63b2468a4446c2fafe9bfe07

Use of references (FK) in snapshot isolation (REPEATABLE READ) causes above test to run into two kinds of errors:

Error during commit: Operation expired: Transaction expired: 25P02

and

Unexpected exception: Operation failed. Try again.: c1f51af9-5811-4086-816f-966d2a7b4a6c Conflicts with committed transaction: eedae21d-719a-403e-a08c-8443cfd43238
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."table1" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

Current workaround is to change:

conn.set_session(autocommit=True, isolation_level="REPEATABLE READ")
to:
conn.set_session(autocommit=True, isolation_level="SERIALIZABLE")
But once this fix lands, this should be not be required.

[jaki]

For people waiting, this is in progress, and I am trying to land it as soon as I can. I hope to do so within 24 hours. There are some broken regression tests that I need to investigate.