Unanswered: Set Transaction Isolation Level does not work / has no effect
I have a simple application written in C#.NET 2.0 that sends the following command to SQL Server 2005:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Then it uses DBCC USEROPTIONS to obtain the current isolation level. No matter what I do, the isolation level remains at "read committed", which is the default value for SQL Server. By testing the program's behavior, I have verified that indeed the isolation level is not serializable. However, if I use SQL Server Management Studio Express to log in to the same server and database with the same credentials, the same command does have the desired effect. All subsequent transactions conducted using Management Studio are performed according to isolation level serializable. Using SQL Server Profiler, I verified that the server is receiving and processing the same commands and transactions from both processes.
I figured out why the command shown above apparently has no effect when issued from my application. The SQL statement only works for Transact-SQL. In .NET, the programmer modifies the isolation level by passing a value of the enumerated type, IsolationLevel, to the SqlConnection.BeginTransaction method.