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
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).
In Firebird 4.0 and earlier, 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.
Since Firebird 5.0, there is an optional clause SKIP LOCKED
for WITH LOCK
, which allows you to skip locked rows.