May 1, 2003 at 1:41 pm
I've got a stored proc for use by an ASP web app. Its purpose is to return a new ID for use in a number of places. It's an overt sequence rather than using the new IDENTITY value (embedded sequence) of a table with an IDENTITY column (and maybe that would be faster and more secure--please advise). My question is the use of ISOLATION LEVEL. I want to make sure that there is no competing update whenever someone begins the transaction. Is this a good approach, and do I need to reset the isolation level at the end of the proc?
Thanks in advance!
CREATE procedure dbo.usp_global_get_new_global_id
@newglobalid int output,@rows_updated tinyint output
as
declare @update_error int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select @newglobalid = global_id + 1 from dbo.globals
update dbo.globals set global_id = @newglobalid
set @update_error = @@error
set @rows_updated = @@rowcount
if @update_error = 0 and @rows_updated = 1
begin
commit tran
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
return
end
else
begin
rollback tran
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
return
end
May 1, 2003 at 5:53 pm
We discussed this not long ago in another thread and ultimately this was the solution we agreed probably gave the best performance and locked the row long enough itself to prevent duplication.
CREATE procedure dbo.usp_global_get_new_global_id
@newglobalid int output
as
update dbo.globals set @newglobalid = global_id = global_id + 1
go
Fix to your needs but the row will lock so ISOLATION LEVEL should not be needed.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply