Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike7kaplan
Starting Member
1 Post |
 Posted - 2009-12-16 : 11:11:07
|
| I have a simple application written in C#.NET 2.0 that sends the following command to SQL Server 2005:SET TRANSACTION ISOLATION LEVEL SERIALIZABLEThen 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. |
|
|
saralstalin
Starting Member
11 Posts |
Posted - 2009-12-17 : 00:22:38
|
| Dear Mike,TRANSACTION ISOLATION LEVEL is a connection property. Management Studio has one connection per command window. Since you are using the same connection for changing the ISOLATION LEVEL and checking USEROPTIONS (using same command window) changes are visible.If you SET TRANSACTION ISOLATION LEVEL and execute DBCC USEROPTIONS over same connection (say in a query batch) you can see the same in C# alsoSaral S Stalin |
 |
|
|
|
|
|
Set Transaction Isolation Level has no effect