At 09:47 7/10/2008, Lane Campbell wrote:

>I,m working on an application that uses Delphi7, IBObjects 4.6, and
>Firebird 2.1.

You need IBO 4.8 for Fb 2.1 because IBO 4.6 doesn't have any support for the API changes in Fb 2.x.

>I can have multiple users simultaneously inserting
>records to a table (AT_STATEMENT) from different PCs on the network. I
>need to temporarily prevent User B from inserting until User A's
>insert is completed & committed. Then User B's application needs to
>check the data state before doing its insert. In absence of better
>knowledge, I'm assuming this needs a table lock on AT_STATEMENT. If
>so, what is the best way to do this? The way I'm doing it works only
>in one specific instance, and seems to deadlock (or lock conflict) any
>subsequent inserts/deletes from other places in the application (other
>forms, other queries, etc.)
>
>I know Delphi fairly well; but transaction processing is relatively
>new to me.

Frankly, you really need to understand the various attributes that you can set for transactions. You don't do explicit locking with Firebird or Interbase, although with Firebird and also with IBO you *can* do updates or deletes with one or another form of pessimistic locking.

I don't understand what you mean by "then User B's application needs to check the data state before doing its insert". The whole point of transaction isolation is to make each transaction see the database state consistently from beginning to end. Thus, there is NO WAY you can see (and thus check) what is happening in other transactions during the course of your transaction. (This capability is known as Dirty Read and is a total no-no for ACID-compliant database systems!)

What you do have is Read Committed isolation, which is as close as Fb gets to Dirty Read. What this gives your transaction is the ability to react to the results of transactions that were committed after your transaction began.

With Read Committed, you have two conflict resolution options: Record Version and No Record Version (see the RecVersion property of IBO).
-- When RecVersion is true (sets the RECORD_VERSION attribute) the transaction see the latest committed version of the record, even if another transaction has a more recent version pending (pending = changes posted but not yet committed or rolled back).
-- With RecVersion false (sets the NO_RECORD_VERSION attribute) the effect depends on whether your lock resolution setting is WAIT or NO WAIT (IBO property LockWait).
---- If LockWait is true, your ReadCommitted RecVersion(false) transaction will wait until the conflicting transaction commits or rolls back and will then try to commit.
---- If LockWait is false then your transaction will except immediately and report the conflict.

RecVersion has no effect on Snapshot (Concurrency) transactions. However, you can set a Concurrency transaction to wait or not. In practice, WAIT is fairly pointless in Concurrency isolation since the conflict can't be resolved unless the competing transaction rolls back.

Do you understand the two-step post-commit sequence of Firebird? As long as there is no conflict with an operation on a record that has been posted by another transaction, your transaction will write your changes to disk in a new record version that only your transaction and the Fb engine can see. As soon as that happens, the record is locked and no other transaction can change that record - it will be yours until you commit it or roll it back. In the Delphi environment, this part of the operation is referred to as Post. If you can't get that lock, because another transaction has a change pending to that record (has "posted" a new version) , then your transaction won't be allowed to post.

IBO offers a way to deal with this situation: you can set the PessimisticLocking attribute of the *dataset* (your query) to True. The effect of this is that, as soon as you call the Edit or Delete method of the dataset, IBO will attempt to post a dummy update to that record and thus either secure a lock or get a conflict exception. It handles the conflict exception by preventing the dataset from going into Edit or Delete mode.

PessimisticLocking has no effect on Insert mode though. Because of the optimistic locking design of Fb/IB, all inserts can be posted and will either succeed if there is no conflict or fail if there is either a conflict *or* any another transaction that is in ReadCommitted isolation. For this reason (and other reasons, natch!!) you shouldn't, for example, run reports in ReadCommitted isolation because that will prevent anyone from inserting until the report's transaction is finished.

There are transaction settings that allow table locks but there is virtually no situation where a table lock makes sense.

You wrote:

> I need to temporarily prevent User B from inserting until User A's
>insert is completed & committed.

It depends on your objective. If you are happy for User B to proceed with its intentions once User A has finished, then set up both transactions as ReadCommitted, RecVersion(false) LockWait(True) and MAKE SURE YOU DON'T ALLOW TRANSACTIONS TO GO LONG!! However, if you want User B to make a fresh try once the conflict situation has gone then use LockWait(False) and put a retry loop on the Post operation. I favour the second option because users will do what users will do, like going off to lunch leaving locks everywhere.

Helen