Simon:

I already looked at (1.5) Explicit locking on Page 15 of the release notes for FB 1.5 but it's about record locking and what I need is table locking. Thanks anyway.

Adam / Ann

I was not clear but I see you understood my problem anyway. I know that when the transaction commits it has completed. As Adam said, what I don't want is that my transaction finds that some other transaction has modified a record it needs to modify when it is near the end of its processing and have to roll back.

I appreciate the ideas and information you gave me. Thank you very much.

Gustavo

----- Mensaje original -----
De: Ann W. Harrison
Para: firebird-support@yahoogroups.com
Enviado: Viernes, 22 de Abril de 2005 13:39
Asunto: Re: [firebird-support] Locking tables


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

Regards,


Ann



------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




[Non-text portions of this message have been removed]