SqlConnection behavior when changing isolation level in SQL
As per the notes section http://msdn.microsoft.com/en-nz/library/ms173763.aspx :
Only one of the isolation level parameters 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 a parameter is ISOLATION LEVEL
changed in a stored procedure it will carry over to another next use of the connection? For example.
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 an easy way to check the isolation level used in the request?
source to share
for part A ...
NOT.
From the docs ...
If you issue a SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger when an object returns control, the isolation level is reset to the level in effect when the object was called. For example, if you install REPEATABLE READ in batch mode and then the batch is called by a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting falls back to REPEATABLE READ when the stored procedure returns to batc
source to share