sql server - transaction - Cuando actualizo/inserto una sola fila, ¿debería bloquear toda la tabla?

para que sirven los bloqueos en sql server

Question

Tengo dos consultas de larga ejecución que son tanto en transacciones como en acceso a la misma tabla pero filas completamente separadas en esas tablas. Estas consultas también realizan algunas actualizaciones e inserciones basadas en esas consultas.

Parece que cuando se ejecutan simultáneamente, se encuentran con un bloqueo de algún tipo e impide que la tarea finalice y se bloquea cuando se actualiza una de las filas. Estoy utilizando un bloqueo de fila exclusivo en las filas que se están leyendo y el bloqueo que se muestra en el proceso es un bloqueo lck_m_ix.

Dos preguntas:

1. Cuando actualizo / inserto una sola fila, ¿bloquea toda la tabla?
2. ¿Qué se puede hacer para solucionar este tipo de problema?

Answer 1

La versión corta:

1. No
2. Arregla tu código.

La versión larga:

LCK_M_IX es un bloqueo de intención, lo que significa que la operación colocará un bloqueo X en un elemento subordinado. P.ej. Al actualizar una fila en una tabla, la tabla de operación toma un bloqueo IX en la tabla antes de bloquear X la fila que se está actualizando / insertando / eliminando. Los bloqueos de intención son una estrategia común para lidiar con las jerarquías, como tabla / página / fila, porque el administrador de bloqueos no puede entender la estructura física de los recursos solicitados para ser bloqueados (es decir, no puede saber que un X-lock en la página P1 es incompatible con un S-lock en la fila R1 porque R1 está contenido en P1). Para más detalles, vea Modos de bloqueo .

El hecho de que esté viendo conflicto en los bloqueos de intención significa que está tratando de obtener bloqueos de objetos de alto nivel, como bloqueos de tabla. Deberá analizar el código fuente de la solicitud que se está bloqueando (la que solicita el bloqueo incompatible con LCK_M_IX) y eliminar la causa de la solicitud de bloqueo a nivel de objeto. Lo que eso signifique dependerá de su código fuente, no puedo saber qué está haciendo allí. Mi conjetura es que utiliza una sugerencia de bloqueo errónea.

Un enfoque más general es confiar en el AISLAMIENTO DE SNAPSHOT . Pero esto, muy probablemente, no resolverá el problema que está viendo, ya que el aislamiento de instantáneas solo puede beneficiar los problemas de contención en el nivel de fila, no las aplicaciones que solicitan bloqueos de tabla.

Answer 2

Las diferentes bases de datos tienen diferentes mecanismos de bloqueo, pero las como SQL Server y Oracle tienen diferentes tipos de bloqueo.

El valor predeterminado en SQL Server parece ser el bloqueo de la página pesimista, por lo que si tiene un número reducido de registros, es posible que todos se bloqueen.

La mayoría de las bases de datos no deberían bloquearse cuando se ejecuta un script, por lo que me pregunto si es posible que esté ejecutando varias consultas simultáneamente sin transacciones.

Answer 3

Normalmente no, pero depende (¡la respuesta más utilizada para SQL Server!)

SQL Server tendrá que bloquear los datos involucrados en una transacción de alguna manera. Tiene que bloquear los datos en la propia tabla, y los datos de cualquier índice afectado, mientras realiza una modificación. Para mejorar la concurrencia, existen varias "granularidades" de bloqueo que el servidor podría decidir usar, para permitir que se ejecuten múltiples procesos: bloqueos de fila, bloqueos de página y bloqueos de tabla (hay más). La escala de bloqueo en juego depende de cómo el servidor decida ejecutar una actualización determinada. Para complicar las cosas, también hay clasificaciones de bloqueos compartidos, exclusivos e intenciones exclusivas, que controlan si el objeto bloqueado se puede leer y / o modificar.

Según mi experiencia, SQL Server utiliza principalmente bloqueos de página para cambios en pequeñas porciones de tablas, y más allá de algún umbral se elevará automáticamente a un bloqueo de tabla, si una parte más grande de una tabla parece (desde las estadísticas) verse afectada por una actualización o borrar. La idea es que es más rápido bloquear una tabla (un bloqueo) que obtener y administrar miles de bloqueos individuales de fila o página para una gran actualización.

Para ver lo que está sucediendo en su caso específico, deberá observar la lógica de consulta y, mientras se está ejecutando su tarea, examine las condiciones de bloqueo / bloqueo en sys.dm_tran_locks, sys.dm_os_waiting_tasks u otros DMV. Desearía descubrir qué se está bloqueando exactamente por qué paso en cada uno de sus procesos, para descubrir por qué uno está bloqueando al otro.

Answer 4

Un objetivo frecuente de utilizar transacciones: manténgalas lo más cortas y dulces posible. Me da la sensación de su redacción en la pregunta de que está abriendo una transacción, y luego hace todo tipo de cosas, algunas de las cuales llevan mucho tiempo. Luego, esperando que varios usuarios puedan ejecutar este mismo código simultáneamente. Desafortunadamente, si realiza una inserción al comienzo de ese conjunto de códigos, luego hace 40 cosas más antes de cometer o deshacer, es posible que esa inserción impida que todos los demás ejecuten el mismo tipo de inserción, esencialmente desviando su operación. libre para todos a serie.

Averigüe qué hace cada consulta y si está obteniendo escalas de bloqueo que no esperaría. El hecho de que diga WITH (ROWLOCK) en una consulta no significa que SQL Server podrá cumplir ... si se tocan múltiples índices, vistas indizadas, columnas calculadas persistentes, etc., hay todo tipo de razones por las que su rowlock No puede contener agua. Es posible que más adelante en la transacción haya cosas que demoren más de lo que cree, y tal vez no se dé cuenta de que los bloqueos en todos los objetos involucrados en la transacción (no solo la declaración que se está ejecutando actualmente) pueden mantenerse. La duración de la transacción.