Describir el bloqueo y el bloqueo
El bloqueo es una característica clave de las bases de datos relacionales, esencial para mantener las propiedades de atomicidad, coherencia y aislamiento del modelo ACID. Todas las acciones de bloqueo rdBMS que infringirían la coherencia y el aislamiento de las escrituras de base de datos. Los programadores de SQL deben iniciar y finalizar transacciones en los puntos adecuados para garantizar la coherencia de los datos. El motor de base de datos proporciona mecanismos de bloqueo para proteger la coherencia lógica de las tablas afectadas, que es fundamental para el modelo relacional.
En SQL Server, el bloqueo se produce cuando un proceso contiene un bloqueo en un recurso específico (fila, página, tabla, base de datos) y un segundo proceso intenta adquirir un bloqueo con un tipo de bloqueo incompatible en el mismo recurso. Normalmente, los bloqueos se mantienen durante un breve período y, una vez que el proceso que contiene el bloqueo lo libera, el proceso bloqueado puede adquirir el bloqueo y completar su transacción.
SQL Server bloquea la menor cantidad de datos necesarios para completar una transacción, lo que permite la máxima simultaneidad. Por ejemplo, si SQL Server bloquea una sola fila, todas las demás filas de la tabla permanecen disponibles para otros procesos, lo que permite el trabajo simultáneo. Sin embargo, cada bloqueo requiere recursos de memoria, por lo que no es rentable que un proceso contenga miles de bloqueos individuales en una sola tabla. Para equilibrar la simultaneidad con el costo, SQL Server usa una técnica denominada extensión de bloqueo. Si es necesario bloquear más de 5000 filas en un solo objeto en una sola instrucción, SQL Server escala los bloqueos de varias filas a un solo bloqueo de tabla.
El bloqueo es un comportamiento normal y se produce con frecuencia durante todo el día. Solo se vuelve problemático cuando provoca bloqueos que no se resuelven rápidamente. Hay dos tipos de problemas de rendimiento causados por el bloqueo:
- Un proceso contiene bloqueos en un conjunto de recursos durante un período prolongado antes de liberarlos, lo que hace que otros procesos bloqueen y desgraden el rendimiento y la simultaneidad de las consultas.
- Un proceso adquiere bloqueos en un conjunto de recursos y nunca los libera, lo que requiere la intervención del administrador para resolverlo.
El interbloqueo es otro escenario de bloqueo que se produce cuando una transacción contiene un bloqueo en un recurso y otra transacción contiene un bloqueo en un recurso diferente. Después, cada transacción intenta adquirir un bloqueo en el recurso actualmente bloqueado por la otra transacción, lo que conduce a una espera infinita, ya que ninguna transacción puede completarse. El motor de SQL Server detecta estos escenarios y resuelve el interbloqueo matando una de las transacciones, en función de qué transacción ha realizado la menor cantidad de trabajo que debe revertirse. La transacción que se mata se conoce como víctima de interbloqueo. Los interbloqueos se registran en la system_health sesión de eventos extendidos, que está habilitada de forma predeterminada.
Es importante comprender el concepto de una transacción. La confirmación automática es el modo predeterminado de SQL Server y Azure SQL Database, lo que significa que los cambios realizados por la siguiente instrucción se registrarían automáticamente en el registro de transacciones de la base de datos.
INSERT INTO DemoTable (A) VALUES (1);
Para permitir que los desarrolladores tengan un control más granular sobre su código de aplicación, SQL Server también le permite controlar explícitamente las transacciones. La consulta siguiente tomaría un bloqueo en una fila de la tabla DemoTable que no se liberaría hasta que se agregara un comando posterior para confirmar la transacción.
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
La manera adecuada de escribir la consulta siguiente es la siguiente:
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
COMMIT TRANSACTION
El COMMIT TRANSACTION comando confirma explícitamente un registro de los cambios en el registro de transacciones. Los datos modificados finalmente se convertirán en el archivo de datos de forma asincrónica. Estas transacciones representan una unidad de trabajo al motor de base de datos. Si el desarrollador olvida emitir el COMMIT TRANSACTION comando, la transacción permanece abierta y no se liberarán los bloqueos. Esta es una de las principales razones para las transacciones de larga duración.
El otro mecanismo que usa el motor de base de datos para ayudar a la simultaneidad de la base de datos es el control de versiones de fila. Cuando se habilita un nivel de aislamiento de control de versiones de fila en la base de datos, el motor mantiene las versiones de cada fila modificada en TempDB. Normalmente se usa en cargas de trabajo de uso mixto, con el fin de evitar que las consultas de lectura bloqueen las consultas que escriben en la base de datos.
Para supervisar las transacciones abiertas en espera de confirmación o reversión, ejecute la consulta siguiente:
SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, transaction_type = CASE tat.transaction_type WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow
, transaction_state = CASE tat.transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The transaction has been initialized but has not started.'
WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The transaction has been committed.'
WHEN 7 THEN 'The transaction is being rolled back.'
WHEN 8 THEN 'The transaction has been rolled back.' END
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_tran_session_transactions tst on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;
Niveles de aislamiento
SQL Server ofrece varios niveles de aislamiento para permitirle definir el nivel de coherencia y corrección que necesita garantizado para los datos. Los niveles de aislamiento permiten encontrar un equilibrio entre la simultaneidad y la coherencia. El nivel de aislamiento no afecta a los bloqueos realizados para evitar la modificación de datos. Una transacción siempre obtendrá un bloqueo exclusivo en los datos que se modifican. Sin embargo, el nivel de aislamiento puede afectar al período de tiempo que se mantienen los bloqueos. Los niveles de aislamiento inferiores aumentan la capacidad de varios procesos de usuario para acceder a los datos al mismo tiempo, pero aumentan los riesgos de coherencia de los datos que pueden producirse. Los niveles de aislamiento de SQL Server son los siguientes:
Leer sin confirmar : nivel de aislamiento más bajo disponible. Se permiten lecturas sucias, lo que significa que una transacción puede ver los cambios realizados por otra transacción que aún no se han confirmado.
Lectura confirmada : permite que una transacción lea datos anteriormente leídos, pero no modificados por otra transacción con sin esperar a que finalice la primera transacción. Este nivel también libera bloqueos de lectura en cuanto se realiza la operación de selección. Este es el nivel predeterminado de SQL Server.
Lectura repetible : este nivel mantiene los bloqueos de lectura y escritura adquiridos en los datos seleccionados hasta el final de la transacción.
Serializable : este es el nivel más alto de aislamiento en el que las transacciones están aisladas. Los bloqueos de lectura y escritura se adquieren en los datos seleccionados y no se liberan hasta el final de la transacción.
SQL Server también incluye dos niveles de aislamiento que incluyen el control de versiones de fila.
Instantánea confirmada de lectura: en este nivel, las operaciones de lectura no toman bloqueos de fila o página, y el motor presenta cada operación con una instantánea coherente de los datos tal como existía al principio de la consulta. Este nivel se usa normalmente cuando los usuarios ejecutan consultas de informes frecuentes en una base de datos OLTP, con el fin de evitar que las operaciones de lectura bloqueen las operaciones de escritura.
Instantánea : este nivel proporciona coherencia de lectura de nivel de transacción a través del control de versiones de fila. Este nivel es vulnerable a conflictos de actualización. Si una transacción que se ejecuta en este nivel lee los datos modificados por otra transacción, se finalizará una actualización de la transacción de instantáneas y se revertirá. Esto no es un problema con el aislamiento de instantáneas confirmadas de lectura.
Los niveles de aislamiento se establecen para cada sesión con el comando T-SQL SET , como se muestra:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
No hay ninguna manera de establecer un nivel de aislamiento global en todas las consultas que se ejecutan en una base de datos o para todas las consultas ejecutadas por un usuario determinado. Es una configuración de nivel de sesión.
Supervisión de problemas de bloqueo
La identificación de problemas de bloqueo puede ser difícil debido a su naturaleza esporádica. La DMV sys.dm_tran_locks, cuando se combina con sys.dm_exec_requests, proporciona información sobre los bloqueos mantenidos por cada sesión. Una manera más eficaz de supervisar los problemas de bloqueo es usar el motor de eventos extendidos de forma continua.
Los problemas de bloqueo normalmente se dividen en dos categorías:
- Diseño transaccional deficiente: por ejemplo, una transacción sin un
COMMIT TRANSACTIONno finalizará nunca. Intentar realizar demasiado trabajo en una sola transacción o tener una transacción distribuida mediante una conexión de servidor vinculado puede provocar un rendimiento imprevisible. - Transacciones de larga duración causadas por el diseño del esquema: esto suele implicar una actualización en una columna con un índice que falta o una consulta de actualización mal diseñada.
La supervisión de problemas de rendimiento relacionados con el bloqueo permite identificar rápidamente la degradación del rendimiento relacionada con el bloqueo.
Para obtener más información sobre cómo supervisar el bloqueo, consulte Descripción y resolución de problemas de bloqueo de SQL Server.