Thank you for the very long and detailed response. I'll make comments inline
on things I don't understand...

> Doing it this way you are losing the benefit of the fact that generators
> are outside transaction control. Once a number has been generated, the
> transaction that took it has it, no further ado. That number won't be
> generated again.

Actually I don't want that. Here is the model I'm using in a typical data
entry screen:

1. The existing data is loaded via a select or defaults are given for a new
record. There is no locking done. There is a unique object id for each
record and also a numeric version.

2. The user makes their changes to the data in the screen.

3. The user submits the update. At this point it does an SQL update command
to effect the changes (or an insert to create a new record with an initial
version of zero). The update where clause includes "version = ?" which is
used to check if the version being updated is the same as the original one.
Also the update increases the version number.

4. Right after the update a commit is done. The whole thing should always
be very quick to reduce lock conflicts if another user is editing the same
data.

5. If the update failed then it is one of two things. Either another user
has changed the record in-between the initial select and the update or
another user has deleted the record. This is noticed by the update giving
back a count of zero for the number of rows effected. The user is then
informed of the conflict and they can redo the changes.

I personally find this works very well. It is rare for my users to ever edit
the same data at the same time so this way I'm not holding locks. For the
insert case, the first thing it does is get an id from the id_table in the
way I've mentioned. This is what is causing the deadlock errors but I still
don't understand why...

> When you have multiple users potentially all trying to update the same row
> in what's (effectively) a generator table, you are stuck with transaction
> control to prevent overwriting. You're certain to get as many lock
> conflicts as you have users accessing inserting to any single table,
> because one transaction already has an uncommitted update pending when
> another wants to do the same thing to it. Whatever you do is going to be
> "best of a bad job" and this isn't a nice way to generate unique
> ids. (This isn't just in IB, it's in any database. In dbs without
> transaction control and pessimistic locking, you are going to get
> overwrites and key violations instead.)

I want it under transaction control. If something in referential integrity
fails or there is some other error part way through, I want the rollback to
give the object_id out to the next user instead of reserving it. Since the
whole transaction takes place in an extremely short period of time (just an
insert or update or two and always based on a primary key so very fast), I'm
not worried about lock conflicts. I just want the second user that tries to
get an object_id to wait until the current transaction is committed.

> You want to do
>
> update id_table set next_id = next_id + 1 where table_name = ?
> select next_id from id_table where table_name = ?
> commit

This is what I am doing.

> Now, this won't work unless it's in a stored procedure, I guess you realise
> that. You can pass only one dynamic SQL statement at a time. And here is
> your trap with DSQL:

I'm not sure I understand you here. I create a new transaction using
isc_start_transaction and then isc_prepare and isc_execute the update and
then the select. I then normally go on and do an insert and then finally a
isc_commit_transaction. I want the update on the id_table during this
transaction to lock the data so that other programs that are doing the same
operation will wait at that point until the transaction is committed (or
rolled back).

> Statement 1:
> update id_table set next_id = next_id + 1 where table_name = ?
> commit
>
> Statement 2:
> select next_id from id_table where table_name = ?
> commit
>
> How are you going to know whether Statement 2 will return the value as
> updated by Statement 1?

I don't call isc_commit_transaction until after the whole thing so I should
be able to assume this. I do the update first to make sure there is a write
lock that will make others wait on the lock before their update goes through.

> Assuming your id_table has a structure something like this:
>
> id_table
> table_name varchar(31)
> next_id numeric(18,0)

Yes thats pretty much it.

> create procedure get_table_id(the_table varchar(31))
> returns (the_id numeric(18,0))
> as
> begin
> update id_table set table_name = table_name
> where table_name = :the_table; /* try to get an exclusive lock */
> select next_id from id_table
> where table_name = :the_table
> into :the_id;
> the_id = :the_id + 1;
> update id_table set next_id = :the_id
> where table_name = :the_table;
> SUSPEND;
> end
>
> All your application has to do is
> select the_id from get_table_id('fish_species')
>
> although if you want this to be portable to a PL that doesn't support this
> syntax, you'll be best using a more standard means to get back the return
> value.

I'm trying to stay away from things like procedures and generators since I
find them to not be portable. I need to support a full range of databases
(Interbase, Sybase, PostgreSQL, DB2, Oracle, ...) and its a real headache to
manage these when using features that don't port. For example in Sybase to
get a unique id you set the column as auto_inc and then *after* the insert
you do a separate select on @@id. The code I've mentioned here for unique
ids works on all the other databases and functions just how I want to except
on Interbase.

> To reduce the length of time this row is blocked for other users, make the
> call to this procedure the only one in its own transaction context and use
> the fastest possible way to have it commit, e.g. in Delphi set Autocommit
> true.
>
> Use SNAPSHOT isolation, use WAIT for conflict resolution and have your
> client handle ANY exception by doing ROLLBACK, i.e. don't provide any
> fallback for fixing errors until **after** the transaction is rolled back.

I'm not sure what SNAPSHOT isolation is. Remember I'm using the low level
API of isc_* calls. I didn't see anything about SNAPSHOT in the API manual.

> Caution: DON'T use SNAPSHOT TABLE STABILITY because that will lock out all
> users.
>
> If there is no exception ( i.e. a table_name value is found to match the
> input argument) the procedure will wait until it can get an exclusive lock
> by being allowed to do the dummy update on the table_name column. Once it
> has this lock, there is no possibility that another transaction will read
> the same initial value for next_id. It will hold the lock (thus excluding
> other transactions from seeing this row) until your app commits the
> procedure.
>
> With WAIT true, you won't get any lock resolution message if the procedure
> fails to get a lock - it will just wait indefinitely until a lock is
> available.

Well thats what I want. But how to get it with the API I'm using. I want
the initial update to get the lock and for all other accesses to wait until
the transaction is committed or rolled back exactly as you have said. But I
instead get these deadlock errors!

> If you have a lot of users inserting, you might want to put a timer in
> there somewhere, set a timeout limit and have the transaction roll back if
> it's not committed in some user-tolerable period. Deliver some message
> like "Request timed out after n seconds" which at least will reduce the
> queue and alert someone that something is holding things up. It'll also
> help QA when the design review comes up and it will be amusing to otherwise
> frustrated users to run sweepstakes on how many timeouts each workstation
> will get today.

The design I have guarentees that no data written takes more than a fraction
of a second so I don't normally have to worry about this. If two users
happen to click on "Save" at exactly the same time, one will get the lock and
do its update first, the other will wait on the lock, the first will complete
in a fraction of a second, and then the second will go.

> There are other strategies, e.g. use NOWAIT for conflict resolution and put
> a retry handler in your client app. I guess it gets down to local demand
> and what the users will tolerate.

I think the database should handle this and not have me in some loops
checking for stuff like this and from what you've said, it sounds like it
should work - but it doesn't!

> Where this could get really horrible is if you have multiple users doing
> batch inserts or updates simultaneously. Then you'll *really* start to
> wonder how important it is to have a vendor-independent id generator.

Not a problem in the model I'm using.

Thanks again for all your help and I hope with a bit more help I can resolve
this! I'm going to write a simple test program so I can show you exactly
what I'm doing.

--
Brad Pepers
brad@...