locked
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED RRS feed

  • Question

  • Can you set the isolation level to  READ UNCOMMITTED for the entire database?

    Alan

    Thursday, September 15, 2016 12:58 AM

Answers

  • See here-

    “How do I set the default transaction isolation level on the current database?”

    You can’t. The default isolation level for all SQL Server databases is Read Committed, and your only option is to set the isolation level within a session, if you want to use a level other than the default. Workarounds have been suggested, such as creating a logon trigger that sets the isolation level, but these approaches tend not to deliver the expected results.

    That said, SQL Server does support several database options that can impact the isolation levels: READ_COMMITTED_SNAPSHOT,ALLOW_SNAPSHOT_ISOLATION, and MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. All of these are disabled by default, but you can easily use T-SQL to turn them on, as shown in the following examples:

    ALTER DATABASE AdventureWorks2014
    SET READ_COMMITTED_SNAPSHOT ON;
     
    ALTER DATABASE AdventureWorks2014
    SET ALLOW_SNAPSHOT_ISOLATION ON;
     
    ALTER DATABASE AdventureWorks2014
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;

    ref- https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

    go through the above link.

    Also,


    SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
    https://msdn.microsoft.com/en-IN/library/ms173763.aspx

    Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.


    Regards, S_NO "_"

    • Marked as answer by anaylor01 Thursday, September 15, 2016 2:38 AM
    Thursday, September 15, 2016 1:16 AM

All replies

  • See here-

    “How do I set the default transaction isolation level on the current database?”

    You can’t. The default isolation level for all SQL Server databases is Read Committed, and your only option is to set the isolation level within a session, if you want to use a level other than the default. Workarounds have been suggested, such as creating a logon trigger that sets the isolation level, but these approaches tend not to deliver the expected results.

    That said, SQL Server does support several database options that can impact the isolation levels: READ_COMMITTED_SNAPSHOT,ALLOW_SNAPSHOT_ISOLATION, and MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. All of these are disabled by default, but you can easily use T-SQL to turn them on, as shown in the following examples:

    ALTER DATABASE AdventureWorks2014
    SET READ_COMMITTED_SNAPSHOT ON;
     
    ALTER DATABASE AdventureWorks2014
    SET ALLOW_SNAPSHOT_ISOLATION ON;
     
    ALTER DATABASE AdventureWorks2014
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;

    ref- https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

    go through the above link.

    Also,


    SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
    https://msdn.microsoft.com/en-IN/library/ms173763.aspx

    Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.


    Regards, S_NO "_"

    • Marked as answer by anaylor01 Thursday, September 15, 2016 2:38 AM
    Thursday, September 15, 2016 1:16 AM
  • Can you set the isolation level to  READ UNCOMMITTED for the entire database?

    Alan


    You cannot set for whole database but you have to set it for each query or connection. I would say this is very very bad idea I would never do that, even if it is somehow possible

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, September 15, 2016 4:04 AM
    Answerer
  • Is it a bad idea to set the transaction isolation level to read uncommitted for an entire database if that database is a read only copy of a database and only used for reporting purposes?

    Stephen Saunders

    Thursday, August 3, 2017 7:08 PM