[YSQL] READ COMMITTED definition violation

[Coconut-DB1024]

Jira Link: DB-6332

Description

When runing a workload on the lasted version of YugabyteDB, we finds that a query violates the definition of READ COMMITTED 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 read committed transaction (ID=0) to query the record (k=62).
Next, the workload launches another read committed transaction (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 lasted version of the record (k=3), that is, 15574 installed by the transaction (ID=19).
However, the acctual query result is 28703, which violate the definition of default isolation level READ COMMITTED.
The default isolation level READ COMMITTED should see the data committed before the query began.

transaction ID	operation
0	START TRANSACTION;
0	SELECT k,v FROM table0 WHERE k=62;
19	START TRANSACTION;
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.

Note that, the database schema and the initial datbase are as follows
drop database if exists "reproduceDB";
create database "reproduceDB";
create table table0 (k int primary key, v int);
insert into table0 values(3, 28703);
insert into table0 values(62, 16541);

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

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

[FranckPachot]

Hi @Coconut-DB1024, by default the "read committed" still maps to repeatable reads. Since read commited have been implemented, it must be enabled by starting the tablet servers with:
--yb_enable_read_committed_isolation=true.
See: https://dev.to/yugabyte/how-to-set-read-committed-in-yugabytedb-2m6c

and then your example has the same behavior as PostgreSQL:

postgres=# create table table0 (k int primary key, v int);
CREATE TABLE
postgres=# insert into table0 values(3, 28703);
INSERT 0 1
postgres=# insert into table0 values(62, 16541);
INSERT 0 1
postgres=# START TRANSACTION;
START TRANSACTION
postgres=*# SELECT k,v FROM table0 WHERE k=62;
 k  |   v
----+-------
 62 | 16541
(1 row)

postgres=*# \! psql -c "UPDATE table0 SET v=15574 WHERE k=3;";
Pager usage is off.
UPDATE 1
postgres=*# SELECT k,v FROM table0 WHERE k=3;
 k |   v
---+-------
 3 | 15574
(1 row)

[Coconut-DB1024]

Hi, @FranckPachot, I think ydb should fix this issue, so as to compatiable with PostgreSQL.
It is very strange that we set isolation level as “read committed” while the actual one is “repeatable read”.
Further，in PostgreSQL, we can set isolation level for each transaction individually, which is very convenient for our application.

[wengsy150943]

Hi,@FranckPachot, it might be confusing when I run show default_transaction_isolation;. Though I start server without extra settings, i.e., without enable read committed, the command also returns read committed.

[FranckPachot]

@wengsy150943 Yes, that's right, because of some backward compatibility with versions where Read Committed was not implemented yet.
You can yb_effective_transaction_isolation_level to show the effective value.

[FranckPachot]

@Coconut-DB1024 you should set --yb_enable_read_committed_isolation=true and then it is all postgres compatible. It is still set to false in the current version for backward compatibility, but it makes sense to set to true for all new clusters.

[m-iancu]

To add more context see: https://www.postgresql.org/docs/11/transaction-iso.html
It is technically correct to implement an isolation level as a stricter one (since being stricter it does avoid the unallowed phenomena). PostgreSQL itself does this for READ UNCOMMITTED (implementing it as READ COMMITTED internally).
So this is not a correctness violation rather just a Postgres compatibility issue.

However, as we are aiming for Postgres compatibility we are planning to eventually have the same default behavior as Postgres.
This is broadly tracked in: #13557 so closing this as duplicate.
(Until then the flag @FranckPachot mentioned should help -- if there's any issues with that flag please let us know)