Isolation Levels

  • 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

  • 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