[YSQL] Support SELECT ... NOWAIT in REPEATABLE READ and SERIALIZABLE isolation level

[pkj415]

Jira Link: DB-503

Description

Currently, NOWAIT is supported in READ COMMITTED isolation level only.

[FranckPachot]

@pkj415 What is the status of this issue?

I see the expected behavior: not waiting but an error if it cannot lock the row:

yugabyte=# create table kv ( key int primary key, value int);
CREATE TABLE

yugabyte=# begin transaction isolation level repeatable read;
BEGIN

yugabyte=*# SELECT Key, Value FROM KV WHERE Key=1 FOR UPDATE NOWAIT;
WARNING:  Setting wait policy to NOWAIT which is not allowed in REPEATABLE READ isolation (GH issue #12166)

 key | value
-----+-------
   1 |   100
(1 row)

yugabyte=*# \! psql -c " begin transaction isolation level repeatable read; SELECT Key, Value FROM KV WHERE Key=1 FOR UPDATE NOWAIT; commit;"

WARNING:  Setting wait policy to NOWAIT which is not allowed in REPEATABLE READ isolation (GH issue #12166)
BEGIN
ERROR:  could not obtain lock on row in relation "kv"

On other tries (probably depending on transaction priority) it succeeds, and the other transaction fails on Unknown transaction, could be recently aborted. But in all case it's a NOWAIT behavior.