At 04:53 PM 06-01-01 -0500, Ann Harrison wrote:

>I haven't read all the correspondence between you and Helen,

There's a LOT of confusion there... :)) I don't want to add to it, but I'm
in the process of writing quite a substantial tutorial on transaction
contexts for IB Objects and I'm finding the beta docs to be rather
inconsistent on the matter of locking. I'd really like for there to be a
paper somewhere (IBPhoenix.com or IB_ReallyUseful at IB2K), spelling it out
as it really is and possibly crystallising Brad's questions in some kind of
FAQ.

I think we need some kind of reference map showing the relationship between
the SET TRANSACTION parameters and the parameters that go into the TPB of
an API function call. The dispersion of documentation of these
inter-dependent topics around 5 of the 6 manuals moves an already tricky
issue into the realm of total mystery.

>but the transaction mode you want when you're getting the new
>identifier is protected write, reserving id_table. If you're
>using the API, call isc_start_transaction, using a transaction
>parameter block with options of isc_tpb_protected, isc_tpb_lock_write,
>"ID_TABLE".

In considering the effects of the parameters surfaced by SET TRANSACTION,
I'd forgotten totally about the protected/shared write implications. I
think I can get some help by studying Jason's implementation of the TPB
parameters in the IBO source, since I have IBO routines for multi-user
updating shared tables without incurring deadlocks, that I don't believe
need stronger isolation than SNAPSHOT.

I'd assumed I was queuing to get protected write access by grabbing a
pessimistic lock with SET AFIELD=AFIELD with the WAIT option. This appears
to work as expected, i.e. as I described to Brad. From the above
paragraph, I think I must be actually getting more aggressive locking than
I had previously assumed. In my implementations, it's OK - I'm getting
what I want. The fact is, I would never want to design a routine like the
one Brad describes, where every user needs exclusive ROW access (never mind
actually needing to apply RESERVING TABLE access !!) to a table before
being able to proceed with DML access to any other table.

>A protected write/reserving transaction acquires all its locks
>before it begins. In the case of short update transactions with
>a high conflict rate, this serialization is actually a plus. By
>avoiding conflicts and rollbacks, it gets better throughput than
>parallel execution.

As I understand Brad's requirements, he wants to avoid acquiring the
next_id from the id generator, if a rollback occurs in the succeeding DML
on the referred table. The only way to achieve that is to include the DML
in the same transaction, which means the transaction has to hold its
exclusive table lock until the succeeding DML actually commits or rolls back.

>Alternately, you could catch the update conflict error, rollback,
>and try again.

Brad said he wants to do this all on the server, i.e. he rejected the idea
of forcing rollback on any exception at all in the succeeding DML. He says
his model works with SQLAnywhere's locking strategy (which isn't necessary
a recommendation, but that's another war story!!). I shudder to think
about the implications of simply assuming things worked on the server side
when any uncommitted transaction has the potential to hold an unresolved
serialized block on this table.

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________