Jonas Olofsson wrote:

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

I don't know much about SP:s, but would it be possible to create an
updatable SP that in effect ignores the update but does in implicit
update during select?

In effect, when the client app does:
SELECT ID FROM NEXTIDS WHERE TABLENAME = 'xyz'
then the SP would actually do:
START TRANSACTION
UPDATE NEXTIDS SET ID = ID+1 WHERE TABLENAME = 'xyz'
SELECT ID FROM NEXTIODS WHERE TABLENAME = 'xyz'
COMMIT TRANSACTION

And when the client app does:
UPDATE NEXTIDS SET ID = ID+1 WHERE TABLENAME = 'xyz'
then the SP would actually do:
(nothing)

Not sure it solves all problems with legacy apps though - it would
depend of the transaction isolation used there. (?)

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64