hi,

couldn't you replace your table by a view which kowns its data from
generator?

Thierry

Jonas Olofsson a écrit :

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