[YSQL] Unexpected error in SELECT FOR UPDATE in serializable transaction

[andrei-mart]

Jira Link: DB-7521

Description

CREATE TABLE t (a INT, PRIMARY KEY (a ASC));
INSERT INTO t(a) VALUES(1);
begin transaction isolation level serializable;
SELECT * FROM t ORDER BY a FOR UPDATE;

Last statement fails with

ERROR:  Serializable locking should have been done in the scans.

It seems the difference with working case SELECT * FROM t FOR UPDATE; is that IndexScan is being used instead of SeqScan.

yugabyte=# explain SELECT * FROM t ORDER BY a FOR UPDATE;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 LockRows  (cost=0.00..124.00 rows=1000 width=36)
   ->  Index Scan using t_pkey on t  (cost=0.00..114.00 rows=1000 width=36)
(2 rows)

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

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

[foucher]

Commit 94c98b3 broke some cases such as the one in the repro, though I found one that seems not to have worked correctly previously. This output is from that commit's parent:

yugabyte=# BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
yugabyte=# EXPLAIN /*+ SeqScan(t) */SELECT * FROM t FOR UPDATE;
                           QUERY PLAN
----------------------------------------------------------------
 LockRows  (cost=0.00..110.00 rows=1000 width=36)
   ->  YB Seq Scan on t  (cost=0.00..100.00 rows=1000 width=36)
(2 rows)

yugabyte=# set yb_debug_log_docdb_requests to on;
SET
yugabyte=# /*+ SeqScan(t) */SELECT * FROM t FOR UPDATE;
 a
---
 1
(1 row)

The LockRows node didn't do anything at that point. Here is the docdb request output:

I0809 10:21:55.305405 3984137728 pg_session.cc:305] Applying operation: { READ active: 1 read_time: { read: <invalid> local_limit: <invalid> global_limit: <invalid> in_txn_limit: <invalid> serial_no: 0 } request: client: YQL_CLIENT_PGSQL stmt_id: 4569710880 schema_version: 0 targets { column_id: 10 } targets { column_id: -8 } column_refs { ids: 10 } is_aggregate: 0 limit: 1024 return_paging_state: 1 ysql_catalog_version: 1 table_id: "000033f1000030008000000000004000" col_refs { column_id: 10 attno: 1 } size_limit: 0 }
I0809 10:21:55.306047 3984137728 pg_session.cc:332] Flushing collected operations, using session type: kTransactional num ops: 1

Notice the lack of rowmarks.

[foucher]

Filed #18652 for the YB Sequential Scan issue.