That is the point of a generational transactional architecture like
Firebird. Your transaction can only see the state of the database as of
the start of your transaction.

Locking an entire table is rarely (if ever) desirable. For interactive
work, your should be pulling your data in one transaction, making the
changes in the GUI, and then posting all of the changes in a second
transaction.

Given that the average hard drive seek time is 10ms, this means that the
minimum amount of time that you can count on a table being unavailable
due to locking is 20ms. Even a tiny user base (10 people) could make
such a scheme unusable if they were moderately heavy users. The
hypothetical 10 user installation would be capable of locking users out
for up to 2 seconds during update, assuming that the transactions were
being opened and committed as rapidly as possible.

The demand for table locks suggests that you are trying to use the
BDE/ISAM Model in Delphi, and you want to lock the table while one user
edits it. This design anti-pattern is frequently encountered in this
forum. That model does not work with any RDBMS. The BDE/ISAM model is
not comfortable when it does not control the hardware.

Rather than insisting on table locks, you might want to start asking
design questions that will lead you towards a cleaner design that has
more a more efficient and effective data integrity model.

Such a design would likely be scalable to at least a moderate number of
concurrent users, and possibly into the range of a small enterprise
system. At the same time, your users would find it cleaner to use, and
your programming task would be made much easier.

On Fri, 2005-04-22 at 20:04 -0300, Gustavo wrote:
>
> 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]
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>