--- In firebird-support@yahoogroups.com, Tetram Corp <dev@...> wrote:

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

Hi Thierry,

I have thought of trying to use a view instead, but the problem is
that it has to be compatible with legacy applications that most of the
time do:

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

Is it possible to create a view that handles the above and also is
more robust when it comes to concurrent operations?

Cheers

Jonas

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