none
Problem with SET TRANSACTION ISOLATION LEVEL

    Question

  • I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

    1. What if I want to change the isolation level of the whole database? Which command is it ?

    2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

    for ex. this is from a stored procedure

    /* Blah Blah Blah Blah the T-SQL before the transaction */

    BEGIN TRANSACTION myTran ;   /*I want IsolationLevel = Serializable being used here*/

    /* Do something Blah Blah Blah in this transaction */

    COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

    /* Blah Blah Blah Blah the T-SQL after the transaction */

    Thanks every body!!!

    Pi

    Friday, September 01, 2006 1:27 AM

Answers

  • Hi Pi.

    Answers:

    1.  No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

    2.  To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

    /* Blah Blah Blah Blah the T-SQL before the transaction */

    -- ADD THIS HERE

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION myTran ;   /*I want IsolationLevel = Serializable being used here*/

    /* Do something Blah Blah Blah in this transaction */

    COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

    -- AND THIS HERE

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    /* Blah Blah Blah Blah the T-SQL after the transaction */

     

    Hope that helps,

    Friday, September 01, 2006 2:08 AM
  • In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

    There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

    Thanks!

     

     

    Friday, January 26, 2007 3:29 AM

All replies

  • Hi Pi.

    Answers:

    1.  No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

    2.  To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

    /* Blah Blah Blah Blah the T-SQL before the transaction */

    -- ADD THIS HERE

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION myTran ;   /*I want IsolationLevel = Serializable being used here*/

    /* Do something Blah Blah Blah in this transaction */

    COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

    -- AND THIS HERE

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    /* Blah Blah Blah Blah the T-SQL after the transaction */

     

    Hope that helps,

    Friday, September 01, 2006 2:08 AM
  • Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

    Monday, September 11, 2006 7:26 PM
  • In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

    There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

    Thanks!

     

     

    Friday, January 26, 2007 3:29 AM
  • If the database has not been set to

     

    ALTER DATABASE AdventureWorks

    SET READ_COMMITTED_SNAPSHOT ON

     

    Then you cannot do a single statement

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    SELECT *

    FROM Person.StateProvince

     

    Msg 3952, Level 16, State 1, Line 2

    Snapshot isolation transaction failed accessing database 'AdventureWorks' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

     

    Is there a way to do this without setting entire database to this mode?

     

    What different with READ_COMMITTED_SNAPSHOT and ALLOW_SNAPHOT_ISOLATION.

     

    Sunday, June 08, 2008 1:49 PM