[comp.databases] Generating unique keys in SYBASE. Whats the best method?

rpburry@ncs.dnd.ca (Paul Burry) (03/14/91)

What is the best mechanism for generating unique keys in SYBASE.

Are there any better (faster, more concurrency) methods than:

	begin transaction
		/** update (and lock) the key control table **/
		update	control_table
		set	key = key + 1
		where	row = 1

		/** grab the key value **/
		select	@key=key
		from	control_table
		where	row = 1	
	commit transaction

	/** if the following tranaction is rolled back, then the	**/
	/** unique key created will be unused...			**/
	begin transaction
		/** use the key **/
		insert into the_table
		values (@key, ...)

		...
	commit transaction

forgive the syntax errors.  I hope you get the general idea.  Presumably
it is better to use a different control table for each table requiring
unique keys (to get around the page locking), and presumably splitting
the key generation and usage will get permit more concurency.

Any suggestions or comments??
-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Paul Burry			
Voice: (613)-991-7325		Internet: rpburry@ncs.dnd.ca
Fax:   (613)-991-7323		UUCP:	  ..!{uunet,cunews}!ncs.dnd.ca!rpburry

davidm@uunet.UU.NET (David S. Masterson) (03/16/91)

>>>>> On 14 Mar 91 12:31:56 GMT, rpburry@ncs.dnd.ca (Paul Burry) said:


Paul> What is the best mechanism for generating unique keys in SYBASE.

Paul> Are there any better (faster, more concurrency) methods than:

Paul> 	begin transaction
Paul> 		/** update (and lock) the key control table **/
Paul> 		update	control_table
Paul> 		set	key = key + 1
Paul> 		where	row = 1

Paul> 		/** grab the key value **/
Paul> 		select	@key=key
Paul> 		from	control_table
Paul> 		where	row = 1	
Paul> 	commit transaction

Paul> 	/** if the following tranaction is rolled back, then the	**/
Paul> 	/** unique key created will be unused...			**/
Paul> 	begin transaction
Paul> 		/** use the key **/
Paul> 		insert into the_table
Paul> 		values (@key, ...)

Paul> 		...
Paul> 	commit transaction

I forget how Sybase does this (its been 3 years since I used it), but couldn't
the first transaction be made into an insertion trigger that the second
transaction will trigger?
--
====================================================================
David Masterson					Consilium, Inc.
(415) 691-6311					640 Clyde Ct.
uunet!cimshop!davidm				Mtn. View, CA  94043
====================================================================
"If someone thinks they know what I said, then I didn't say it!"