3

According to the remarks section of http://msdn.microsoft.com/en-nz/library/ms173763.aspx :

Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed.

and according to http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

Does this mean that if the ISOLATION LEVEL is changed in a stored procedure it carries over to other the next use of a connection? E.g.

var con1 = new SqlConnection("<THE CONNECTION STRING>"); 

// ...call stored procedure altering isolation level with:
// SET TRANSACTION ISOLATION LEVEL SNAPSHOT

con1.Close();

var con2 = new SqlConnection("<THE CONNECTION STRING>"); 
// ... will this connection potentially have the altered isolation level?

EDIT: Is there a simple way to check the isolation level used in a query?

Emil L
  • 20,219
  • 3
  • 44
  • 65

1 Answers1

1

to part A...

NO.

From the docs...

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batc

dbugger
  • 15,868
  • 9
  • 31
  • 33
  • This is good news for me, but is there a simple way to verify it? Also do I understand it correctly that if the isolation level is changed in SQL provided from the c# application (i.e. not in a stored proc.) then the connection's isolation level might change? – Emil L Dec 01 '14 at 03:14
  • If you create the isolation level when you create a connection -- it's yours to keep. But there do appear to be possible leak issues if you aren't careful. The following post covers this and how to check the level. http://stackoverflow.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections – dbugger Dec 01 '14 at 03:49