Firebird lock table / lock record

0
votes

Suppose you have one table for a Desktop application and several users. When a user opens a record, i want to lock this record. I have tried "WITH LOCK" statement. It works fine. But when a second users want to update the same record, i want to put a message "Sorry, you cannot work on this order because it is locked. Somebody else has opened this record before you". Firebird waits the first user to commit/rollback. I don t want to wait. I want to put an error message. Is there a simple way to ask firebird record lock status ?

Is there a way to lock a full table ? Or to put a semaphore/mutex (like get_lock on mysql) i have tried reserving on set transaction statement but it does not work. My wish is to display a message to the user. Not waiting.

Thanks

1
lockingfirebird

1 Answers

1
votes

If you don't want to wait, then configure your transaction to use NO WAIT, or a wait timeout. However controlling business rules like this through database transactions is not advisable as it requires long running transactions which inhibit garbage collection, increases the chain of interesting transactions, and increases the chance of update conflicts.

I'd advise to use different options like:

  • First to update wins
  • Change detection (eg by a timestamp or record version counter which is also used as a condition in the update statement), and allowing the user to overwrite or abandon his update (or maybe merge)
  • Explicit reservation by updating the record (setting the username) in a separate transaction. This might require cleanup or the ability for a user to break the reservation (eg if someone had it open for too long).

Note that Firebird uses multi version concurrency control (MVCC), so explicit locking is not really natural. See also this answer to Locking tables firebird, delphi.

Locking tables using RESERVING should be possible, but I have never used it, so I am not entirely sure how to use it although you probably also need to specify FOR PROTECTED READ (see Interbase 6.0 Embedded SQL Guide, pages 70/71).