>
> Hang on a minute. In previous posts, you said you got these lock

conflicts

> with updates. Now you are describing a different thing.

In that case, you have misunderstood my first post. Sorry.

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

Quoting myself again: "...even if firebird doesn't support
optimistic locking" -- by that, it is CLEAR that I am not asking for
IBO to support pessimistic locking. Don't know where you're getting at
with your answers.

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

How many times do I have to define what I mean by an update
transaction? Do I have to type "a transaction that contains update"
every single time? Every single operation happens in a transaction, be
it explicit or implicit. IBO implicitly does the transaction for us
even without an explicit call to StartTransaction. I've done the
low-level interfacing with GDS32.dll before. isc_start_transaction
will return the transaction handle in its second parameter (by return,
I mean writing to the address passed to isc_start_transaction in its
second parameter, in case you correct me again saying the return of
isc_start_transaction is actually ISC_STATUS). every DSQL then happens
within that transaction (e.g. isc_dsql_execute_immediate's third
parameter). I've not encountered something like that with my direct
API, only with IBO, on a MultiCPU / HT machine.

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

This will result in a dead lock with the example I gave. So, neither
WAIT nor NO WAIT will work.


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!

No matter how many times I have the thread retry, it will still give
the same error, while the rest of the threads continued updating their
records just fine (tested for 2 hours, the rest did not fail). It is
as if commit did not actually get executed.

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

Did I say I want any other types of locking mechanism? Perhaps you've
been answering this sort of questions too long and can't think outside
the box. Perhaps this is a flaw of which the commit did not get
executed, thus leaving that particular record locked and transaction
floating? I could not trace into IBO since BCB6 does not quite like
files like IB_Components.pas (which is over 32k lines long). If you
have BCB6, you will notice that the break points are screwed up in
pascal files that are too big in size.

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

You have been trying to give me answers which I can easily find from
the FAQ. The reason I'm posting here is that I believe something has
deviated from the supposed behavior. Anyway, that's alright. I don't
think I wanna waste any more energy on something which clearly no one
thinks is possible to have any flaws.

Thanks for the FAQ answers so far.

Regards,
Zach