SQL Server: perdite di livello di isolamento tra connessioni in pool

Come dimostrato dalle precedenti domande Stack Overflow ( TransactionScope e Connection Pooling e In che modo SqlConnection gestisce IsolationLevel? ), Il livello di isolamento della transazione trabocca attraverso le connessioni in pool con SQL Server e ADO.NET (anche System.Transactions ed EF, perché si basano su ADO.NET).

Ciò significa che la seguente sequenza pericolosa di eventi può verificarsi in qualsiasi applicazione:

  1. Si verifica una richiesta che richiede una transazione esplicita per garantire la coerenza dei dati
  2. Arriva qualsiasi altra richiesta in cui non viene utilizzata una transazione esplicita perché fa solo letture non critiche. Questa richiesta verrà ora eseguita come serializzabile, causando potenzialmente blocchi e deadlock pericolosi

La domanda: qual è il modo migliore per prevenire questo scenario? È davvero necessario utilizzare le transazioni esplicite ovunque ora?

Ecco una replica autosufficiente. Vedrai che la terza query avrà ereditato il livello Serializable dalla seconda query.

class Program { static void Main(string[] args) { RunTest(null); RunTest(IsolationLevel.Serializable); RunTest(null); Console.ReadKey(); } static void RunTest(IsolationLevel? isolationLevel) { using (var tran = isolationLevel == null ? null : new TransactionScope(0, new TransactionOptions() { IsolationLevel = isolationLevel.Value })) using (var conn = new SqlConnection("Data Source=(local); Integrated Security=true; Initial Catalog=master;")) { conn.Open(); var cmd = new SqlCommand(@" select case transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'RepeatableRead' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' end as lvl, @@SPID from sys.dm_exec_sessions where session_id = @@SPID", conn); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine("Isolation Level = " + reader.GetValue(0) + ", SPID = " + reader.GetValue(1)); } } if (tran != null) tran.Complete(); } } } 

Produzione:

 Isolation Level = ReadCommitted, SPID = 51 Isolation Level = Serializable, SPID = 51 Isolation Level = Serializable, SPID = 51 //leaked! 

In SQL Server 2014 questo sembra essere stato risolto. Se si utilizza il protocollo TDS 7.3 o successivo.

In esecuzione su SQL Server versione 12.0.2000.8 l’output è:

 ReadCommitted Serializable ReadCommitted 

Sfortunatamente questo cambiamento non è menzionato in alcuna documentazione come:

  • Comportamenti Modifiche alle funzionalità del motore di database in SQL Server 2014
  • Rottura delle modifiche alle funzionalità del motore di database in SQL Server 2014

Ma il cambiamento è stato documentato su un forum di Microsoft.

Aggiornamento 2017-03-08

Sfortunatamente questo è stato successivamente “non risolto” in CU2 di SQL Server 2014 e SQL Server 2014 SP1 poiché ha introdotto un bug:

FIX: il livello di isolamento della transazione viene reimpostato in modo errato quando la connessione SQL Server viene rilasciata in SQL Server 2014

“Si supponga di utilizzare la class TransactionScope nel codice sorgente lato client di SQL Server e non si apra esplicitamente la connessione di SQL Server in una transazione: quando viene rilasciata la connessione SQL Server, il livello di isolamento della transazione viene reimpostato in modo errato.”

Il pool di connessioni chiama sp_resetconnection prima di riciclare una connessione. Reimpostare il livello di isolamento della transazione non è nell’elenco delle cose che fa sp_resetconnection. Questo spiegherebbe perché le perdite “serializzabili” attraverso le connessioni in pool.

Immagino che tu possa iniziare ogni query assicurandoti che sia al giusto livello di isolamento :

 if not exists ( select * from sys.dm_exec_sessions where session_id = @@SPID and transaction_isolation_level = 2 ) set transaction isolation level read committed 

Un’altra opzione: le connessioni con una stringa di connessione diversa non condividono un pool di connessioni. Quindi, se si utilizza un’altra stringa di connessione per le query “serializzabili”, non condivideranno un pool con le query “read committed”. Un modo semplice per modificare la stringa di connessione è utilizzare un accesso diverso. Puoi anche aggiungere un’opzione casuale come Persist Security Info=False; .

Infine, puoi assicurarti che ogni query “serializzabile” ripristini il livello di isolamento prima che ritorni. Se una query “serializzabile” non riesce a completarsi, è ansible cancellare il pool di connessioni per forzare la connessione contaminata fuori dal pool:

 SqlConnection.ClearPool(yourSqlConnection); 

Questo è potenzialmente costoso, ma le query non riuscite sono rare, quindi non è necessario chiamare spesso ClearPool() .

Ho appena fatto una domanda su questo argomento e ho aggiunto un pezzo di codice C #, che può aiutare a risolvere questo problema (che significa cambiare il livello di isolamento solo per una transazione).

Modificare il livello di isolamento solo nelle singole transazioni ADO.NET

È fondamentalmente una class che deve essere racchiusa in un blocco “using”, che interroga il livello di isolamento originale prima e lo ripristina in un secondo momento.

Tuttavia, richiede due ulteriori round trip al DB per controllare e ripristinare il livello di isolamento predefinito, e non sono assolutamente sicuro che non perderà mai il livello di isolamento alterato, anche se non vedo pericoli.