Last week I was involved in a discussion about the default transaction isolation level used by the transaction objects in WCF’s services (Serializable) versus the one used by the transaction objects in Entity Framework for SQL Server (ReadCommitted) whose details I’m not going to cover in this post.

 

At a given point during that conversation, somebody asked whether it would be possible to change the default isolation level chosen by an instance of SQL Server when a new session is created.

And that is the part I consider interesting to divulge with this post, so here I go.

As the documentation of the product has always said, the default transaction isolation level in SQL Server is READ COMMITTED. For example, the topic that covers SET TRANSACTION ISOLATION LEVEL mentions this about the READ COMMITTED option:

”Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.”

Now, what mechanisms people think they have so that every time SQL Server instantiates a new session it gets initialized with a default isolation level other than READ COMMITTED? And here’s where the demythification process begins. Smile

  1. Q: Maybe one of those obscure and undocumented trace flags the product group saves for special occasions?
    A: Unfortunately no. Not this time. Well, to be completely honest with you, there is one TF which, when enabled (and only if you are running a debug build of SQL Server) it defaults to SNAPSHOT isolation level. I want to believe that one was just created to facilitate the stress testing of the SNAPSHOT isolation level when it was included in its first release (SQL Serevr 2005). So, for us mere mortals, this one is just as if it wouldn’t exist at all.
  2. Q: Aha! One of the configuration values one can specify as global defaults for all users through the user options server configuration option must let the DBA specify the default isolation level to override the READ COMMITTED.
    A: Wrong answer again. The default isolation level is not one of the global defaults user options let you modify.
  3. Q: Could it some option the DBA can set at the database level so that the default isolation level will vary, depending on the database on whose context the session is?
    A: And the answer is no, again. The only thing you can change at the database level is that you can enable read committed snapshot option (see the READ_COMMITTED_SNAPSHOT option for ALTER DATABASE). When you do so, DML statements against objects stored in that database start generating row versions even when no transaction uses snapshot isolation. Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.
    Even though DBCC USEROPTIONS could return read committed snapshot as a possible value for the isolation level SET option, the actual isolation level in that case is still read committed. Remember that there’s no such isolation level as read committed snapshot. The documentation for DBCC USEROPTIONS has a specific remark explaining this.
    But what is important to recall from this third point is that there isn’t a database option either with which you can force SQL to use a default transaction isolation level other than READ COMMITTED.
  4. Q: What if I create a logon trigger in which I explicitly call SET TRANSACTION ISOLATION LEVEL and set the new session’s isolation level to that I want as the default?
    A: Two pitfalls with this one: 1) This would only affect sessions created because an external client is connecting to the instance, for all connectionless sessions created by the server itself, that wouldn’t have any effect; and 2) if you think this would work, you have probably never read the Considerations When You Use the SET Statements section in the “SET Statements” topic of SQL Server’s documentation.
    One of the considerations is this “If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger.” That means that the SET options created when the session is instantiated has the default isolation level READ COMMITTED, when the logong trigger gets called, a new copy of the SET options is created and passed to the execution context of the trigger; during the execution of the trigger any SET options modified affect its local copy only, and when the execution of the trigger comes to its completion, the former copy is restored, so the isolation level turns back to READ COMMITTED.
    If you didn’t know SET options worked like this with triggers and stored procedures, it’s time to review the T-SQL code you have written or reviewed in the past, because most probably it wasn’t behaving the way you thought it was.
  5. Q: Since I’m running the Enterprise Edition of SQL Server, I could leverage Resource Governor and change the transaction isolation level of every new connection inside the classifier function.
    A: Same story as with previous point in the sense that it only affects sessions bound to a connection. Plus, you cannot even attempt to do this. The reason being that the type of statements you can use inside a function is somehow limited. And the SET commands are not among the set of permitted operations inside a function. If you try to include them in the definition of the function you get back an error 443 (Invalid use of a side-effecting operator '%s' within a function) upon running the offending CREATE FUNCTION or ALTER FUNCTION.

So, back to the original promising question. How to set the default transaction isolation level server wide?

The answer is: There isn’t a supported way to do so.

If you want/need a particular session to default to a transaction isolation level other than READ COMMITTED, make sure you run SET TRANSACTION ISOLATION LEVEL just after the connection has succeeded.

Notice that different data access layers (OLEDB Provider for SQL Server, ODBC Driver for SQL Server, or .NET’s SqlClient class) offer different mechanisms to set the isolation level programmatically, which end up issuing a SET TRANSACTION ISOLATION LEVEL statement against SQL Server without you having to programmatically execute the SET TRANSACTION ISOLATION LEVEL statement explicitly.