Open jaki opened 4 years ago
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.)
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 |
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
The following results are from after commit 073b342316976fd583d76cfb71651f02aed5df0e (issue #2523).
(I use SELECT 123
statements because of issue #2702.)
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 }
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 #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
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
causesFor vanilla Postgres, the last
INSERT
causesDELETE, SELECT FOR SHARE
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
causesIt doesn't matter whether
i
is a primary key.DELETE, SELECT FOR KEY SHARE
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
causesIt doesn't matter whether
i
is a primary key.INSERT, DELETE
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
causesThe
COMMIT
causesFor vanilla Postgres, it blocks on the
DELETE
until theCOMMIT
.SELECT FOR SHARE, DELETE
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
causesThe
COMMIT
causesFor vanilla Postgres, it blocks on the
DELETE
until theCOMMIT
.It doesn't matter whether
i
is a primary key.SELECT FOR KEY SHARE, DELETE
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
causesThe
COMMIT
causesFor vanilla Postgres, it blocks on the
DELETE
until theCOMMIT
.It doesn't matter whether
i
is a primary key.