[YSQL] Conform transactions and row locks to vanilla Postgres

[jaki]

Jira Link: DB-1577
EDIT: remove "Row locking does not have any affect" from "Should fix".

There are some differences in the way Yugabyte and vanilla Postgres work with
transactions and row locks. I highlight several of them below.

• Should fix:

  • A deletion is detected in the middle of a transaction when it shouldn't be
    visible (see issue Data becomes visible before it should when using FOR KEY SHARE in default isolation mode #2523):

    • [DELETE, INSERT]
    • [DELETE, SELECT FOR SHARE]
    • [DELETE, SELECT FOR KEY SHARE]

• Need not be fixed:

  • No wait on conflicting deletion:

    • [INSERT, DELETE]
    • [SELECT FOR SHARE, DELETE]
    • [SELECT FOR KEY SHARE]

(I use SELECT 123 statements because of issue #2702.)

DELETE, INSERT

Session A	Session B
CREATE TABLE a (i int PRIMARY KEY);
CREATE TABLE b (i int REFERENCES a(i));
INSERT INTO a VALUES (1);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 123;
	DELETE FROM a WHERE i = 1;
INSERT INTO b VALUES (1);
COMMIT;

For Yugabyte, the last INSERT causes

ERROR:  insert or update on table "b" violates foreign key constraint "b_i_fkey"
DETAIL:  Key (i)=(1) is not present in table "a".

For vanilla Postgres, the last INSERT causes

ERROR:  could not serialize access due to concurrent update
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."a" x WHERE "i" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

DELETE, SELECT FOR SHARE

Session A	Session B
CREATE TABLE d (i int);
INSERT INTO d VALUES (1);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 123;
	DELETE FROM d WHERE i = 1;
SELECT * FROM d FOR SHARE
COMMIT;

For Yugabyte, the last SELECT returns 0 rows.

For vanilla Postgres, the last SELECT causes

ERROR:  could not serialize access due to concurrent update

It doesn't matter whether i is a primary key.

DELETE, SELECT FOR KEY SHARE

Session A	Session B
CREATE TABLE d (i int);
INSERT INTO d VALUES (1);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 123;
	DELETE FROM d WHERE i = 1;
SELECT * FROM d FOR KEY SHARE
COMMIT;

For Yugabyte, the last SELECT returns 0 rows.

For vanilla Postgres, the last SELECT causes

ERROR:  could not serialize access due to concurrent update

It doesn't matter whether i is a primary key.

INSERT, DELETE

Session A	Session B
CREATE TABLE a (i int PRIMARY KEY);
CREATE TABLE b (i int REFERENCES a(i));
INSERT INTO a VALUES (1);
BEGIN ISOLATION LEVEL REPEATABLE READ;
INSERT INTO b VALUES (1);
	DELETE FROM a WHERE i = 1;
COMMIT;

For Yugabyte, there is a transaction error that takes one of two forms:

• The DELETE causes

  ERROR:  Operation failed. Try again.: 9286b55f-07f5-4ee8-a5bc-3e29c0d18d46 Conflicts with higher priority transaction: 464ae259-4d6c-4beb-ae2a-4e08174f27e7
  

• The COMMIT causes

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

For vanilla Postgres, it blocks on the DELETE until the COMMIT.

SELECT FOR SHARE, DELETE

Session A	Session B
CREATE TABLE d (i int);
INSERT INTO d VALUES (1);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM d FOR SHARE;
	DELETE FROM d WHERE i = 1;
COMMIT;

For Yugabyte, there is a transaction error that takes one of two forms:

• The DELETE causes

  ERROR:  Operation failed. Try again.: 2833764f-4198-45c5-9d1a-1a628b3c6e24 Conflicts with higher priority transaction: 5bafd882-c417-4b23-a92c-a22d184fbf31
  

• The COMMIT causes

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

For vanilla Postgres, it blocks on the DELETE until the COMMIT.

It doesn't matter whether i is a primary key.

SELECT FOR KEY SHARE, DELETE

Session A	Session B
CREATE TABLE d (i int);
INSERT INTO d VALUES (1);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM d FOR KEY SHARE;
	DELETE FROM d WHERE i = 1;
COMMIT;

For Yugabyte, there is a transaction error that takes one of two forms:

• The DELETE causes

  ERROR:  Operation failed. Try again.: 6865ea81-1b94-42df-8581-ea847093925c Conflicts with higher priority transaction: e3399164-ceff-4a75-b134-bc0a847550ea
  

• The COMMIT causes

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

For vanilla Postgres, it blocks on the DELETE until the COMMIT.

It doesn't matter whether i is a primary key.

[jaki]

The following results are from a build after intermediate work on issue #2523
that changes the read time for SELECT FOR statements in snapshot isolation.

(I use SELECT 123 statements because of issue #2702.)

(mod-read) INSERT, SELECT FOR SHARE

Session A	Session B
CREATE TABLE m (i int PRIMARY KEY, j int);
INSERT INTO m VALUES (1, 10), (2, 20), (3, 30);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 123;
	INSERT INTO m VALUES (4, 40);
SELECT * FROM m <opt>;
COMMIT;

<opt>	Yugabyte	vanilla Postgres
FOR SHARE;	works	works
WHERE i = 4 FOR SHARE;	works	works
WHERE i > 2 FOR SHARE;	works	works
WHERE j = 40 FOR SHARE;	works	works
WHERE j > 20 FOR SHARE;	works	works

(mod-read) DELETE, SELECT FOR SHARE

Session A	Session B
CREATE TABLE m (i int PRIMARY KEY, j int);
INSERT INTO m VALUES (1, 10), (2, 20), (3, 30);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 123;
	DELETE FROM m WHERE i = 2;
SELECT * FROM m <opt>;
COMMIT;

<opt>	Yugabyte	vanilla Postgres
FOR SHARE;	works	causes error
WHERE i = 2 FOR SHARE;	causes error	causes error
WHERE i > 1 FOR SHARE;	works	causes error
WHERE j = 20 FOR SHARE;	works	causes error
WHERE j > 10 FOR SHARE;	works	causes error

For Yugabyte, the error looks like

ERROR:  Operation failed. Try again.: Value write after transaction start: { physical: 1572461224161622 } >= { physical: 1572461219862048 }

For vanilla Postgres, the error looks like

ERROR:  could not serialize access due to concurrent update

[jaki]

The following results are from after commit
073b342 (issue #2523).

(I use SELECT 123 statements because of issue #2702.)

(post-2523) INSERT, SELECT FOR SHARE

Session A	Session B
CREATE TABLE m (i int PRIMARY KEY, j int);
INSERT INTO m VALUES (1, 10), (2, 20), (3, 30);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 123;
	INSERT INTO m VALUES (4, 40);
SELECT * FROM m <opt>;
COMMIT;

<opt>	Yugabyte	vanilla Postgres
FOR SHARE;	fails	works
WHERE i = 4 FOR SHARE;	fails	works
WHERE i > 2 FOR SHARE;	fails	works
WHERE j = 40 FOR SHARE;	fails	works
WHERE j > 20 FOR SHARE;	fails	works

For Yugabyte, the error looks like

ERROR:  Operation failed. Try again.: Value write after transaction start: { physical: 1573074034359199 } >= { physical: 1573074014575273 }