--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:

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

Just did a check ... I AM using IBObjects 4.8; I was getting my
version # off an out-of-date Help file...