locked
Q: set transaction isolation level RRS feed

  • Question

  •  

    Hello All,

    I would like to know that if in the procedure i will set like this

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

     

    than is it necessary to

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    at the end of procedure or not. i know it is best practice to undo what ever you have change default property but does it effect any how.

     

    Thanks,

    N P

    Monday, July 28, 2008 2:28 PM

Answers

  •  

    The isolation level inside a proc lives only until the life of a proc call

     

    first it will be committed (2)

    then change it to serializable (4)

    exec the proc (which will be 1)

    check it again and you will see that it is 4, and it will stay 4 for any inline code

     

    Code Snippet

    CREATE PROC USP_TEST

    as

    set transaction isolation level read uncommitted

    select transaction_isolation_level ,session_id from sys.dm_exec_sessions

    WHERE session_id = @@spid --1

    GO

     

    select transaction_isolation_level ,session_id from sys.dm_exec_sessions

    where session_id = @@SPID  --2


    set transaction isolation level serializable 

    EXEC USP_TEST

    select transaction_isolation_level ,session_id from sys.dm_exec_sessions

    where session_id = @@SPID  --4

    go

    drop proc usp_test

     

     

    Denis The SQL Menace

    http://www.lessthandot.com/

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx


    Monday, July 28, 2008 5:50 PM

All replies

  • And one more question that

    from sp_configure i can check that xp_configure is on or off

    SQL Mail XPs is on or off and so on

     

    is there any system table, dmv or procedure to check that what is current isolation level, is set nocount is on or off for current window we are working on.

     

    Thanks,

    N P

    Monday, July 28, 2008 3:09 PM
  • The setting is constant for the connection, so you would need to reset the isolation level if you are making additional stored procedure calls in that connection that should be operating at a different isolation.  Hopefully you would be handling this in a different connection.

     

    To your second question you can query the sys.dm_exec_sessions Dynamic Management View for the isolation level of any connection.  Look for the transaction_isolation_level column, it contains these values:

     

    0 Unspecified

    1 Read Uncommitted

    2 Read Committed

    3 Repeatable

    4 Serializable

    5 Snapshot

    Monday, July 28, 2008 3:47 PM
  • Setting is not constant for the connection while you execute the stored procedure once sp is executed settings are changed to default.

     

    Look this

    CREATE PROC USP_TEST

    as

    set transaction isolation level read uncommitted

    select transaction_isolation_level ,session_id from sys.dm_exec_sessions

    WHERE session_id = @@spid

    GO

    EXEC USP_TEST

    select transaction_isolation_level ,session_id from sys.dm_exec_sessions

    where session_id = @@SPID

    go

    drop proc usp_test

    Monday, July 28, 2008 5:24 PM
  •  

    The isolation level inside a proc lives only until the life of a proc call

     

    first it will be committed (2)

    then change it to serializable (4)

    exec the proc (which will be 1)

    check it again and you will see that it is 4, and it will stay 4 for any inline code

     

    Code Snippet

    CREATE PROC USP_TEST

    as

    set transaction isolation level read uncommitted

    select transaction_isolation_level ,session_id from sys.dm_exec_sessions

    WHERE session_id = @@spid --1

    GO

     

    select transaction_isolation_level ,session_id from sys.dm_exec_sessions

    where session_id = @@SPID  --2


    set transaction isolation level serializable 

    EXEC USP_TEST

    select transaction_isolation_level ,session_id from sys.dm_exec_sessions

    where session_id = @@SPID  --4

    go

    drop proc usp_test

     

     

    Denis The SQL Menace

    http://www.lessthandot.com/

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx


    Monday, July 28, 2008 5:50 PM