On Saturday 06 January 2001 14:53, you wrote:

> Brad,
>
> >Two copies of the program at once. When
> >I do this, I get errors like this:
> >
> >Statement failed. SQLCODE = -913
> >deadlock
> >-update conflicts with concurrent update
> >
> > >From all I've read and understand, the WAIT in the SET TRANSACTION
> > > should
> >
> >stop this from happening. Please let me know if I've done something wrong
> >and if not, there is a problem within Interbase.
>
> I haven't read all the correspondence between you and Helen,
> 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".

Am I right that this implies a coarse lock on the whole table? If so, I
would prefer not to do this.

> InterBase's lock modes are a bit more complicated than those
> of a database which uses locks for concurrency control. It
> uses locks to indicate the presence of a transaction in a table.
> Normally that's a share lock, allowing other readers and writers.
> When you ask for a "consistency" mode, the system takes out a
> protected mode lock. Protected read is compatible with shared
> read and protected read locks. Protected write is compatible
> only with shared read.
>
> When two concurrent shared write transactions attempt to update
> the same row, they are in conflict. Neither can "see" the other's
> changes. Using the WAIT option doesn't change this - even in a
> read-committed mode transaction. (I suppose it could, but it
> doesn't.) WAIT is used to prevent "live-lock" the situation in
> which two transactions want to modify the same two rows, in
> reverse order. They each fail, retry, fail, retry, etc. WAIT
> causes one of them to wait until the other has committed or rolled
> back.

This is not well documented anywhere. The manual makes it look like it
should cause the second transaction to wait. The manual says that
isc_tpb_wait "specifies that the transaction should wait until locked
resources are released.". The trick in this is to explain what "locked
resources" are. If you come from a background of databases that use locking
instead of the multi-generational method, this is just what I would expect.
The one update locks, the second one waits...

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

Just a clarification here - what do you mean acquiring all the locks before
it begins? Are these locks at a full table level then? If so then thats the
coarse granularity that I don't want to use!

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

Actually what I do is catch the update conflict and then just wait a short
time and retry the isc_dsql_execute. This is better than the rollback I
think and should work properly in all cases. It sort of makes it work how I
wanted it to except that I have to do the loop and retries instead of the
database doing it.

Thanks for your response. It really cleared up a lot for me and I think I've
got it all working how I want now!

--
Brad Pepers
brad@...