sys.dm_tran_locks (Transact-SQL)

Se aplica a: SQL Server Azure SQL Database Azure SQL Instancia administrada Azure Synapse Analytics Analytics Platform System (PDW) Warehouse en Microsoft Fabric

Devuelve información acerca de los recursos del administrador de bloqueos activos actualmente en SQL Server. Cada fila representa una solicitud activa al administrador de bloqueos sobre un bloqueo que se ha concedido o está esperando a ser concedido.

Las columnas del conjunto de resultados se dividen en dos grupos principales: recurso y solicitud. El grupo sobre el recurso describe el recurso en que se ha solicitado realizar el bloqueo; el grupo sobre la solicitud describe la solicitud de bloqueo.

Permisos

En SQL Server y SQL Managed Instance, requiere el permiso VIEW SERVER STATE.

En los objetivos de servicio Básico, S0 y S1 de SQL Database y para bases de datos en grupos elásticos, se requiere la cuenta de administrador del servidor, la cuenta de administrador Microsoft Entra o la pertenencia al ##MS_ServerStateReader## rol del servidor. En el resto de objetivos del servicio de SQL Database, se requiere el permiso VIEW DATABASE STATE en la base de datos o la pertenencia en el rol del servidor ##MS_ServerStateReader##.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VER ESTADO DE RENDIMIENTO DEL SERVIDOR en el servidor.

Observaciones

Un estado de solicitud Granted indica que se ha concedido el bloqueo sobre un recurso al solicitante. Una solicitud en espera indica que la solicitud aún no se ha concedido. La columna devuelve request_status los siguientes tipos de solicitud en espera:

• Un estado de solicitud Convert indica que el solicitante ya tiene concedida una solicitud para un recurso y está esperando la concesión de una actualización a la solicitud inicial.

• Un estado de solicitud Wait indica que el solicitante no tiene concedida actualmente ninguna solicitud sobre el recurso.

Dado que sys.dm_tran_locks se rellena desde estructuras de datos internas del administrador de bloqueos, el mantenimiento de esta información no agrega sobrecarga adicional al procesamiento normal. Materializar la vista requiere acceso a las estructuras de datos internas del administrador de bloqueos. Esto puede tener consecuencias menores en el procesamiento normal en el servidor. Puede que no perciba estas consecuencias o que solo afecten a los recursos con un alto grado de utilización. Como los datos de esta vista corresponden al estado activo del administrador de bloqueos, estos se pueden cambiar en cualquier momento; además, las filas se agregan y quitan según se van adquiriendo y liberando bloqueos. Es posible que las aplicaciones que consulten esta vista tengan un rendimiento impredecible debido a la naturaleza de protección de la integridad de las estructuras del administrador de bloqueos. Esta vista no tiene información histórica.

Dos solicitudes funcionan en el mismo recurso solamente si todas las columnas del grupo de recursos son iguales.

Puede controlar el bloqueo de operaciones de lectura mediante las siguientes herramientas:

• SET TRANSACTION ISOLATION LEVEL para especificar el nivel de bloqueo de una sesión. Para obtener más información, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• Sugerencias de bloqueo de tablas para especificar el nivel de bloqueo para una referencia individual de una tabla en una cláusula FROM. Para conocer la sintaxis y las restricciones, consulte Sugerencias de tabla (Transact-SQL).

Un recurso que se ejecuta en uno session_id puede tener más de un bloqueo concedido. Las distintas entidades que se ejecutan en una sesión pueden poseer un bloqueo en el mismo recurso y la información se muestra en las request_owner_type columnas y request_owner_id que devuelve sys.dm_tran_locks. Si existen varias instancias de la misma request_owner_type , la request_owner_id columna se usa para distinguir cada instancia. Para las transacciones distribuidas, las request_owner_type columnas y request_owner_guid muestran la información de entidad diferente.

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

No es posible distinguir varios cursores que se ejecutan en una misma sesión y, por tanto, se tratan como una sola entidad.

Las transacciones distribuidas que no están asociadas a un session_id valor son transacciones huérfanas y se les asigna el session_id valor de -2. Para más información, consulte KILL (Transact-SQL).

Bloqueos

Se mantienen bloqueos en recursos de SQL Server , como filas leídas o modificadas durante una transacción, para evitar que varias transacciones utilicen simultáneamente los recursos. Por ejemplo, si una transacción mantiene un bloqueo exclusivo (X) en una fila de una tabla, ninguna otra transacción podrá modificar esa fila hasta que se libere el bloqueo. La reducción de bloqueos aumenta la simultaneidad, lo que puede mejorar el rendimiento.

Detalles del recurso

En la tabla siguiente se enumeran los recursos que se representan en la resource_associated_entity_id columna .

En la tabla siguiente se muestran los subtipos asociados a cada tipo de recurso.

En la tabla siguiente se proporciona el formato de la resource_description columna para cada tipo de recurso.

Ejemplos

A Uso de sys.dm_tran_locks con otras herramientas

En el ejemplo siguiente se trabaja con un escenario en el que una operación de actualización ha sido bloqueada por otra transacción. Mediante el uso sys.dm_tran_locks y otras herramientas, se proporciona información sobre el bloqueo de recursos.

USE tempdb;
GO

-- Create test table and index.
CREATE TABLE t_lock
    (
    c1 int, c2 int
    );
GO

CREATE INDEX t_lock_ci on t_lock(c1);
GO

-- Insert values into test table
INSERT INTO t_lock VALUES (1, 1);
INSERT INTO t_lock VALUES (2, 2);
INSERT INTO t_lock VALUES (3, 3);
INSERT INTO t_lock VALUES (4, 4);
INSERT INTO t_lock VALUES (5, 5);
INSERT INTO t_lock VALUES (6, 6);
GO

-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN
    SELECT c1
        FROM t_lock
        WITH(holdlock, rowlock);

-- Session 2
BEGIN TRAN
    UPDATE t_lock SET c1 = 10;

La consulta siguiente muestra información de bloqueo. El valor de <dbid> debe reemplazarse por de database_id sys.databases.

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks
    WHERE resource_database_id = <dbid>;

La siguiente consulta devuelve información de objetos de la consulta anterior mediante resource_associated_entity_id. Esta consulta debe ejecutarse mientras se esté conectado a la base de datos que contiene el objeto.

SELECT object_name(object_id), *
    FROM sys.partitions
    WHERE hobt_id=<resource_associated_entity_id> ;

En la consulta siguiente se muestra información de bloqueo.

SELECT
    t1.resource_type,
    t1.resource_database_id,
    t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
    ON t1.lock_owner_address = t2.resource_address;

Libere los recursos revirtiendo las transacciones.

-- Session 1
ROLLBACK;
GO

-- Session 2
ROLLBACK;
GO

B. Vinculación de la información de sesión a subprocesos del sistema operativo

En el ejemplo siguiente se devuelve información que asocia a session_id un identificador de subproceso de Windows. El rendimiento del subproceso puede supervisarse en el Monitor de rendimiento de Windows. Esta consulta no devuelve un session_id que está actualmente en suspensión.

SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
    ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
GO

Contenido relacionado

• sys.dm_tran_database_transactions (Transact-SQL)
• Guía de versiones de fila y bloqueo de transacciones
• Vistas de administración dinámica del sistema
• Funciones y vistas de administración dinámica relacionadas con transacciones (Transact-SQL)
• SQL Server, locks (objeto)