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

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.

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.

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

Regards,

Ann


Regards,

Ann
www.ibphoenix.com
We have answers.