right - Punto muerto provocado por la instrucción SELECT JOIN con SQL Server

left join sql server

Question

Al ejecutar una instrucción SELECT con un JOIN de dos tablas, SQL Server parece bloquear ambas tablas de la declaración individualmente. Por ejemplo, mediante una consulta como esta:

SELECT ...
FROM
    table1
    LEFT JOIN table2
        ON table1.id = table2.id
    WHERE ...

Descubrí que el orden de los bloqueos depende de la condición WHERE. El optimizador de consultas intenta producir un plan de ejecución que solo lee tantas filas como sea necesario. Entonces, si la condición WHERE contiene una columna de table1, primero obtendrá las filas resultantes de table1 y luego obtendrá las filas correspondientes de table2. Si la columna es de la tabla 2, lo hará al revés. Las condiciones más complejas o el uso de índices también pueden tener un efecto en la decisión del optimizador de consultas.

Cuando los datos leídos por una declaración se deben actualizar más adelante en la transacción con las declaraciones UPDATE, no se garantiza que el orden de las instrucciones UPDATE coincida con el orden que se usó para leer los datos de las 2 tablas. Si otra transacción intenta leer datos mientras una transacción está actualizando las tablas, puede provocar un punto muerto cuando la instrucción SELECT se ejecuta entre las instrucciones UPDATE porque ni SELECT puede obtener el bloqueo en la primera tabla ni UPDATE puede obtener el bloqueo la segunda mesa Por ejemplo:

T1: SELECT ... FROM ... JOIN ...
T1: UPDATE table1 SET ... WHERE id = ?
T2: SELECT ... FROM ... JOIN ... (locks table2, then blocked by lock on table1)
T1: UPDATE table2 SET ... WHERE id = ?

Ambas tablas representan una jerarquía de tipos y siempre se cargan juntas. Entonces tiene sentido cargar un objeto usando SELECT con un JOIN. Cargar ambas tablas individualmente no le daría al optimizador de consultas la oportunidad de encontrar el mejor plan de ejecución. Pero dado que las instrucciones UPDATE solo pueden actualizar una tabla a la vez, esto puede causar interbloqueos cuando se carga un objeto mientras otra transacción lo actualiza. Las actualizaciones de objetos a menudo causan ACTUALIZACIONES en ambas tablas cuando se actualizan las propiedades del objeto que pertenecen a diferentes tipos de la jerarquía de tipos.

He intentado agregar sugerencias de bloqueo a la instrucción SELECT, pero eso no cambia el problema. Simplemente causa el interbloqueo en las instrucciones SELECT cuando ambas instrucciones intentan bloquear las tablas y una instrucción SELECT obtiene el bloqueo en el orden opuesto de la otra instrucción. Tal vez sea posible cargar datos para actualizaciones siempre con la misma instrucción forzando a los bloqueos a estar en el mismo orden. Eso evitaría un punto muerto entre dos transacciones que desean actualizar los datos, pero no evitaría que una transacción que solo lee datos en punto muerto, que debe tener diferentes condiciones DONDE.

El único work-a-round así que hasta ahora parece ser que las lecturas pueden no obtener bloqueos en absoluto. Con SQL Server 2005 esto se puede hacer usando SNAPSHOT ISOLATION. La única forma para SQL Server 2000 sería usar el nivel de aislamiento LEÍDO NO COMUNICADO.

Me gustaría saber si existe otra posibilidad de evitar que SQL Server cause estos bloqueos.

Answer 1

Esto nunca ocurrirá en el aislamiento de instantáneas, cuando los lectores no bloquean a los escritores. Aparte de eso, no hay forma de evitar tales cosas. Escribí muchos scripts aquí: reproduciendo deadlocks que involucran solo una tabla

Editar:

No tengo acceso a SQL 2000, pero trataría de serializar el acceso al objeto mediante sp_getapplock, de modo que la lectura y las modificaciones nunca se ejecuten simultáneamente. Si no puede usar sp_getapplock, despliegue su propio mutex.

Answer 2

Estaba enfrentando el mismo problema. El uso de la sugerencia de consulta FORCE ORDER solucionará este problema. La desventaja es que no podrá aprovechar el mejor plan que tiene el optimizador de consultas para su consulta, pero esto evitará el punto muerto.

Entonces (esto es del usuario "Bill the Lizard") si tiene una consulta FROM table1 LEFT JOIN table2 y su cláusula WHERE solo contiene columnas de table2, el plan de ejecución normalmente seleccionará primero las filas de table2 y luego buscará las filas de table1 . Con un pequeño conjunto de resultados de la tabla 2, solo se deben obtener algunas filas de la tabla1. Con FORCE ORDER primero deben obtenerse todas las filas de table1, porque no tiene una cláusula WHERE, luego las filas de table2 se unen y el resultado se filtra utilizando la cláusula WHERE. Por lo tanto, el rendimiento degradante.

Pero si sabes que este no será el caso, usa esto. Es posible que desee optimizar la consulta manualmente.

La sintaxis es

SELECT ...
FROM
    table1
    LEFT JOIN table2
        ON table1.id = table2.id
    WHERE ...
OPTION (FORCE ORDER)

Answer 3

Otra forma de solucionar esto es dividir la selección ... de ... unirse a múltiples declaraciones de selección. Establezca el nivel de aislamiento para leer confirmado. Use la variable de tabla para canalizar los datos de select para unirlos a otros. Use distinct para filtrar inserciones en estas variables de tabla.

Entonces, si tengo dos tablas A, B. Estoy insertando / actualizando en A y luego B. Donde como el optimizador de consultas de sql prefiere leer primero B y A. Voy a dividir la selección simple en 2 selecciones. Primero leeré B. Luego, transmitiré estos datos a la próxima declaración de selección que dice A.

Aquí el punto muerto no ocurrirá porque los bloqueos de lectura en la tabla B se publicarán tan pronto como se haya completado la primera instrucción.

PD. He enfrentado este problema y esto funcionó muy bien. Mucho mejor que mi respuesta de orden de fuerza.