SELECT ... FOR UPDATE SKIP LOCKED

[Adriano dos Santos Fernandes]

Hi!

At least PostgreSQL and Oracle has this feature.

It allows to easily implement work queues, where many workers read
records from a table and no one get duplicate records.

select item
from work_items
for update
skip locked

For Firebird this would be specially interesting to use with events.

A routine may insert data in some table and post a event and more than
one worker thread is listening for events.

Firebird implements FOR UPDATE different than others, and actually WITH
LOCK is more likely others FOR UPDATE (without SKIP LOCKED).

But I don't see a good way to mix WITH LOCK and SKIP LOCKED.

I propose we implement FOR UPDATE SKIP LOCKED clause with following
semantics:
- It will disable batches like FOR UPDATE
- It will lock rows like WITH LOCK
- It will skip locked rows (new feature)

With this clause even WAIT transactions will not block on these lookups.

Do you see any blocker to implement this for Firebird?

In a very raw prototype implementation I see correct results.


Adriano

[Dmitry Yemanov]

08.10.2022 16:47, Adriano dos Santos Fernandes wrote:
>
> At least PostgreSQL and Oracle has this feature.
>
> It allows to easily implement work queues, where many workers read
> records from a table and no one get duplicate records.

Agree, I also had it in mind for the nearest future.

> Firebird implements FOR UPDATE different than others, and actually WITH
> LOCK is more likely others FOR UPDATE (without SKIP LOCKED).

Correct.

> But I don't see a good way to mix WITH LOCK and SKIP LOCKED.

Just an idea:

WITH LOCK [{WAIT | NO WAIT}]

where WAIT is our current (and default) behaviour while NO WAIT means
skipping the record we could not lock.

But I admit it may be not really friendly to users.

> I propose we implement FOR UPDATE SKIP LOCKED clause with following
> semantics:
> - It will disable batches like FOR UPDATE
> - It will lock rows like WITH LOCK
> - It will skip locked rows (new feature)

I respectfully disagree, because I'd like FOR UPDATE to really do what
it means -- ensure the cursor is updatable (i.e. throw an error if it's
non-updatable). Disabling protocol-level buffering may remain as a bonus
feature (and to preserve backward compatibility). But this way it does
not match other DMBS's behaviour and mixing things is going to be even
more confusing.

Personally, I'd rather go for [somewhat ugly] WITH LOCK SKIP LOCKED
instead of reusing the FOR UPDATE semantics. Or maybe we could still
find a better syntax.

> Do you see any blocker to implement this for Firebird?

No. Although I have a question: whether only SELECT WITH LOCK is allowed
to skip already locked rows? Could it be useful for regular SELECTs
(without locking) too? What about UPDATEs and DELETEs?


Dmitry

[Dimitry Sibiryakov]

Dmitry Yemanov wrote 08.10.2022 17:10:
>> But I don't see a good way to mix WITH LOCK and SKIP LOCKED.
>
> Just an idea:
>
>   WITH LOCK [{WAIT | NO WAIT}]
>
> where WAIT is our current (and default) behaviour while NO WAIT means skipping
> the record we could not lock.

Isn't what the transaction's parameter is for?

--
WBR, SD.

[Dmitry Yemanov]

08.10.2022 18:38, 'Dimitry Sibiryakov' wrote:
>
>>> But I don't see a good way to mix WITH LOCK and SKIP LOCKED.
>>
>> Just an idea:
>>
>>    WITH LOCK [{WAIT | NO WAIT}]
>>
>> where WAIT is our current (and default) behaviour while NO WAIT means
>> skipping the record we could not lock.
>
>   Isn't what the transaction's parameter is for?

No. (1) NO WAIT transaction raises an error while we want to skip locked
row silently in the discussed feature. (2) You may want to skip locked
rows inside WAIT transactions too.


Dmitry

[Adriano dos Santos Fernandes]

Em sáb., 8 de out. de 2022 12:10, Dmitry Yemanov <fire...@yandex.ru> escreveu:

> But I don't see a good way to mix WITH LOCK and SKIP LOCKED.

Just an idea:

   WITH LOCK [{WAIT | NO WAIT}]

where WAIT is our current (and default) behaviour while NO WAIT means
skipping the record we could not lock.

But I admit it may be not really friendly to users.

NO WAIT would be understandable as fail fast, not skip.

> I propose we implement FOR UPDATE SKIP LOCKED clause with following
> semantics:
> - It will disable batches like FOR UPDATE
> - It will lock rows like WITH LOCK
> - It will skip locked rows (new feature)

I respectfully disagree, because I'd like FOR UPDATE to really do what
it means -- ensure the cursor is updatable (i.e. throw an error if it's
non-updatable).

Yes, that's a point I missed.

Disabling protocol-level buffering may remain as a bonus
feature (and to preserve backward compatibility). But this way it does
not match other DMBS's behaviour and mixing things is going to be even
more confusing.

Personally, I'd rather go for [somewhat ugly] WITH LOCK SKIP LOCKED
instead of reusing the FOR UPDATE semantics.

This is the alternative I've thought as well (or WITH LOCK SKIPPING LOCKED), but it's ugly.

Or maybe we could still
find a better syntax.

> Do you see any blocker to implement this for Firebird?

No. Although I have a question: whether only SELECT WITH LOCK is allowed
to skip already locked rows?

Could it be useful for regular SELECTs
(without locking) too?

I think no.

What about UPDATEs and DELETEs?

It could be interesting. I don't look at it possible implementation yet.

Adriano

[Mark Rotteveel]

On 08-10-2022 17:10, Dmitry Yemanov wrote:
> 08.10.2022 16:47, Adriano dos Santos Fernandes wrote:
>>
>> At least PostgreSQL and Oracle has this feature.
>>
>> It allows to easily implement work queues, where many workers read
>> records from a table and no one get duplicate records.
>
> Agree, I also had it in mind for the nearest future.
>
>> Firebird implements FOR UPDATE different than others, and actually WITH
>> LOCK is more likely others FOR UPDATE (without SKIP LOCKED).
>
> Correct.
>
>> But I don't see a good way to mix WITH LOCK and SKIP LOCKED.
>
> Just an idea:
>
>   WITH LOCK [{WAIT | NO WAIT}]
>
> where WAIT is our current (and default) behaviour while NO WAIT means
> skipping the record we could not lock.
>
> But I admit it may be not really friendly to users.

I think that syntax isn't clear what it does. For example, given its
similarity to the SET TRANSACTION form, I would sooner expect it to
inform whether you wait for the lock to become available or end with an
error. At least "SKIP LOCKED" tells you what it does. The downside is
that this might introduce yet more confusion surrounding "FOR UPDATE".

Personally, given both Oracle and PostgreSQL uses "FOR UPDATE SKIP
LOCKED", I think we should use that as well, but the semantics should
then be clearly defined (e.g. does it imply "WITH LOCK", or not, etc.).

As an alternative, SQL Server uses WITH(READPAST) for this, using a more
generalized "hints" feature:
https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16

Using such a generalized hint feature might allow for future expansion
to other types of hints as well.

I do think we should consider whether this would need to be applied for
other DML statements as well.

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

Em sáb., 8 de out. de 2022 13:02, Adriano dos Santos Fernandes <adri...@gmail.com> escreveu:

What about UPDATEs and DELETEs?

It could be interesting. I don't look at it possible implementation yet.

Looking at this again. If we have UPDATE/DELETE SKIP LOCKED, then SELECT FOR UPDATE SKIP LOCKED becomes a natural choice too (for who needs it).

Then WITH LOCK SKIP LOCKED is for who do not want FOR UPDATE semantics.

Adriano

[Dmitry Yemanov]

08.10.2022 19:02, Adriano dos Santos Fernandes wrote:

> I respectfully disagree, because I'd like FOR UPDATE to really do what
> it means -- ensure the cursor is updatable (i.e. throw an error if it's
> non-updatable).
>
> Yes, that's a point I missed.

Well, this is not the point yet, as this behaviour does not exist, it's
just in my plans. But it's defined in the standard while all others
DMBS's usage of FOR UPDATE for locking does not comply. We need to
decide what camp we're in. If we go with other players, then some other
clause could be invented for updateability checking.


Dmitry

[Adriano dos Santos Fernandes]

Here is a situation I'd like others opinions.

Considering two ISQL connections with default transaction options
(snapshot, wait):

C1: connects
C2: connects
C1: select * from t0 where n = 1 with lock;
C1: disconnects
C2: select * from t0 where n = 1 with lock;

In this situation C2 does not wait, it throws exception:

deadlock
-update conflicts with concurrent update

If C2 uses SKIP LOCKED, should it skip the record instead?

I guess yes, but since it's in another code place, I'd like to ask
others opinions.


Adriano

[liviuslivius]

Hi

Interesting. 

You have written

C1: disconnects

Is that mean that the lock is not released after transaction is finished? And c2 can got update conflicts even if c1 is disconnected? Is there transaction numer in the message? Normally it is there. Or is this mistyped?

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Adriano dos Santos Fernandes <adri...@gmail.com>

Data: 11.10.2022 11:41 (GMT+01:00)

Do: firebir...@googlegroups.com

Temat: Re: [firebird-devel] SELECT ... FOR UPDATE SKIP LOCKED

--
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/42341f75-382c-f8a6-c9fb-da6e9033dc25%40gmail.com.

[Mark Rotteveel]

On 11-10-2022 14:24, liviuslivius wrote:
> Interesting.
> You have written
> C1: disconnects
>
> Is that mean that the lock is not released after transaction is
> finished? And c2 can got update conflicts even if c1 is disconnected? Is
> there transaction numer in the message? Normally it is there. Or is this
> mistyped?

Locks are achieved by creating a new, identical record version for the
record (with the transaction id which locked it).

Both C1 and C2 are using the SNAPSHOT isolation level, thus from the
perspective of C2, the records are still locked (assuming transaction C1
was committed, not rolled back), because they have a record version
which was not yet committed when C2 started.

I guess you're thinking of read committed behaviour instead.
--
Mark Rotteveel

[Attila Molnár]

This is a complex issue.

How skip locked should work when the lock succeeded on the "base" update record, but via update trigger a lock conflict arise for an other record (in the same table, or other table). Skip locked also should affect triggered updates?

Skip lock is like an exception swallow, which is bad practice (but some rare case has its place).

It would be better if mon$is_locked(rdb$db_key) system function existed and with that you could filter via WHERE or IF. It would provide greater flexibility, can be used also in PSQL, and in UPDATE, DELETE, MERGE command.

[Attila Molnár]

Igonre the first part, I realised it's not an issue, as the skip locked would be the part of the SELEC, not the UPDATE.

[Adriano dos Santos Fernandes]

Em ter., 11 de out. de 2022 11:21, Attila Molnár <amolna...@gmail.com> escreveu:

This is a complex issue.

How skip locked should work when the lock succeeded on the "base" update record, but via update trigger a lock conflict arise for an other record (in the same table, or other table). Skip locked also should affect triggered updates?

Skip lock is like an exception swallow, which is bad practice (but some rare case has its place).

It would be better if mon$is_locked(rdb$db_key) system function existed and with that you could filter via WHERE or IF. It would provide greater flexibility, can be used also in PSQL, and in UPDATE, DELETE, MERGE command.

There is no point in ask if something is locked without lock it.

Adriano

[Adriano dos Santos Fernandes]

Hi!

Can this https://github.com/FirebirdSQL/firebird/pull/7350 be merged or
is there any point to be decided/changed?


Adriano