Transacciones: IsolationLevel.Serializable y timeouts

[Carlos Admirador]

Hola a todos.

En un proceso de carga de fichero CSV en Sql Server, con  unos 3.000 registros y utilizando TransactionScope, aparecen errores de TransactionException y TimeoutException.

Error ProcesoLeerCSV. La operación no es válida para el estado de la transacción.

Type: System.Transactions.TransactionException

InnerException: Tiempo de espera de la transacción

InnerException Type: System.TimeoutException

Error Descarga. Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Type: System.Data.SqlClient.SqlException

InnerException: Tiempo de espera de la operación de espera agotado

InnerException Type: System.ComponentModel.Win32Exception

 El nivel de aislamiento es Serializable

IsolationLevel.Serializable  ' Valor por defecto

Parte del código:

        Dim ts As TimeSpan = New TimeSpan(0, 30, 0)

        Dim transOp As New System.Transactions.TransactionOptions

        transOp.Timeout = ts

        '''''''''''''transOp.IsolationLevel = IsolationLevel.Serializable  ' Valor por defecto

        Using tran As New System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, transOp)

            'Register for the transaction completed event for the current transaction

            AddHandler Transactions.Transaction.Current.TransactionCompleted, AddressOf Current_TransactionCompleted

            'Transactions.Transaction.Current.TransactionCompleted += New Transactions.TransactionCompletedEventHandler(Current_TransactionCompleted)

            Dim swTimer As New Stopwatch()

            swTimer.Start()

            Try

                DoProcesoTratarLineas(arrText, fechaCargaYYYYMMDD, nombreFicheroCarga)

                Trace.WriteLine("ProcesoTratarLineas. TRANSACCION. Complete ")

                tran.Complete()

            Catch ex As Exception

                Dim ts2 As System.Transactions.Transaction = System.Transactions.Transaction.Current

                'current.TransactionInformation.internalTransaction

                '//either 1 of following lines will use

                '''''''System.Transactions.Transaction.Current.Rollback()

                'scope.Dispose();

                Throw ex

            End Try

            swTimer.Stop()

            Trace.WriteLine("TOTAL TIEMPO ProcesoTratarLineas : " & swTimer.Elapsed.ToString())

        End Using

Alguna recomendación al respecto?

Saludos.

[Kiquenet]

Es un tema muy amplio y complejo.

En tu caso, se solucionaría con otro nivel de aislamiento menos restrictivo que Serializable.
Cuanto menos restrictivo, permite más concurrencia, pero también hay menos consistencia en los datos.

Escalabilidad (Concurrencia) vs Consistencia

Conceptos:

los niveles de aislamiento de transacciones que implementa SQL Server
sugerencias para nivel de aislamiento y granularidad de bloqueo

En general los deadlock se producen por:

1. Errores de diseño, sea en procesos T-SQL batch, procedures, triggers, gestión de transacciones o modelo ER. Si este es el caso, tendrás deadlocks muy seguido, y como ya comentaron, deberás identificar que lo genera, el SQL Server Profiler puede ser muy útil, más info en http://msdn.microsoft.com/en-us/library/ms188246.aspx y http://msdn.microsoft.com/en-us/library/ms178104.aspx

2. Concurrencia, es muy poco probable que sea por una alta concurrencia de usuarios, pero posible. Si este es el caso, rara vez tendrás un deadlock, podrías analizar el problema, pero teniendo en cuenta que el motor resuelve los deadlock con consistencia, puede ser mas práctico ignorar el error y volver a ejecutar el proceso. ”

Hacen falta guías buenas de “troubleshooting” como https://es.slideshare.net/SolidQ/troubleshooting-de-bloqueos-2016

http://blog.koalite.com/2018/04/lo-minimo-a-entender-sobre-transacciones/

https://kiquenet.wordpress.com/tag/transacciones/

[Kiquenet]

Un error típico:

La transacción (id. de proceso 105) quedó en interbloqueo en bloqueo recursos con otro proceso y fue elegida como sujeto del interbloqueo. Ejecute de nuevo la transacción.

SELECT * FROM sys.messages WHERE text LIKE '%deadlock%' AND language_id=1033

Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Puede haber diferentes técnicas para identificar los bloqueos:

https://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/

Pueden ser útiles estas consultas:

  SELECT @@SPID

  

  sp_lock

  sp_lock 88

  sp_who 

  sp_who2 88

  sp_who active

  select * from sys.dm_tran_locks

Podemos considerar ciertos códigos de error de Sql (SqlException.Number) como "transient"

 public static int[] TemporarySqlErrors = new[] {

                        601, // Could not continue scan with NOLOCK due to data movement.

847, // Timeout occurred while waiting for latch: class '%ls', id %p, type %d, Task 0x%p : %d, waittime %d, flags 0x%I64x, owning task 0x%p. Continuing to wait.

1205, // Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

1206, // The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

1220, // No more lock classes available from transaction.

1222, // Lock request time out period exceeded.

1421, // Communications to the remote server instance '%.*ls' failed to complete before its timeout. The ALTER DATABASE command may have not completed. Retry the command.

1807, // Could not obtain exclusive lock on database '%.*ls'. Retry the operation later.

3928, // The marked transaction '%.*ls' failed. A Deadlock was encountered while attempting to place the mark in the log.

5030, // The database could not be exclusively locked to perform the operation.

5061, // ALTER DATABASE failed because a lock could not be placed on database '%.*ls'. Try again later.

7604, // Full-text operation failed due to a time out.

8628, // A time out occurred while waiting to optimize the query. Rerun the query.

8645, // A time out occurred while waiting for memory resources to execute the query. Rerun the query.

14355, // The MSSQLServerADHelper service is busy. Retry this operation later.

17197, // Login failed due to timeout; the connection has been closed. This error may indicate heavy server load. Reduce the load on the server and retry login.%.*ls

17830, // Network error code 0x%x occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total %d ms, enqueued %d ms, network writes %d ms, network r

17889, // A new connection was rejected because the maximum number of connections on session ID %d has been reached. Close an existing connection on this session and retry.%.*ls

18486, // Login failed for user '%.*ls' because the account is currently locked out. The system administrator can unlock it. %.*ls

};

Otros indican otros códigos como:

SqlConnectionBroken = -1,

SqlTimeout = -2,

SqlOutOfMemory = 701,

SqlOutOfLocks = 1204,

SqlDeadlockVictim = 1205,

SqlLockRequestTimeout = 1222,

SqlTimeoutWaitingForMemoryResource = 8645,

SqlLowMemoryCondition = 8651,

SqlWordbreakerTimeout = 30053

Se puede utilizar Polly o un código custom para reintentar según los códigos de error que consideremos:

var policy = Policy

  .Handle<SqlException(e => e.Number == 1205 || e.Number == 1204 ...) // Handling deadlock victim

  .Or<OtherException>()

  .Retry(3, (exception, retyCount, context) =>

  {

    // Log...

  });










public static T Retry<T>(Func<T> func) { int count = 3; TimeSpan delay = TimeSpan.FromSeconds(5); while (true) { try { return func(); } catch (SqlException ex) { --count; if (count <= 0) throw; var dbex = new DatabaseException(ex.Message, ex); if (dbex.ExceptionInformation.MayRetry) _log.Debug("Deadlock, retrying", e); else throw; Thread.Sleep(delay); } } }











La clave es cómo encontrar "qué" está locking-blocking mis tablas.

https://stackoverflow.com/questions/8749426/how-to-find-what-is-locking-my-tables

Saludos.




[Kiquenet]

Los métodos de Retry pueden ser genéricos (utilizando Action, Function) y así ser más elegantes en cuanto a estilo de codificación:

https://stackoverflow.com/questions/4821668/what-is-good-c-sharp-coding-style-for-catching-sqlexception-and-retrying/6569328

Interesante el método de "preservar el StackTrace" en los re-throw de excepciones.

public static void PreserveStackTrace(Exception ex)
{
    MethodInfo preserveStackTrace = typeof(Exception).GetMethod("InternalPreserveStackTrace", BindingFlags.Instance | BindingFlags.NonPublic);
    preserveStackTrace.Invoke(ex, null); 

}

 

[Preguntón Cojonero Cabrón]

En un proceso de carga de fichero CSV en Sql Server, con  unos 1.600 registros y utilizando TransactionScope, aparecen errores de TransactionException y TimeoutException.

 Using tran As New System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, transOp)

Error ProcesoLeerCSV. La operación no es válida para el estado de la transacción.

Type: System.Transactions.TransactionException

InnerException: Tiempo de espera de la transacción

InnerException Type: System.TimeoutException

Error Descarga. Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Type: System.Data.SqlClient.SqlException

InnerException: Tiempo de espera de la operación de espera agotado

InnerException Type: System.ComponentModel.Win32Exception

 El nivel de aislamiento es Serializable

IsolationLevel.Serializable  ' Valor por defecto

Log

Registro 58
COMPROBACION NUMERO DE TELEFONOS. Contador de Gremios: 1
COMPROBACION NUMERO DE TELEFONOS. Num.Telefonos: 4

Insertado TABLA_ARCHIVO_CLIENTE. Indice 1268830
Contador de Gremmios: 1
Inserta en C2_AR_LLAM_CALIDAD_HS
INDICE,MIXUP,PRIORITE,VERSOP,INTERNAL,STATUSGROUP,STATUS,LIB_STATUS,DETAIL,LIB_DETAIL,RAPPEL,TEL,TEL1,TEL2,TEL3,TEL4,PROFIL_RECORD, TZID
1268830,1268830,1,-1,100000,NULL,NULL,NULL,NULL,NULL,'Z999999999999','nnnnnn','nnnnn','nnnnn','nnnn','nnnnn',2,'Romance Standard Time'

AnularLlamadaParaComunicacionSms. TipoComunicacion: 0
AnularLlamadaParaClienteRobinson. Poliza: 184146264                     

*********** TRANSACTION *********** 
A transaction has completed:
c7d18d3a-16be-4a0d-82c8-7ef099562ace:1: ID:             {0}
Distributed ID: 00000000-0000-0000-0000-000000000000
Status:         Aborted
IsolationLevel: ReadCommitted
*********** TRANSACTION *********** 

*********** ERROR TRANSACTION *********** 
ERROR. TRANSACCION. ROLLBACK 
TRANSACCION STATUS: 2
*********** ERROR TRANSACTION *********** 

Error ProcesoLeerCSV. La operación no es válida para el estado de la transacción.

"The operation is not valid for the state of the transaction"

Type: System.Transactions.TransactionException
InnerException: Tiempo de espera de la transacción
InnerException Type: System.TimeoutException

El procesosuele funcionar, da problemas cuando llegan más de 1000 registros en el CSV, algo que ocurre en la BBDD a ciertas hora, y hace que la inserción de registros sea lentísima y acabe fallando la transacción.
Aún partiendoel fichero en más pequeños, sigue dando error, no llega ni insertar 60 registros y da el error antes mencionado.

[Preguntón Cojonero Cabrón]

En otro intento, ya falla nada más prueba a insertar 1 registro:



Registro 1

    COMPROBACION NUMERO DE TELEFONOS. Contador de Gremios: 1
    COMPROBACION NUMERO DE TELEFONOS. Num.Telefonos: 4

Insertado TABLA_ARCHIVO_CLIENTE. Indice 1268831

     Contador de Gremmios: 1
     Inserta en C2_AR_LLAM_CALIDAD_HS
        INDICE,MIXUP,PRIORITE,VERSOP,INTERNAL,STATUSGROUP,STATUS,LIB_STATUS,DETAIL,LIB_DETAIL,RAPPEL,TEL,TEL1,TEL2,TEL3,TEL4,PROFIL_RECORD, TZID

        1268831,1268831,1,-1,100000,NULL,NULL,NULL,NULL,NULL,'Z999999999999','nnnn','nnnn','nnnn','nnnn','nnnn',2,'Romance Standard Time'

     AnularLlamadaParaComunicacionSms. TipoComunicacion: 0
     AnularLlamadaParaClienteRobinson. Poliza: 909508001                    

*********** ERROR TRANSACTION ***********
ERROR. TRANSACCION. ROLLBACK

TRANSACCION STATUS: 0
*********** ERROR TRANSACTION ***********

*********** TRANSACTION ***********
A transaction has completed:

ae78302e-0dff-4246-878d-f56b40cc7a73:1: ID:             {0}

Distributed ID: 00000000-0000-0000-0000-000000000000
Status:         Aborted
IsolationLevel: ReadCommitted
*********** TRANSACTION ***********

Error ProcesoLeerCSV. Se agotó el tiempo de espera de ejecución. El período de tiempo de espera transcurrió antes de la finalización de la operación o el servidor no responde.