Reset ISOLATION LEVEL to it's default for SQL Server >= 2014

Question

I read this topics:

SQL Server 2014 reseting isolation level

How do I unset/reset a transaction isolation level for SQL Server?

KB3025845 - FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014

Entity Framework and Transactionscope doesn't revert the isolation level after dispose of Transactionscope

What does sp_reset_connection do?

And so many posts and blogs...

I have some questions about this topics:

1. I couldn't find a reliable reference from Microsoft sites that explain reset ISOLATION LEVEL in SQL Server 2014 and sp_reset_connection. How can I be sure that this happened and ISOLATION LEVEL reset to it's default value.

2. Does it correct that changing ISOLATION LEVEL in stored procedures, just have scope in the scope of the stored procedure? Doesn't it affect connection?

Thanks

Atp

Why not race your horses and find out, use select transaction_isolation_level from sys.dm_exec_requests where session_id = @@SPIDto get the current isolation level, change it, and then do the sp_reset_connection thing – Fissure

@siggemannen: The problem is you did it yourself. In some posts and topics is mentioned that these operation will do automatically. I want to make sure if it will be done or not. – Atp

It's an old issue, and was fixed then unfixed by MS. One hack is to ensure that the isolation level is set only using parameterized queries. But this is difficult (impossible) if using TransactionScope. Consider using a separate, slightly different connection string in order to get a different connection pool for this query. Alternatively, you can manually set an isolation level per table that you query using FromSqlRaw. Also what happens if you open an explicit database transaction with using var tran = dbContext.Database.BeginTransaction(); – Terrify

sp_reset_connection does not reset the isolation level by design with or without TrasnactionScope . Regarding #2, all SET commands executed in a proc are reverted when the proc exits, including SET TRANSACTION ISOLATION LEVEL.. – Remonstrance

@DanGuzman: Thanks but the like you specified is too old. It is for 2009 but I want SQL Server behavior for version 2014 and above. – Atp

@DanGuzman: Could you please mention a reference for comment #2. Thanks – Atp

SQL 2014 is too old and should urgently upgeadedi – Chromogen

@DanGuzman i wouldn't call it "by design" rather "won't fix". A fix was put in place for 2014, but rolled back because it had unexpected side effects. No further fix has been offered. – Terrify

@Chromogen Thanks but I said >= 2014. – Atp

@Charlieface, I'm aware of the history behind this ugliness. Let's say it's "won't fix by design" :-) – Remonstrance

@Charlieface: I'm confused. Does it reset every time a connection fetch from pool? or not. I saw it's fix: support.microsoft.com/en-au/topic/…. But I didn't see it's rollback. Could you please mention the reference? Thanks – Atp

See SQL Server: Isolation level leaks across pooled connections. So that "fix" you mention *is8 the rollback of the earlier actual fix. And the reason it was done is because TransactionScope calls sp_reset_connection without keeping the transaction. A new field in TDS was added for this, but because older clients hadn't been updated, nothing was changed in the end. – Terrify

@DooDoo, in the SET statement documentation, "If a SET statement runs in a stored procedure or trigger, the value of the SET option gets restored after the stored procedure or trigger returns control". – Remonstrance

See learn.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/… where the RESETCONNECTIONSKIPTRAN bit flag was added for TransactionScope. – Terrify

@DanGuzman Note that if a batch is passed with parameters then the client normally uses sp_executesql RPC calls to do that, so that counts as a separate scope, whereas without parameters most clients just send an Ad-Hoc Batch, which is not. – Terrify

@Charlieface: In this SQL Server: Isolation level leaks across pooled connections is mentioned that: The issue was fixed in the following cumulative updates for SQL Server. Why did you say it wasn't fixed. – Atp

@DooDoo, note that sp_reset_connection does not get executed directly. The client API sets a flag in the TDS request when the first query on a reused pooled connection is executed, This causes the database engine to execute sp_reset_connection internally to restore the session to the state of a new connection, sans isolation level. – Remonstrance

The fix KB3025845 is saying the exact opposite: the transaction gets reset and it shouldn't have been. There originally was no mechanism to reset all settings apart from the transaction status, so TransactionScope used the normal reset. The reset of the isolation level and transaction status meant that TransactionScope stopped working. MS restored the old behaviour, so rolling back the original fix. – Terrify