Search code examples
sqlfirebirddeadlockdatabase-deadlocks

Firebird lock and select unlocked fields

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
Solution

  • 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.