> I would add this to the UPDATE statement:
> AND ID = <old-id-value>
>
> If this results in 0 records updated, it means someone else updated

the record, this will make sure that concurrent updates know of eachother.

>
> Have you thought of wrapping the getting of IDs into a Stored Procedure
> instead of doing direct updates? This way, you can easily switch to a
> Generator instead of a table. There's no way of knowing a Generator
> is used (like a table trigger), so they'll never be update to date

unless

> you create a trigger on each table that will automatically update your
> NEXTIDs table with the last number inserted.

Hi Martijn,

yes, the thought was to wrap this functionality into a Stored
Procedure to use generators for all new tables and then the existing
next id table for old tables. The big thing is that all legacy
applications use call SELECT and UPDATE from the application withoout
a standard way of doing it and what I can see no thought of
concurrentcy problems.

My line of thought was to implement triggers on the some of the old
tables that updates the generator if a row is inserted with a id
higher than the generator.

Will there be some issues with this if a legacy application gets an id
from the next id table at the same time?

Does it matter if I in my stored procedure calls:

UPDATE NEXTIDS SET ID = ID+1 WHERE TABLENAME = 'xyz'
SELECT ID FROM NEXTIDS WHERE TABLENAME = 'xyz'

or

SELECT ID FROM NEXTIDS WHERE TABLENAME = 'xyz'
UPDATE NEXTIDS SET ID = ID+1 WHERE TABLENAME = 'xyz'

1. Will this lock the row in the database so I will always get an
unique id?
2. Will the row be locked for editing until I commit my transaction
after the select?
3. What happens if another application tries to access the nextid at
the same time, will the get an exception?
4. But another application can still get the same id as I if it does
the SELECT the id first and then update, or?

I do not care if the id is contingous as long as it is unique.

Cheers

Jonas