Subject Re: [firebird-support] Locking tables
Author Ann W. Harrison
Gustavo wrote:
> I am developping an application in Delphi 5 and I am using IBX.
> Sometimes my application does processing on big tables that last
> a lot of time and it needs to be sure that it can complete the
> operation when it commits the transaction. So I want to lock the
> tables involved before beginning the operation.

That's not necessary. If your transaction completes, the changes will
all be there. I would run the transaction 'concurrency', 'wait'.
Tables will be locked on reference in shared write mode. You run a risk
of having a deadlock / update conflict error which you need to trap,
rollback, and restart.
> I tried starting a transaction with parameters 'consistency',
> 'nowait', 'lock_write=<TableName>' and 'protected' and it locks
> the table TableName. It works but I canĀ“t use it because I have to
> know which table to lock when I start the transaction and sometimes
> I start the transtaction and after that, depending on the result
> of other processing, I know what tables to lock.

If you really really need to avoid the possibility of deadlock, then you
need to start a 'consistency' 'wait' transaction and lock all the tables
you'll use at the beginning of the transaction, meaning all the tables
you can possibly reference. That's going to stop everybody else from
running, but it's guaranteed deadlock free.
> Does anybody know how to lock a table when a transaction is already started?
If you start a 'consistency' 'wait' transaction and don't lock any
tables, Firebird will lock tables for protected read and protected write
as you reference them. If you start with a select on a table, you'll
get protected read which is compatible with shared read and other
protected read transactions. When you change a table, Firebird will try
to upgrade the lock from protected read to protected write. That may
work, or it may not, depending on what else is going on. If you lock
tables incrementally, you risk getting a deadlock. If you're going to
handle deadlocks, use 'concurrency' and improve your overall system