with tutorial the español applications sql sql-server-2008 transactions deadlock isolation-level

sql - tutorial - the django project



¿Por qué se produce un punto muerto? (4)

Uso una pequeña transacción que consiste en dos consultas simples: seleccionar y actualizar:

SELECT * FROM XYZ WHERE ABC = DEF

y

UPDATE XYZ SET ABC = 123 WHERE ABC = DEF

A menudo ocurre cuando la transacción se inicia con dos subprocesos y, dependiendo del nivel de aislamiento, se produce un interbloqueo (RepeatableRead, Serialization). Ambas transacciones intentan leer y actualizar exactamente la misma fila. Me pregunto por qué está sucediendo. ¿Cuál es el orden de las consultas que conduce a un punto muerto? He leído un poco sobre el bloqueo (compartido, exclusivo) y cuánto tiempo duran los bloqueos para cada nivel de aislamiento, pero todavía no entiendo completamente ...

Incluso preparé una prueba simple que siempre resulta en un punto muerto. Miré los resultados de la prueba en SSMS y SQL Server Profiler. Comencé la primera consulta y luego inmediatamente la segunda.

Primera consulta:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT ... WAITFOR DELAY ''00:00:04'' UPDATE ... COMMIT

Segunda consulta:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT ... UPDATE ... COMMIT

Ahora no puedo mostrarte registros detallados, pero se parece mucho menos a esto (muy probablemente me haya perdido Lock: deadlock etc. en algún lugar):

(1) SQL:BatchStarting: First query (2) SQL:BatchStarting: Second query (3) Lock:timeout for second query (4) Lock:timeout for first query (5) Deadlock graph

Si entiendo bien los bloqueos, en (1) la primera consulta toma un bloqueo compartido (para ejecutar SELECCIONAR), luego va a dormir y mantiene el bloqueo compartido hasta el final de la transacción. En (2) la segunda consulta también toma bloqueo compartido (SELECCIONAR) pero no puede tomar el bloqueo exclusivo (ACTUALIZAR) mientras hay bloqueos compartidos en la misma fila, lo que resulta en Bloquear: tiempo de espera. Pero no puedo explicar por qué se produce el tiempo de espera para la segunda consulta. Probablemente no entiendo todo el proceso bien. ¿Alguien puede dar una buena explicación?

No he notado los puntos muertos usando ReadCommitted pero me temo que pueden ocurrir. ¿Qué solución recomiendas?

"Pero no puedo explicar por qué se produce el tiempo de espera para la segunda consulta".

Porque la primera consulta tiene bloqueo compartido. Luego, la actualización en la primera consulta también intenta obtener el bloqueo exclusivo, lo que lo hace dormir. Entonces, la primera y la segunda consulta se quedan dormidas esperando que la otra se despierte, y este es un punto muerto que da como resultado el tiempo de espera :-)

En mysql funciona mejor: el interbloqueo se detecta de inmediato y una de las transacciones se retrotrae (no es necesario esperar el tiempo de espera :-)).

Además, en mysql, puede hacer lo siguiente para evitar un punto muerto :

select ... for update

que pondrá un bloqueo de escritura (es decir, bloqueo exclusivo) justo desde el comienzo de la transacción, y de esta manera se evita la situación de punto muerto. Tal vez puedas hacer algo similar en tu motor de base de datos.

Ha pasado mucho tiempo desde la última vez que resolví esto, pero creo que la declaración select crea un bloqueo de lectura, que solo evita que se cambien los datos, por lo que múltiples consultas pueden contener y compartir un bloqueo de lectura en el mismo datos. El bloqueo de lectura compartida es para la coherencia de lectura, es decir, si varias veces en su transacción lee la misma fila, la coherencia de lectura debería significar que siempre debe obtener el mismo resultado.

La instrucción de actualización requiere un bloqueo exclusivo y, por lo tanto, la instrucción de actualización debe esperar a que se libere el bloqueo de lectura.

Ninguna de las dos transacciones liberará los bloqueos, por lo que las transacciones fallan.

Diferentes implementaciones de bases de datos tienen diferentes estrategias para lidiar con esto, con servidores Sybase y MS-SQL que usan escalamiento de bloqueo con tiempo de espera (escalar de bloqueo de lectura a escritura) - Creo que Oracle (en algún punto) implementó la coherencia de lectura a través del uso del roll-back-log, donde MySQL aún tiene una estrategia diferente.

Para MSSQL hay un mecanismo para evitar interbloqueos. Lo que necesitas aquí se llama la pista de WITH NOLOCK .

En el 99,99% de los casos de declaraciones SELECT , se puede usar y no es necesario agrupar el SELECT con la ACTUALIZACIÓN. Tampoco es necesario colocar un SELECT en una transacción. La única excepción es cuando las lecturas sucias no están permitidas.

Cambiar sus consultas a este formulario resolvería todos sus problemas:

SELECT ... FROM yourtable WITH (NOLOCK) WHERE ... SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION UPDATE ... COMMIT