sql: support FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}

[rafiss]

#6583 and #40206 added support for SELECT FOR UPDATE. There is additional syntax to support: SKIP LOCKED and NOWAIT. See an example of the syntax here: https://www.postgresql.org/docs/current/sql-select.html

---

Update, Feb 2021: CockroachDB v20.2 supports the NO WAIT syntax. SKIP LOCKED is still not supported at the time of this writing.

Epic CRDB-11976

Jira issue: CRDB-5535

[nvanbenschoten]

This rest of this was addressed by #43868.

[rafiss]

@nvanbenschoten thanks for adding this to the parser! It looks like #43868 adds code that references this issue (i.e. unimplementedWithIssueDetailf(40476, ...)) , so I'm going to leave this open. Though if you would prefer, we could also create a new issue and refer to that instead.

[nvanbenschoten]

Good point, thanks for re-opening. I was thinking this was now covered by #40205, but that's not the case.

[KoryNunn]

Has there been any activity towards adding NOWAIT recently?

[nvanbenschoten]

Hi @KoryNunn, unfortunately, NOWAIT is not currently on our short term roadmap. Do you mind elaborating on your use case for the functionality?

[KoryNunn]

Sure, we would like to use it to check if processing should be done on a record. If any other process has a lock on a row, it should ignore it, as another process is working on it.

Currently without NOWAIT all processes that look up a row have to wait untill the lock is released, see that processing is complete, and then skip the row.

If you have any suggestions to get equivalent functionality without NOWAIT, I'd be very happy to learn.

[kocoten1992]

I want to share my experience, I'm also building a queues, before I though I would need SELECT FOR UPDATE and SKIP LOCKED, turn out I didn't need any of those!!

My trick is to use CTE with RETURNING clause, for example:

with abc as (
    select id table_1 where ... limit 1
), xyz as (
    update table_2 set ... returning id
), mnt as (
    delete from table_3 where table_1_id in (select id from table_1) and not exists (select * from xyz)
) select 1;

You get the idea!

Since with CTE we could run many sql, you can get rid of transaction (autocommit mode) and still guarantee serialize isolation. That how I don't need these feature anymore.

P/s: here another magic trick I've learn with CTE, multiple select on many table at the same time, for example: you want to select 2 columns from table table_1 and one column from table_2, how can it be done with a single sql only ? BE HOLD

with
select_table_1 as (
    select row_to_json(row(a, b)) as result from table_1
),
select_table_2 as (
    select row_to_json(row(c)) as result from table_2
)
select result from select_table_1
union all
select result from select_table_2
--admittedly the CTE part in this case is redundant, I got carried away and don't know where else I should post them

[Nican]

I am in need of SKIP LOCKED for a ticketing system. I have a scenario where each user can only claim one ticket, and would like a method for users to claim the first non-claimed ticket in a payment transaction.

Also making queues would be nice.

[Nican]

Wouldn't this work ? update tickets set user_id = ?, claimed = true where claimed = false returning *

That is not the only operation inside of the transaction. The order must be completed, vouchers may have been claimed, and other things. I would like to keep the atomicity of the whole operation.

[Bessonov]

Transactions are serialized (not linearilized) by design. Do you have an example when it won't work for you? And look at CTE above.

[Nican]

Thank you for taking your time @kocoten1992 / @Bessonov.

I just took some time to test things.

The original issue that we had is that were using a in_stock value in a products table. Every time an order was complete, a UPDATE products SET in_stock=in_stock-1 query would be executed, but we found that the row would be locked, and only allow 1 transaction to be executed at the time. This was causing delays sometimes when several orders were being processed at the same time for the same product. (Unfortunately, due to some design decisions, some orders may take up to 3 seconds to process)

I just did some tests by using the tickets table without using "SKIP LOCKED", and while the operations are still serialized, it has the lock/delay issue. Each transaction is going to be waiting on other transactions to find an available ticket.

Example code:

create table orders(id int primary key, status string);
create table tickets (id int primary key, claimed boolean);
insert into orders values (0, 'unpaid'), (1, 'unpaid');
insert into tickets values (0, false), (1, false);

User 1:

begin transaction;
update orders set status='paid' where id=0;
update tickets set claimed=true where claimed=false limit 1 returning *;

User 2:

begin transaction;
update orders set status='paid' where id=1;
update tickets set claimed=true where claimed=false limit 1 returning *;

User 2 is stuck until User 1 runs commit. While the code functions correctly, it is a bottleneck during periods of high load.

[kocoten1992]

@Nican , moved the discussion here https://www.reddit.com/r/CockroachDB/comments/pk4nqa/using_cte_instead_select_for_updateskip_locked/

[Bessonov]

Doesn't something like work for you? (I have no crdb on this laptop):

with t as (
  select * from tickets where claimed=false limit 1
),
o as (
  update orders set status='paid' where id=0
)
update tickets set claimed=true where id in (select id from t) returning *

[otan]

this blocks hasura

i've put this on KV for now, because i think a lot of the work is in that layer