Firebird lock and select unlocked fields

Question

I have a query

SELECT FIRST 10 * FROM FP_TASKS WITH LOCK

but I have multiple clients using a table, and I need for each selected client 10 rows that are not locked.. Is there any way to do something like below?

SELECT FIRST 10 * FROM FP_TASKS
 WHERE ROW NOT LOCKED
WITH LOCK

Answer 1

If you hold a lock a row, then another select .. with lock in another transaction will either wait for the lock to be released or raise an exception (depending on the transaction configuration).

There is no way to ignore or skip locked rows when selecting. The Firebird documentation also explicitly says:

WITH LOCK provides a limited explicit pessimistic locking capability for cautious use in conditions where the affected row set is:

a. extremely small (ideally, a singleton), and
b. precisely controlled by the application code.

Your query is neither 'extremely small' nor 'precisely controlled' by your application.

You should consider allocating a row using a short transaction that updates the row with some type of connection-specific claim, or maybe a single producer or resource manager that allocates rows to a specific client.