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.
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.