Búsqueda de sitios web

Buscar y eliminar bloqueos en Microsoft SQL Server

Los bloqueos en MS SQL Server son una forma de garantizar la integridad de los datos cuando varios usuarios realizan cambios al mismo tiempo. MSSQL bloquea objetos en una tabla cuando comienza una transacción y libera el bloqueo cuando finaliza la transacción. En este artículo, aprenderemos cómo encontrar bloqueos en la base de datos de MS SQL Server y eliminarlos.

Es posible simular un bloqueo en una de las tablas usando una transacción inconclusa (una que no ha sido completada por rollback o commit). Por ejemplo, utilice la siguiente consulta SQL:

USE tesdb1
BEGIN TRANSACTION
DELETE TOP(1) FROM tblStudents

Antes de realizar cualquier cambio, SQL Server bloqueará la tabla. Ahora intente abrir SQL Server Management Studio y ejecutar una consulta de selección de SQL simple:

SELECT * FROM tblStudents

La consulta se congelará en el estado "Ejecutando consulta" hasta que se agote el tiempo de espera. Esto se debe a que la consulta SELECT intenta acceder a los datos de una tabla que está bloqueada por SQL Server.

Puede configurar bloqueos de nivel de fila o de tabla completa en Microsoft SQL Server.

Para obtener una lista de todas las consultas bloqueadas en MSSQL Server, ejecute el comando

select cmd,* from sys.sysprocesses
where blocked > 0

También puede mostrar una lista de bloqueos para una base de datos específica:

SELECT * FROM master.dbo.sysprocesses
WHERE
dbid = DB_ID('testdb12') and blocked <> 0
order by blocked

La columna Bloqueado muestra el ID de proceso del proceso que bloqueó los recursos. También muestra el tiempo de espera para esta consulta (tiempo de espera en milisegundos). Si es necesario, se puede utilizar para buscar los bloqueos más antiguos o más recientes.

En algunos casos, un árbol de procesos completo puede causar un bloqueo. Para encontrar el proceso de bloqueo de origen, use la siguiente consulta para SPID hasta que encuentre el proceso con blocked=0, que es el proceso inicial que contiene el bloqueo.

select * FROM
master.dbo.sysprocesses
where 1=1
--and blocked <> 0
and spid = 59

El proceso SPID te da el código T-SQL de la última consulta SQL ejecutada por ese proceso o transacción:

DBCC INPUTBUFFER(59)

Para forzar la finalización del proceso y liberar el bloqueo, ejecute el comando:

KILL number_of_session
GO

En mi caso será:

KILL 59

Puede crear un procedimiento almacenado independiente si se producen bloqueos constantemente y desea identificar las consultas que consumen más recursos:

CREATE PROCEDURE GetCurrentQueryCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end - @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
print @line

Ahora, si desea ver la consulta SQL que bloqueó la tabla, todo lo que necesita hacer es especificar su SPID:

Exec GetCurrentQueryCode 51

También puede obtener el código de consulta del sql_handle del proceso de bloqueo. Por ejemplo

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

Puede utilizar Microsoft SQL Server Management Studio para buscar bloqueos en MS SQL Server. Puede utilizar uno de los siguientes métodos:

  • Haga clic derecho en el servidor, abra el Monitor de actividad y expanda Procesos. Verá una lista de solicitudes en espera de que se liberen recursos con un estado SUSPENDIDO.

  • Seleccione una base de datos y vaya a Informes -> Todas las transacciones bloqueadas. También muestra una lista de consultas bloqueadas y el SPID de la fuente de bloqueo.

Artículos relacionados