[YSQL] Unneccessary lock with concurrent read and write transactions

[wb14123]

Jira Link: DB-13972

Description

There is a case Yugabytes create unnecessary lock when there is a concurrent read and write transaction when isolation level is serializable:

Prepare:

create table test (k int, v int);
insert into test values (1, 1);

T1:

begin transaction isolation level serializable;
select * from test where k = 1;

Before t1 commit, start t2:

begin transaction isolation level serializable;
insert into test values (1, 2);

Now t2 hangs. But it's better not since t1 hasn't do any write yet and most likely won't since the select doesn't hint with for update. It wouldn't be too late to abort if t1 actually write something later.

I understand the current behavior doesn't violates any correctness guarantee. Just something can be improved for better performance.

Issue Type

kind/enhancement

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

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

[FranckPachot]

In this situation, YugabyteDB employs a wait-on-conflict strategy, using read locks and detecting conflicts as soon as possible during write operations. In contrast, PostgreSQL uses a fail-on-conflict approach, relying on predicate locks and detecting conflicts at the time of commit.

It wouldn't be too late to abort if t1 actually wrote something later.

Yes, but depending on the cases, the throughput may be better without errors and retries.
I explained this in detail here: https://dev.to/franckpachot/a-brief-example-of-a-serializable-transaction-with-ansiiso-sql-1a70.

The advantage of YugabyteDB is that it minimizes unnecessary retriable errors, whereas PostgreSQL has the benefit of not waiting if there are no conflicts. But there's an issue to track this possible improvement.

since t1 hasn't do any write yet and most likely won't since the select doesn't hint with

If there's no intent to write, then better use a lower isolation level (read only if there's no write at all, or read committed if there's no write that may cause a serializable anomaly). The goal of serializable is not to have to declare the intent in advance.

[wb14123]

Thanks for clarification. Yeah the strategy has pros and cons but if that's something by design then I'm okay with that.

read only if there's no write at all

Do you mean snapshot isolation here?