Interbloqueos en SQL Server

Sergio Alcalde | 19 de febrero de 2009 | 1 comentario

Más sobre: Documentación, SQL Server | Tags: bloqueos, concurrencia, SQL, T-SQL, transacciones

Por si fuera poco lo que hemos visto hasta ahora, aún tenemos pendiente revisar la gestión de interbloqueos que realiza SQL Server. Este punto es de vital importancia en nuestras aplicaciones que acceden concurrentemente a los datos en nuestra base de datos.

Un interbloqueo es una situación en la que dos transacciones están esperando para utilizar un recurso bloqueado.

SQL Server detecta automáticamente una situación de interbloqueo mediante un método denominado detección de cadenas circulares de bloqueos. Cuando un interbloqueo ocurre, SQL Server termina el proceso que haya generado la cadena circular de bloqueos.

Los interbloqueos no se producen únicamente a nivel de página de datos, de hecho, muchos interbloqueos se producen a nivel de página de índice.

Minimización de la contienda de bloqueo

Para minimizar el número de interbloqueos se deben considerar las siguientes recomendaciones:

Las transacciones deben ser cortas y afectar al menor número de datos posibles.
Se debe dar preferencia a la utilización de alguna de las técnicas del enfoque optimista.
Puede resultar conveniente establecer una prioridad baja (“set deadlockpriority low”) para las consultas DSS y una alta (“set deadlockpriority normal”) para las consultas OLTP.
El orden de acceso a las tablas debe ser siempre el mismo en las distintas partes del código.
Se debe minimizar el uso de “holdlock”. Por ejemplo, el código:

declare @SEQNO int

begin transaction
   select @SEQNO = isnull(SEQ#,0)+1
   from SEQUENCE with holdlock
   /*
      En ausencia de holdlock el bloqueo compartido sería liberado
      por lo que si otra transacción concurrente ejecutara el mismo
      comando obtendría el mismo número de secuencia
   */
   update SEQUENCE
   set SEQ# = @SEQNO
   /*
      Ahora puede hacerse lo que se desee con este número de
      secuencia
   */
   …
commit transaction
podría reescribirse como:
declare @SEQNO int

begin transaction
   update SEQUENCE
   set @SEQNO = isnull(SEQ#,0)+1
   from SEQUENCE
   select @SEQNO
   …
commit transaction

De esta forma se obtiene directamente el bloqueo de actualización eliminándose la posibilidad de que se produzca un interbloqueo.

SQL Server proporciona un soporte completo para establecer la granularidad del bloqueo a nivel de fila. Una granuralidad del bloqueo más fina proporciona una mejor concurrencia, minimizando el número de interbloqueos, aunque aumenta el trabajo del servidor para gestionar los bloqueos.
Se debe evitar las interactuaciones con el usuario dentro de una transacción con el fin de minimizar el tiempo de duración de la misma.
Se debe seleccionar cuidadosamente el nivel de aislamiento de una transacción, ya que el nivel de aislamiento determina el tipo de bloqueo. Es posible reducir el número de interbloqueos bajando el nivel de aislamiento.

Gestión de interbloqueos

SQL Server devuelve el error 1205 al cliente cuando termina un proceso como resultado de un interbloqueo.

Leer más sobre:

Gestión de transacciones en SQL Server (1/7)
Gestión de bloqueos en SQL Server (2/7)
Enfoques de control de transacciones en SQL Server (3/7)
Enfoque optimista de gestión de transacciones en SQL Server (4/7)
Enfoque pesimista de gestión de transacciones en SQL Server (5/7)
Enfoque mixto de gestión de transacciones en SQL Server (6/7)
Interbloqueos en SQL Server (7/7)

Más sobre: Documentación, SQL Server | Tags: bloqueos, concurrencia, SQL, T-SQL, transacciones