sql - tecnica - Cómo verificar qué bloqueos se llevan a cabo en una mesa

tecnica de la mesa redonda

Question

¿Cómo podemos verificar qué bloqueos de base de datos se aplican en qué filas contra un lote de consulta?

¿Alguna herramienta que destaque el bloqueo del nivel de fila de la tabla en tiempo real?

DB: SQL Server 2005

Answer 1

Esto no muestra exactamente qué filas están bloqueadas, pero esto puede ser útil para usted.

Puede verificar qué declaraciones están bloqueadas ejecutando esto:

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

También le indicará qué espera cada bloque. Para que pueda rastrear todo el camino hacia arriba para ver qué enunciado causó el primer bloque que causó los otros bloques.

Editar para agregar comentarios de @MikeBlandford :

La columna bloqueada indica el spid del proceso de bloqueo. Puede ejecutar kill {spid} para arreglarlo.

Answer 2

Puede encontrar los bloqueos actuales en su tabla siguiendo la consulta.

USE yourdatabase;
GO

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N''dbo.yourtablename'');

Ver sys.dm_tran_locks

Si existen varias instancias del mismo tipo request_owner_type , la columna request_owner_id se usa para distinguir cada instancia. Para las transacciones distribuidas, las columnas request_owner_type y request_owner_guid mostrarán la información de la entidad diferente.

Por ejemplo, la sesión S1 posee un bloqueo compartido en la Tabla1; y la transacción T1, que se ejecuta bajo la sesión S1, también posee un bloqueo compartido en la Tabla1. En este caso, la columna resource_description que devuelve sys.dm_tran_locks mostrará dos instancias del mismo recurso. La columna request_owner_type mostrará una instancia como sesión y la otra como transacción. Además, la columna resource_owner_id tendrá diferentes valores.

Answer 3

También puede usar el procedimiento almacenado sp_who2 para obtener los procesos actuales de bloqueo y bloqueo en una instancia de SQL Server. Normalmente, ejecutaría esto junto con una instancia de SQL Profiler para buscar un proceso de bloqueo y observar el comando más reciente que spid emitió en Profiler.

Answer 4

Uso una Dynamic Management View (DMV) para capturar bloqueos, así como el object_id o partition_id del elemento que está bloqueado.

(DEBE cambiar a la base de datos que desea observar para obtener object_id)

SELECT 
     TL.resource_type,
     TL.resource_database_id,
     TL.resource_associated_entity_id,
     TL.request_mode,
     TL.request_session_id,
     WT.blocking_session_id,
     O.name AS [object name],
     O.type_desc AS [object descr],
     P.partition_id AS [partition id],
     P.rows AS [partition/page rows],
     AU.type_desc AS [index descr],
     AU.container_id AS [index/page container_id]
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_os_waiting_tasks AS WT 
 ON TL.lock_owner_address = WT.resource_address
LEFT OUTER JOIN sys.objects AS O 
 ON O.object_id = TL.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions AS P 
 ON P.hobt_id = TL.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units AS AU 
 ON AU.allocation_unit_id = TL.resource_associated_entity_id;

Answer 5

Para agregar a las otras respuestas, sp_lock también se puede usar para volcar información de bloqueo completo en todos los procesos en ejecución. El resultado puede ser abrumador, pero si quiere saber exactamente qué está bloqueado, es valioso ejecutarlo. Normalmente lo uso junto con sp_who2 para sp_who2 rápidamente en problemas de bloqueo.

Existen múltiples versiones diferentes de procedimientos sp_lock "más amigables" disponibles en línea, dependiendo de la versión de SQL Server en cuestión.

En su caso, para SQL Server 2005, sp_lock aún está disponible, pero desaprobado, por lo que ahora se recomienda utilizar la vista sys.dm_tran_locks para este tipo de cosas. here puede encontrar un ejemplo de cómo "lanzar su propia" función sp_lock.