Add new clause to FOR UPDATE WITH LOCK: SKIP LOCKED - for selecting 'free' record(s) from table or updatable view [CORE4385]

[firebird-automations]

Submitted by: @pavel-zotov

Currently one need to write cursor with trying to update record and catch lock conflicts in when-block to skip rows which are locked by others.
It will be useful if FB has the mechanism to obtain non-locked record(s) directly in SELECT statement.

[firebird-automations]

Modified by: @pavel-zotov

priority: Major [ 3 ] => Minor [ 4 ]

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Pavel,

What would be the purpose/benefit of such a feature?

The current WITH LOCK functionality ensures that no other operations are active which are working on the rows, but it guarantees that the entire set is updated.

This feature would have only part of the set being updated, but the rows that are updated or skipped would be unknown.

[firebird-automations]

Commented by: @pavel-zotov

Suppose that there are many connects-'producers' (which CREATE documents) and several connects-'comsumers' (which HANDLES documents that have been created).

Each consumer "A" knows how to randomly choose arbitrary document, but it can easy encounter lock conflict while another consumer "B" is handling this record. (I intentionally speak about RANDOM strategy of choosing rather than sequential one).

If this attachment "A" will wait during lock_timeout and ends up with success, then most probably it will have to SKIP rest operation because "B" already has finished handling with this row. So, the better way for "A" would be to skip such record IMMEDIATELLY if he sees it as locked.

> The current WITH LOCK functionality ensures that no other operations are active which are working on the rows, but it guarantees that the entire set is updated.

I want to minimize number of lock conflicts with exceptions and to maximize speed of handling records. In my OLTP-emulating test each connect-'consumer' must lock only ONE (single) parent record at once, do something with child records and change state field of parent record.
If there will be some 'queue' of other connects ("consumers") all of which waiting for this record, then after released lock one of them get this record and should CHECK AGAIN it state! This is because SELECT FOR UPDATE WITH LOCK does NOT raise any exception when he get lock on record which has been changed by concurrent.
So, at least this leads to complication of code.

> This feature would have only part of the set being updated, but the rows that are updated or skipped would be unknown.

You're right: I need to update only ONE record of some set (usially this is view with WHERE clause).
ID of row which I'm take for handling can be returned into variable, like this:

UPDATE T SET ID=ID WHERE ID = fn_fast_random_choice( ... ) RETURNING RDB$DB_KEY INTO V_DB_KEY

Rows that I needed to SKIP - they just don't matter for me. Because they are handled by other "consumers".

[mrotteveel]

Duplicate of: #7350 (implemented in Firebird 5.0)