c# - framework - ¿Cómo gestiona SqlConnection IsolationLevel?

transaction c# entity framework

Question

Este artículo de MSDN establece que:

Un nivel de aislamiento tiene un alcance de conexión, y una vez configurado para una conexión con la instrucción SET TRANSACTION ISOLATION LEVEL, permanece vigente hasta que se cierra la conexión o se establece otro nivel de aislamiento. Cuando se cierra una conexión y se devuelve al grupo, se conserva el nivel de aislamiento de la última instrucción SET TRANSACTION ISOLATION LEVEL. Las conexiones subsiguientes que reutilizan una conexión agrupada usan el nivel de aislamiento que estaba vigente en el momento en que se agrupó la conexión.

La clase SqlConnection no tiene ningún miembro que pueda mantener el nivel de aislamiento. Entonces, ¿cómo sabe una conexión en qué nivel de aislamiento se ejecuta?

La razón por la que pregunto esto es debido al siguiente escenario:

1. Abrí una transacción usando TransactionScope en modo Serializable, di "T1".
2. Abrió una conexión para T1.
3. T1 está terminado / eliminado, la conexión vuelve al grupo de conexiones.
4. Se llamó a otra consulta en la misma conexión (después de obtenerla del grupo de conexiones) y esta consulta se ejecuta en modo serializable.

Problema:

1. ¿Cómo sabe la conexión agrupada qué nivel de aislamiento se le asoció?
2. ¿Cómo revertirlo a otro nivel de transacción?

Resolución:
La razón por la que las conexiones agrupadas están devolviendo el nivel de aislamiento serializable se debe a la siguiente razón:

1. Tienes un grupo de conexiones (digamos CP1)
2. CP1 puede tener 50 conexiones.
3. Usted elige una conexión C1 de CP1 y la ejecuta con Serializable. Esta conexión tiene su nivel de aislamiento establecido ahora. Hagas lo que hagas, esto no se restablecerá (a menos que esta conexión se utilice para ejecutar un código en un nivel de aislamiento diferente).
4. Después de ejecutar la consulta C1 (Serializable) vuelve a CP1.
5. Si los pasos 1-4 se ejecutan nuevamente, entonces la conexión utilizada puede ser otra conexión distinta de C1, digamos C2 o C3. Entonces, eso también tendrá su nivel de aislamiento establecido en Serializable.
6. Entonces, lentamente, Serialzable está configurado para múltiples conexiones en CP1.
7. Cuando ejecuta una consulta en la que no se realiza una configuración de nivel de aislamiento explícito, la conexión elegida desde CP1 decidirá el nivel de aislamiento. Por ejemplo, si una consulta de este tipo solicita una conexión y CP1 usa C1 (Serializable) para ejecutar esta consulta, esta consulta se ejecutará en modo Serializable aunque no la haya establecido explícitamente.

Espero que eso aclare algunas dudas. :)

Answer 1

En SQL Server 2014, el nivel de aislamiento para la conexión agrupada se restablece cuando la conexión se devuelve a la agrupación. Ver esta publicación del foro

"en SQL 2014, para los controladores de cliente con TDS versión 7.3 o superior, el servidor SQL restablecerá el nivel de aislamiento de la transacción al valor predeterminado (lectura confirmada) para las conexiones agrupadas. Para los clientes con una versión de TDS inferior a la 7.3, tendrán el comportamiento anterior cuando se ejecutan contra SQL 2014. "

Actualización 2017-04-22

Desafortunadamente, esto se "descifró" más tarde en SQL Server 2014 CU6 y SQL Server 2014 SP1 CU1 desde que introdujo un error:

REVISIÓN: el nivel de aislamiento de la transacción se restablece incorrectamente cuando se lanza la conexión de SQL Server en SQL Server 2014

"Supongamos que utiliza la clase TransactionScope en el código fuente del lado del cliente de SQL Server y no abre explícitamente la conexión de SQL Server en una transacción. Cuando se libera la conexión de SQL Server, el nivel de aislamiento de la transacción se restablece incorrectamente".

Answer 2

Los niveles de aislamiento se implementan en el DBMS subyacente, por ejemplo, SqlServer. Es probable que la configuración del nivel de aislamiento configure los comandos SQL que establecen el nivel de aislamiento para la conexión.

El DBMS mantiene el nivel de aislamiento mientras la conexión permanece abierta. Debido a que las conexiones se colocan en la piscina, permanece abierta y mantiene la configuración realizada antes.

Cuando juegue con niveles de aislamiento, debe restablecer el nivel de aislamiento al final de cualquier transacción o, mejor aún, configurarlo cuando se solicite una nueva conexión.

Answer 3

No devuelve el nivel de aislamiento al valor original. Un ejemplo de uso de entidades requería una transacción vacía para restablecer el nivel (aunque aparentemente no es necesario confirmarla (no se necesita .Complete ()).

Un intento de cambiar el nivel de iso usando un SP en el servidor DB no funciona. Salida:

Antes: ReadCommitted
Durante: Serializable
Después: Serializable
Después de restablecer por intento de SP: Serializable
Durante el reinicio por XACT: ReadCommitted
Después de restablecer por XACT: ReadCommitted

// using Dbg = System.Diagnostics.Debug;
XactIso.iso isoEntity = new XactIso.iso();
using (isoEntity)
{
Dbg.WriteLine("Before: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
var xactOpts = new TransactionOptions();
xactOpts.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
using (TransactionScope xact = new TransactionScope(TransactionScopeOption.Required, xactOpts))
{
Dbg.WriteLine("During: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
xact.Complete();
}
Dbg.WriteLine("After: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
isoEntity.usp_SetXactIsoLevel("ReadCommitted");
Dbg.WriteLine("After Reset by SP Attempt: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
// failed
var xactOpts2 = new TransactionOptions();
xactOpts2.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
using (TransactionScope xact2 = new TransactionScope(TransactionScopeOption.Required, xactOpts2))
Dbg.WriteLine("During Reset by XACT: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
// works w/o commit
Dbg.WriteLine("After Reset by XACT: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
}

de donde enlace

proc [Common].[usp_GetXactIsoLevel]
as
begin
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
from sys.dm_exec_sessions
where session_id = @@SPID;
end

y (no funcionó):

proc [Common].[usp_SetXactIsoLevel]
@pNewLevel varchar(30)
as
begin
if @pNewLevel = ''ReadUncommitted''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
else if @pNewLevel = ''ReadCommitted''
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
else if @pNewLevel = ''RepeatableRead''
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
else if @pNewLevel = ''Serializable''
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
else if @pNewLevel = ''Snapshot''
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
else
raiserror(''Unrecognized Transaction Isolation Level'', 16, 1);
end 

Answer 4

SqlConnection.BeginTransaction acepta un argumento IsolationLevel y así es como se controla el nivel de aislamiento de las conexiones de SqlClient. Otra opción es usar el System.Transactions genérico y especificar el nivel de aislamiento en TransactionOptions.IsolationLevel pasado al constructor TransactionScope. Tanto en el modelo de programación SqlClient como en System.Transactions, el nivel de aislamiento debe especificarse explícitamente para cada transacción. Si no se especifica, se utilizará el valor predeterminado (Lectura confirmada para SqlClient, serializable para System.Transactions).

Las conexiones agrupadas no se reutilizan a ciegas. Han ocultado miembros internos para rastrear el estado actual como la transacción actual, los resultados pendientes, etc. y el marco puede limpiar una conexión devuelta al grupo. El hecho de que el estado no esté expuesto en el modelo de programación no significa que no esté allí (esto se aplica a cualquier clase de biblioteca, cualquier diseñador de clase puede ocultar un miembro bajo el paraguas internal ).

Y finalmente, cualquier conexión reutilizada desde el grupo invoca sp_reset_connection que es un procedimiento de servidor que limpia el estado de la sesión en el lado del servidor.