At 03:09 PM 5/10/2005 +0000, you wrote:

>All of them are using the NO WAIT locking policy. First of all, there
>is no outstanding transaction when I encounter the error. Every single
>post() is followed by a commit().
>
>
>There should not be a conflict:
>consider the following psuedo code:
>
>enter critical section
>insert()
>...
>post()
>commit()
>leave critical section

Hang on a minute. In previous posts, you said you got these lock conflicts
with updates. Now you are describing a different thing.

>There are
> > several conditions that could cause conflicts (and NOTE WELL, a lock
> > conflict is never a bug!!). For example, if a NO WAIT transaction
>goes to
> > update a record that has already been updated by another transaction
>since
> > this transaction began, and the isolation level is CONCURRENCY; or
>is READ
> > COMMITTED with RECORD_VERSION and this transaction is newer than the
>one
> > that has an update pending, then you will get a lock conflict.

The example that I gave was only one set of conditions where a lock
conflict could occur. This is another, different set of conditions! See
below...

>After each post, a commit is issued, before another cycle of edit() /
>post() / commit() (hence the what I termed serialization in the
>previous mail). There's no pending updates, nor is there any newer or
>older transaction.

From the point of view of the client, this is true. From the point of
view of the server, you have no way to tell. Calling Commit signals that
the client's work is finished but, from the server's perspective, it is the
beginning of the procedure to change the state of the database. (People
often fall into the same trap with Disconnect...!)

Unless you are writing a server tier, you'll also have to factor in the
time-lapse for remote clients. Always treat every request as just that - a
request - a beginning, not an end.

> > I reiterate - a lock conflict is NOT a bug. You need to work through
>your
> > requirements and the various transaction parameters and sort out
>what you
> > need to set up to fit them. A truth table would be useful for this.
>
>A lock conflict CAN be a bug, if it doesn't work as it should (i.e.
>locking the record when it should be locked, for example, a
>transaction has modified the record but has not been committed and
>another one tries to modify it).

Repeat, a lock conflict is not a bug. The server doesn't know anything
about threads or critical sections in your client code. It is maintaining
savepoint accounts per transaction. The fact that the locking doesn't work
the way you want it to doesn't make it a bug. However, it's a bug in your
application if your mistaken assumptions about locking cause you to code a
potential race condition (in the case of updates and deletes) or a
sure-fire conflict condition (in the case of inserts).

>I never mentioned the app was writing to the same record -- in fact, I
>specifically state that only one thread writes to the same record
>everytime -- i.e.
>
>thread 1 -- enter CS, insert record A, commit, leave CS, enter CS,
>delete record A, commit, leave CS, loop
>thread 2 -- enter CS, insert record B, commit, leave CS, enter CS,
>delete record B, commit, leave CS, loop
>... --> same goes for thread 3 and 4, but updating record C and D
>respectively
>
>I'm not seeing a conflict there, even if firebird doesn't support
>optimistic locking.

Firebird supports only optimistic, row-level locking in Read Committed and
Concurrency isolations. To get pessimistic locking in these levels you
need a hack - either the dummy update that IBO supports or the FOR UPDATE
WITH LOCK hack that ought to give everyone the creeps and, ideally, be shot
at dawn.

However, these are not just updates and deletes, are they? Row-level
locking is not the cause of locking conflicts with inserts.

>Every update transaction is performed in a
>critical section -- i.e. only one thread performs update transaction
>at one time.

Be clear that there is no such thing as "an update transaction". All
operations happen in a transaction, be it SELECTs, updates, deletes,
inserts or DDL.

>Should I expect a conflict in such a case?

You should expect a conflict when you attempt to insert into a set that is
isolated in Read Committed in NO WAIT by another transaction *for any
purpose*. The famous aphorism "In Firebird readers don't block writers"
does not apply to this case.

Since your intention is to "serialize" work (which, repeat, you can't truly
do in Read Committed or Concurrency isolation) with inserts in the picture,
you will get past the table-level conflicts if you place these transactions
in Concurrency isolation with a WAIT policy. However, you should never
assume that the order of things on the server will comply with the order of
requests from the client. Test, test, test!

If there is a genuine reason why the DML in a transaction has to be
pre-emptive, you'll need to hack your way past optimistic locking, or do
something draconian like using Consistency isolation or a RESERVING
clause. And such measures should certainly not be multi-threaded without
serious attention to exception handling.

No amount of inveigling from me will convince you (and I hereby give up
trying), but you owe it to yourself to test the behaviour of the many
transaction variants using multiple instances of isql or ib_sql. Draw up a
truth table and try everything, so that you can predict exactly what's
going to occur with each set of conditions.

Helen