sybperl-l Archive

From: Barry Callahan <callahan at UU dot NET>
Subject: Re: next key table
Date: Sep 18 1997 6:58PM

Doug,

I tried to go to the url, time out, i gave up.
Unfortunately sybase.com has been very slow lately 
(last 5 weeks or so?) during peak business hours :(

Anyway, looking at the sql it is the update and select
statements that *must* be within the begin/commit tran.

The insert statement, inserts into a different table,
"data_table".

There are quite a few variations on the unique key
scheme, the one below ONLY works if all client programs
first query & update the "next_key" table prior to
inserting into "data_table".

One note of caution to the insert separate from the tran,
it does make it possible to insert rows into "data_table"
w/non-contiguous keys.  This would not bother me, a key is
for uniqueness, nothing else, but some app's build in
dependencies on it representing insertion order. (not a
good idea.)

Barry

At 10:28 AM 9/18/97 -0700, you wrote:
>
>http://www.sybase.com/products/whitepapers/pkey_wpaper.html
>
>states that :
>
>begin tran 
>
>    declare @next_key
>    update next_key set next_key = next_key + 1
>    select @next_key = next_key + 1 from next_key 
>
>    insert into data_table (key_column, ...) values (@next_key, ...) 
>
>commit tran 
>
>does anyone know if its necessary to have the 'insert' statement
>_within_ the transaction in order to gaurantee uniqueness in the
>keys generated ?
>
>i have code that does the above, but i dont have my insert statements
>within 'begin tran' and 'commit tran' but after it.
>
>thanks !
>
>doug
>
>
>