evitar - Zero SQL deadlock por diseño-¿Algún patrón de codificación?

como evitar los deadlock en sql server

Question

Estoy encontrando bloqueos de SQL muy infrecuentes pero molestos en una aplicación web .NET 2.0 que se ejecuta en MS SQL Server 2005. En el pasado, hemos estado lidiando con los bloqueos de SQL de una manera muy empírica, básicamente ajustando las consultas hasta que funcionen.

Sin embargo, encontré este enfoque muy insatisfactorio: lento y poco confiable. Preferiría seguir patrones de consulta deterministas que garantizarían por diseño que nunca se encontrará un punto muerto SQL.

Por ejemplo, en la programación multiproceso de C #, una regla de diseño simple como las cerraduras debe tomarse siguiendo su orden lexicográfico para garantizar que nunca se produzca un punto muerto.

¿Hay algún patrón de codificación SQL garantizado que sea a prueba de interbloqueos?

Answer 1

Además de la secuencia consistente de adquisición de bloqueo, otra ruta es el uso explícito de las sugerencias de bloqueo y aislamiento para reducir el tiempo / los recursos que se pierden al adquirir involuntariamente bloqueos como el intento compartido durante la lectura.

Answer 2

Algo que ninguno ha mencionado (sorprendentemente) es que, en lo que respecta al servidor SQL, muchos problemas de bloqueo se pueden eliminar con el conjunto correcto de índices de cobertura para la carga de trabajo de consulta de un DB. ¿Por qué? Debido a que puede reducir en gran medida el número de búsquedas de marcadores en el índice agrupado de una tabla (suponiendo que no sea un montón), lo que reduce la contención y el bloqueo.

Answer 3

Como dijiste, acceder siempre a las tablas en el mismo orden es una muy buena manera de evitar interbloqueos. Además, acorta tus transacciones tanto como sea posible.

Otro truco genial es combinar 2 declaraciones sql en una siempre que puedas. Las declaraciones individuales son siempre transaccionales. Por ejemplo, use "ACTUALIZAR ... SELECCIONAR" o "INSERTAR ... SELECCIONAR", use "@@ ERROR" y "@@ ROWCOUNT" en lugar de "SELECCIONAR CUENTA" o "SI (EXISTE ...)"

Por último, asegúrese de que su código de llamada pueda manejar interbloqueos reenviando la consulta una cantidad configurable de veces. A veces sucede, es un comportamiento normal y tu aplicación debe ser capaz de manejarlo.

Answer 4

Creo que el siguiente patrón útil de lectura / escritura es una prueba de bloqueo muerto dadas algunas limitaciones:

Restricciones:

1. Una mesa
2. Se usa un índice o PK para lectura / escritura para que el motor no recurra a bloqueos de tabla.
3. Un lote de registros se puede leer utilizando una única cláusula SQL donde.
4. Usar la terminología de SQL Server.

Escribir ciclo:

1. Todo se escribe dentro de una sola transacción "Read Committed".
2. La primera actualización en la transacción es a un registro específico, siempre presente dentro de cada grupo de actualización.
3. Múltiples registros pueden escribirse en cualquier orden. (Están "protegidos" por la escritura en el primer registro).

Leer Ciclo:

1. El nivel de transacción confirmada de lectura predeterminada
2. Sin transacción
3. Leer registros como una sola declaración de selección.

Beneficios:

1. Los ciclos de escritura secundarios se bloquean en la escritura del primer registro hasta que la primera transacción de escritura se completa por completo.
2. Las lecturas se bloquean / ponen en cola / se ejecutan atómicamente entre las confirmaciones de escritura.
3. Lograr la coherencia del nivel de transacción sin recurrir a "Serializable".

Necesito que esto también funcione así que por favor comenten / corrijan !!

Answer 5

Escribir código a prueba de bloqueos es realmente difícil. Incluso cuando accede a las tablas en el mismo orden, aún puede obtener bloqueos [1]. Escribí una publicación en mi blog que elabora a través de algunos enfoques que te ayudarán a evitar y resolver situaciones de estancamiento.

Si desea asegurarse de que dos declaraciones / transacciones nunca se estancarán, puede lograrlo observando qué bloqueos consume cada instrucción utilizando el procedimiento almacenado del sistema sp_lock . Para hacer esto, debes ser muy rápido o usar una transacción abierta con una pista de bloqueo.

Notas:

1. Cualquier instrucción SELECT que necesite más de un bloqueo a la vez puede estancar una transacción inteligentemente diseñada que agarra los bloqueos en orden inverso.

Answer 6

La respuesta rápida es no, no hay una técnica garantizada.

No veo cómo se puede hacer una prueba de interbloqueo de aplicaciones en general como un principio de diseño si tiene un rendimiento no trivial. Si bloquea de manera preventiva todos los recursos que podría necesitar en un proceso en el mismo orden, incluso si no los necesita, corre el riesgo de tener un problema más costoso cuando el segundo proceso está esperando adquirir el primer bloqueo que necesita. y su disponibilidad se ve afectada. Y a medida que crece la cantidad de recursos en su sistema, incluso procesos triviales tienen que bloquearlos todos en el mismo orden para evitar interbloqueos.

La mejor forma de resolver problemas de bloqueo de SQL, como la mayoría de los problemas de rendimiento y disponibilidad, es observar la carga de trabajo en el generador de perfiles y comprender el comportamiento.

Answer 7

No es una respuesta directa a su pregunta, sino algo para pensar:

http://en.wikipedia.org/wiki/Dining_philosophers_problem

El "Problema de los filósofos del comedor" es un viejo experimento mental para examinar el problema del punto muerto. Leer sobre esto puede ayudarlo a encontrar una solución a su circunstancia particular.

Answer 8

No existe una solución mágica de propósito general para este problema que funcione en la práctica. Puede enviar concurrencia a la aplicación, pero esto puede ser muy complejo, especialmente si necesita coordinar con otros programas que se ejecutan en espacios de memoria separados.

Respuestas generales para reducir las oportunidades de interbloqueo:

1. Optimización de consulta básica (uso de índice apropiado) diseño de zona de influencia del usuario, realizar transacciones durante el menor tiempo posible ... etc.

2. Cuando sea posible, establezca tiempos de espera de consulta razonables para que, en caso de que se produzca un punto muerto, se libere automáticamente una vez que expire el tiempo de espera.

3. Los bloqueos en MSSQL a menudo se deben a su modelo de simultaneidad de lectura predeterminado, por lo que es muy importante no depender de él. Asuma el MVCC de estilo Oracle en todos los diseños. Utilice el aislamiento de instantáneas o, si es posible, el nivel de aislamiento READ UNCOMMITED.

Answer 9

Si tiene suficiente control de diseño sobre su aplicación, restrinja sus actualizaciones / inserciones a procedimientos almacenados específicos y elimine los privilegios de actualización / inserción de los roles de la base de datos utilizados por la aplicación (solo permita actualizaciones explícitamente a través de esos procedimientos almacenados).

Aísle las conexiones de su base de datos a una clase específica en su aplicación (todas las conexiones deben provenir de esta clase) y especifique que las conexiones "solo consultas" establecen el nivel de aislamiento en "lectura sucia" ... el equivalente a (nolock) en cada combinación .

De esta manera, aislará las actividades que pueden causar bloqueos (a procedimientos almacenados específicos) y sacará "lecturas simples" del "bucle de bloqueo".

Answer 10

Zero deadlocks es básicamente un problema increíblemente costoso en el caso general porque debe conocer todas las tablas / obj que va a leer y modificar para cada transacción en ejecución (esto incluye SELECT). La filosofía general se llama bloqueo de dos fases estricto ordenado (no debe confundirse con la confirmación en dos fases) ( http://en.wikipedia.org/wiki/Two_phase_locking ; incluso 2PL no garantiza que no haya puntos muertos)

Muy pocos DBMS en realidad implementan un estricto 2PL debido al golpe de rendimiento masivo que causa (no hay almuerzos gratuitos) mientras que todas sus transacciones aguardan incluso para que se ejecuten sentencias SELECT simples.

De todos modos, si esto es algo que realmente le interesa, eche un vistazo a SET ISOLATION LEVEL en SQL Server. Puedes modificar eso según sea necesario. http://en.wikipedia.org/wiki/Isolation_level

Para obtener más información, consulte wikipedia en Serializability: http://en.wikipedia.org/wiki/Serializability

Dicho eso, una gran analogía es como las revisiones del código fuente: check in early y often. Mantenga sus transacciones pequeñas (en # de instrucciones SQL, # de filas modificadas) y rápidas (el tiempo del reloj de pared ayuda a evitar colisiones con otras personas). Puede ser agradable y ordenado hacer MUCHAS cosas en una sola transacción, y en general, estoy de acuerdo con esa filosofía, pero si estás atravesando muchas dificultades, puedes dividir la transacción en otras más pequeñas y luego verifique su estado en la aplicación a medida que avanza. TRAN 1 - ¿OK Y / N? Si es Y, envíe TRAN 2 - ¿OK S / N? etcétera etcétera

Como un aparte, en mis muchos años de ser un DBA y también un desarrollador (de aplicaciones de bases de datos multiusuario que miden miles de usuarios concurrentes) nunca encontré que los bloqueos sean un problema tan grande que necesitaba conocimiento especial de él (o para cambiar el aislamiento niveles quiera o no, etc.).