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

You had questions as to my intent, and I admit, I didn't do the most
thorough job of explaining my problem. The scenario is two or more
users simultaneously entering item bids at a charity/benefit auction
(not an online auction). Normally, before posting the entry, the
application (in an "IsSold" function) checks AT_STATEMENT to see if
the given item is already in the table. In single user mode, or if
people are doing their entries with some time between, this is no
problem. But in the boundary case in which two users hit "Enter"
simultaneously, it has been possible to enter the same item twice.
This is what I'm trying to prevent. So, in this boundary case (which
admittedly should be rare but is still possible, given Murphy's Law),
if User A and User B both input the same item and then simultaneously
hit "Enter", someone (say User A) gets the Insert/ExecSQL first and
posts a record. User B's entry, if it doesn't (somehow) wait for User
A's transaction to complete and then do an "IsSold" query, can
blithely enter a second record, effectively "selling" the same item
twice.

So (if I understand your answer) I don't think what I want to do is a
"Dirty Read" ... I want to do a read after User A's entry completes &
posts/commits.

My transaction is set to Isolation=tiCommitted, RecVersion=True,
LockWait=False.

By trial-and-error, I've learned the following:
1) Since normal transaction processes only block users from a given
row (or rows), it doesn't prevent users from changing or inserting two
different rows (usually a good thing).

2) Because User A & B's simultaneous entry results in two different
rows, I gather I need some sort of "table lock" to prevent this.

3) I got from one entry in the IBObjects help that I could accomplish
this with a query "Select <field> from AT_STATEMENTS" tied to this
transaction. (I call it qLkATStmt). So, if I understand correctly, if
this query is Open, it will have the effect of a table lock; and if it
is Closed, and the transaction committed or rolled back, that the
table lock is off and we revert to strictly record locking.

4) Since I wrote my original question, by trial and error I've learned
that whatever lock-conflict problems I've been having stemmed from
failure to "clean up after myself" (i.e., force closure on qLkATStmt
and do something to refresh the transaction).

Am I on the right track, or is there a better way?