[YSQL] Serializability definition violation

[wengsy150943]

Jira Link: DB-6333

Description

When runing a workload on the lasted version of YugabyteDB, we finds that a query violates the definition of Serializability isolation levels.
We have run the same workload on PostgreSQL, the violation does not happen.
More specifically, as shown in the following table, the workload first launches a serializable transaction (ID=0) to query the record (k=62).
Next, the workload launches another serializable ransaction (ID=19) to modify the record (k=3) and commit.
Finally, the transaction (ID=0) queries the record (k=3).
The expect query result is the transaction (ID=0) fetches the version before it began (k=3), that is the initial version(v=28703).
However, the acctual query result is 15574, which violate the definition of default isolation level Serializability.
The default isolation level Serializability should see the data committed before the query began.

The initial schema is :

create table table0 (k int primary key, v int);
insert into table0 values(3, 28703);
insert into table0 values(62, 16541);

Then we run:

transaction ID	operation
0	START TRANSACTION isolation level serializable
0	SELECT k,v FROM table0 WHERE k=62;
19	START TRANSACTION isolation level serializable
19	UPDATE table0 SET v=15574 WHERE k=3;
19	commit
0	SELECT k,v FROM table0 WHERE k=3;

YugabyteDB version we use is PostgreSQL 11.2-YB-2.17.2.0-b0 on x86_64-pc-linux-gnu, compiled by clang version 15.0.3 (https://github.com/yugabyte/llvm-project.git 0b8d1183745fd3998d8beffeec8cbe99c1b20529), 64-bit.

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[pkj415]

This isn't a bug, just a difference in observed behaviour due to the different implementations for serializable isolation in YugabyteDB vs Postgres. YugabyteDB uses 2PL (two phase locking) whereas Postgres uses SSI (Serializable Snapshot Isolation) for implementing serializability.

• In Postgres, transaction 0 is ordered (or serialized) before transaction 19
• In YugabyteDB, transaction 0 is ordered (or serialized) after the transaction 19

Both implementations will honor the correctness guarantee of serializability, they only differ in how they order the transactions.

[m-iancu]

Thanks @pkj415. Closing this as duplicate -- Postgres-compatibility of transactions is broadly tracked in this issue: #5683.