Transacciones y bloqueos en Sql Server

En muchas situaciones, la modificación de datos requiere varios pasos. Por 
ejemplo, puede necesitar cambiar los valores en dos tablas separadas. 

Puede usar transacciones para completar estas dos operaciones como una unidad, o si ocurre un error, no cambia ninguna de las tablas. Otra consideración clave es que la mayoría de las bases de datos deben ser compatibles con muchas operaciones simultáneamente. Tiene varias opciones para configurar cómo se bloquean los datos mientras el usuario los modifica. En la primera parte de la serie de artículos Transaction and Locking, aprenderá los fundamentos acerca de las transacciones de SQL Server, cómo funcionan y cómo administrarlas, y cómo escribir procedimientos que usan transacciones.

Fundamentos de transacción

Una transacción es una serie de sentencias y procedimientos de comandos SQL 
que se ejecutan como una única unidad lógica. Una transacción exitosa debe 
exhibir las siguientes cuatro propiedades:

  • Atomicidad: la transacción es una sola unidad de trabajo en la que se completan todos los pasos o no.
  • Consistencia: la transacción debe dejar todos los datos en un estado consistente.
  • Aislamiento: loscambios realizados por las transacciones simultáneas deben estar aislados entre sí, lo que significa que ninguna transacción debe encontrar datos en un estado indeterminado (en el proceso de cambio).
  • Durabilidad: los cambios realizados por la transacción se mantienen.

SQL Server confirma (escribe permanentemente) las instrucciones de una 
transacción en la base de datos solo después de que todas se ejecutan correctamente. Este es un ejemplo de la propiedad de atomicidad. La atomicidad es crítica cuando todas o ninguna de las manipulaciones deben completarse juntas. Si las declaraciones en una transacción fallan, el desarrollador deshace toda la transacción. Cuando 
se retrotrae una transacción, SQL Server no confirma ninguna declaración en la base de datos. 
Usted impone la coherencia a través de objetos de base de datos tales como:

  • Restricciones de teclas principales
  • Restricciones de clave externa
  • Verificar restricciones
  • Disparadores

SQL Server es responsable de la coherencia interna, como asegurarse de que las estructuras internas de datos no estén dañadas. Usted impone el aislamiento a través de la configuración de aislamiento de transacción. Puede controlar qué tan estricto se aplica el aislamiento, si se permite o no el acceso a los datos mientras se aplica una transacción, y el alcance del aislamiento. El alcance del aislamiento varía de una sola fila a una base de datos completa.

Usted aplica la durabilidad cuando realiza una transacción. SQL Server usa el registro de transacciones para garantizar la durabilidad al mantener una copia de la transacción hasta que se aplique físicamente a los objetos de la base de datos.

Cómo funcionan las transacciones

El siguiente es un ejemplo de cómo funcionan las transacciones. Tienes dos tablas: Savings to Checking. Cuando transfiere dinero de Savings to Checking, deben ocurrir dos acciones:

  • El monto debe deducirse de la columna Saldo en Ahorros.
  • El importe debe agregarse a la columna Saldo en Comprobación.

Si solo ocurre una de estas acciones, los datos son inconsistentes. Puede garantizar la coherencia de los datos mediante el uso de transacciones.

Rol de registro de transacciones

El registro de transacciones hace posible la transacción ACID.

  • Las transacciones se escriben en el registro de transacciones.
  • En caso de que se produzca una falla del sistema o después de la recuperación de las copias de seguridad, SQL Server procesa el contenido del registro de transacciones para llevar las tablas e índices de la base de datos a un estado constante hasta el punto de falla.
  • Las transacciones completadas y confirmadas se aplican (avanzando).
  • Las transacciones incompletas o no confirmadas se retrotraen.

Modos de transacción

SQL Server admite los siguientes modos de transacción:

  • Explícito: elextracto debe comenzar, comprometerse y retrotraerse explícitamente en cada transacción.
  • Implícito: lapróxima transacción comienza automáticamente cuando la transacción anterior se confirma o se retrotrae.
  • Autocommit: Cada declaración es su propia transacción.
  • Ámbito del lote:todas las transacciones que se inician en una sesión de múltiples conjuntos de resultados activos (MARS) forman parte de una transacción con alcance por lotes y cualquier transacción que no se complete cuando el lote se complete se revierte.
  • Gestiona transacciones por separado para cada conexión. Puede configurar un modo de transacción diferente para cada conexión según sea necesario. A menos que se especifique lo contrario, SQL Server opera en modo de confirmación automática con cada instrucción tratada como una transacción independiente.

Conjuntos de resultados activos múltiples (MARS)

Las versiones anteriores de SQL Server no permitían múltiples declaraciones activas en una sola conexión. Las aplicaciones tenían que procesar o cancelar los conjuntos de resultados de un lote antes de ejecutar un mismo lote en la misma conexión. Esto satisfizo las necesidades de la mayoría de las aplicaciones, pero no todas.

SQL Server 2005 presentó compatibilidad para múltiples conjuntos de resultados activos (MARS) en aplicaciones que acceden al Motor de base de datos. Esto significa que una aplicación puede mantener múltiples conjuntos de resultados, conocidos como conjuntos de resultados activos múltiples (MARS) en una sola conexión. MARS opera intercalando solicitudes y no procesando solicitudes en paralelo. El soporte de MARS está deshabilitado por defecto. 

Para obtener más información sobre MARS, consulte Uso de Múltiples conjuntos de resultados activos (MARS)

Estados de cuenta

Usted gestiona las transacciones agrupando un conjunto de declaraciones. Agrupe 
las declaraciones definiendo el comienzo, el final y entre estos, los puntos de guardado 
y los puntos de retroceso para las transacciones explícitas. SQL Server admite las 
siguientes declaraciones para implementar transacciones:

  • BEGIN TRANSACTION:identifica el comienzo de una transacción. Después de que se abre, una transacción permanece abierta hasta que se confirma o se retrotrae.
  • COMPROMISO DE TRANSACCIÓN:identifica el final de una transacción. La instrucción informa a SQL Server que tiene todas las declaraciones necesarias para completar la transacción.
  • GUARDAR TRANSACCIÓN:coloca un punto de rescate en la transacción como un lugar seguro donde la transacción puede revertirse. Puede hacer referencia al nombre en una Instrucción ROLLBACK TRANSACTION .
  • ROLLBACK TRANSACTION:transfiere una transacción al último punto de rescate o un punto de rescate especificado por nombre. Si no se ha establecido un punto de rescate, la transacción retrocede al comienzo de la transacción. Si la transacción ya está comprometida, no puede deshacerse.

La función @@TRANCOUNT devuelve el número de transacciones abiertas para una conexión. Cada vez que se inicia una transacción , SQL Server incrementa @@TRANCOUNT en 1. Cada vez que una transacción se compromete, SQL Server reduce @@ TRANCOUNT en 1. Si una transacción se retrotrae al principio (no a un punto de rescate), SQL Server establece @ @TRANCOUNT a 0. Usualmente usa @@ TRANCOUNT cuando necesita anidar transacciones. Una transacción anidada es una transacción definida dentro de los límites de otra transacción. Para obtener más información , consulte Transacciones anidadas
La instrucción BEGIN TRANSACTION

La sintaxis para la instrucción BEGIN TRANSACTION es:

BEGIN TRAN[SACTION] [transaction_name | @tran_name_var

[WITH MARK [‘description‘]]]

  

Puede usar el valor transaction_name o @tran_name_var (variable de nombre de transacción ) para asignar un identificador a la transacción. El nombre de la transacción es obligatorio si usa la cláusula WITH MARK. Cuando define una transacción usando WITH MARK, la descripción se ingresa en el registro de transacciones. Puede usar marcas de transacción durante la restauración de la base de datos para restaurar hasta el punto de una transacción específica.

La declaración COMMIT TRANSACTION

La sintaxis de la instrucción COMMIT TRANSACTION es:

 

COMMIT [TRAN [SACTION]] [ transaction_name | @ tran_name_var ]]

 

Si ejecuta COMMIT TRAN cuando @@ TRANCOUNT es igual a cero, SQL 
Server genera un error porque no hay un TREN DE COMIENZO correspondiente.

Alternativamente, puede ejecutar transacciones de confirmación con:

COMMIT [WORK]

COMMIT especificado por sí mismo con la palabra clave WORK es compatible con SQL-92.

La declaración SAVE TRANSACTION

Utiliza la instrucción SAVE TRANSACTION para establecer puntos de rescate en una transacción que permite retrocesos parciales. SQL Server no libera recursos de transacción hasta que la transacción completa se confirma o se retrotrae.

La sintaxis para SAVE TRANSACTION es:

 

SAVE TRAN[SACTION] savepoint_name | @savepoint_name_var

Debe usar un identificador válido para el nombre del punto de guardado. SQL Server no admite puntos de guardado para transacciones distribuidas. Una transacción distribuida es aquella que involucra acciones en múltiples servidores.

La instrucción ROLLBACK TRANSACTION

La instrucción ROLLBACK TRANSACTION transfiere una transacción al comienzo de la transacción o a un punto de rescate establecido dentro de la transacción. Cuando revierte una transacción, todos los cambios se borran en el punto de rescate definido (si existe) o al comienzo de la transacción. La sintaxis para este comando es:

ROLLBACK TRAN[SACTION] [transaction_name | @tran_name_var

| savepoint_name | @savepoint_name_var]

 

Si usa ROLLBACK TRAN en un desencadenador, todas las instrucciones en el desencadenador que siguen a la reversión aún se ejecutan. Sin embargo, una vez que finaliza el desencadenador, finaliza el lote que llamó al enunciado que provocó el disparo del desencadenador. ROLLBACK TRAN funciona de manera diferente si ocurre dentro de un procedimiento almacenado. Las instrucciones en el lote que llamó al procedimiento almacenado aún se ejecutan. La versión compatible con SQL-92 de este comando es:

 

ROLLBACK [WORK]

ROLLBACK WORK es esencialmente lo mismo que ROLLBACK TRAN, excepto que los 
nombres de las transacciones y los nombres de los puntos de rescate no son compatibles.

Ejemplo de transacción

Como ejemplo, tiene dos procedimientos almacenados que deben completarse con atomicidad. Este ejemplo asume que ya ha definido dos procedimientos almacenados llamados rev_inv y rev_cust que se usan para actualizar las tablas de la base de datos y que la transacción se usa dentro de un procedimiento almacenado que define las variables @paid, @invdate e @invnum. Para definir esto como una transacción, ejecuta:

 

    BEGIN TRAN cust_pay

EXEC rev_inv @paid, @invnum

EXEC rev_cust @paid, @invdate

COMMIT TRAN

 

Para modificar el ejemplo para utilizar la comprobación de errores estructurados, ejecute lo 
siguiente:

BEGIN TRAN cust_pay

BEGIN TRY

EXEC rev_inv @payed, @invnum

EXEC rev_cust @payed, @invdate

COMMIT TRAN

END TRY

BEGIN CATCH

print “An error occurred”

ROLLBACK TRAN

END CATCH

   

Usted tiene cierto control sobre cómo SQL Server reacciona a los errores. De forma predeterminada, si se produce un error de instrucción en tiempo de ejecución durante el procesamiento por lotes; solo la declaración que causó el error se revierte automáticamente por el motor de la base de datos . Usted controla este comportamiento utilizando la opción XACT_ABORT , que está establecida en OFF por defecto. La sintaxis de esta opción es:

 

SET XACT_ABORT ON | OFF

   

Cuando establece la opción XACT_ABORT en ON y se produce un error de tiempo de ejecución, el motor de la base de datos retrotrae la transacción actual completa. Sin embargo, en lugar de confiar en reversiones automáticas, debe incluir un 
código de manejo de errores explícito en lotes que ejecutan transacciones.

Transacciones implícitas

Para activar y desactivar el soporte para transacciones implícitas, use lo 
siguiente:

SET IMPLICIT_TRANSACTIONS ON | OFF

Cuando se establece en ON, se iniciará una transacción cuando se ejecuta ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE o UPDATE, y una transacción aún no está abierta. Cuando la opción IMPLICIT_TRANSACTION está establecida en ON, debe confirmar o revertir la transacción. De lo contrario, SQL Server revierte los cambios cuando el usuario se desconecta. Cuando tiene la opción IMPLICIT_TRANSACTION establecida en OFF, cada declaración se considera una transacción (modo de confirmación automática).

Declaraciones y consideraciones ilegales

No debe usar ciertas declaraciones en las transacciones definidas por el usuario porque las funciones que realizan no se pueden deshacer. Los rollbacks deben poder deshacer funciones; por lo tanto, debe evitar usar las siguientes declaraciones en una transacción:

  • CREATE TABLE, ALTER TABLE, TRUNCATE TABLE
  • CREATE INDEX
  • All DROP statements
  • SELECT…INTO
  • GRANT or REVOKE
  • DISK INIT, LOAD DATABASE, LOAD TRANSACTION
  • Cualquier usuario o procedimiento almacenado del sistema que utiliza cualquiera de 
    las declaraciones anteriores

En muchos casos, SQL Server no genera un error cuando utiliza estas declaraciones en una transacción. Sin embargo, no deben usarse debido a la naturaleza de las declaraciones. Además, el motor de base de datos genera un error cuando intenta ejecutar cualquiera de las siguientes declaraciones dentro de una transacción:

  • CREATE DATABASE, ALTER DATABASE, DROP DATABASE
  • COPIA DE SEGURIDAD DE RESTAURACION
  • RECONFIGURE
  • UPDATE STATISTICS

 

Visión general de bloqueo

El bloqueo es una parte necesaria del proceso de transacción cuando se trabaja en un 
entorno de procesamiento de transacciones en línea multiusuario (OLTP). SQL Server usa bloqueos para evitar conflictos de actualización. Por ejemplo, cuando un usuario actualiza los datos en la tabla, los bloqueos de SQL Server impiden que otros usuarios accedan a los datos que se están actualizando. Los bloqueos ayudan a prevenir:

  • Lost Updates: ocurre cuando dos transacciones están actualizando los mismos datos simultáneamente. Los cambios se guardan en la última transacción que se escribe en la base de datos, perdiendo cambios de otra transacción.
  • Dirty reads: Se produce cuando una transacción lee datos no confirmados de otra transacción. Esto puede conducir a cambios para realizar cambios inexactos en los datos. Esto también se conoce como una dependencia no comprometida.
  • Nonrepeatable reads: Se produce cuando cambian los datos de fila entre las lecturas de datos. Esto también se conoce como análisis inconsistente.
  • Phantoms: aparece un registro cuando una transacción relee los datos luego de realizar un cambio Puede serializar transaccionescon bloqueos, lo que significa que solo una persona puede cambiar un elemento de datos, como una fila en particular, todo a la vez. SQL Server puede emitir un bloqueo para:
    • Un identificador de fila (RID), que bloquea una sola fila en una tabla.
    • Una clave, que es un bloqueo de fila dentro de un índice
    • Una tabla que bloquea todas las filas e índices de datos
    • Una base de datos, que se utiliza al restaurar una base de datos
    • Una página que bloquea una página de datos o índice de 8 KB
    • Una medida, bloqueo de un grupo contiguo de páginas durante la asignación de espacio.

SQL Server selecciona un nivel de bloqueo apropiado para la manipulación de datos actual o acción de definición. Por ejemplo, SQL Server usa un bloqueo de fila para actualizar una sola fila de datos en una tabla. SQL Server utiliza la gestión de bloqueo dinámico, lo que significa que el nivel de bloqueo se puede ajustar automáticamente según sea necesario. Puede usar la vista de administración dinámica sys.dm_tran_locks para obtener información acerca de los bloqueos activos.

Bloqueos básicos

SQL Server admite los siguientes tipos de bloqueos:

  • Bloqueos compartidos (S): se usa cuando se realizan operaciones de solo lectura en la base de datos. Los recursos bloqueados con un bloqueo compartido están disponibles para SELECT, pero no para modificación.
  • Bloqueos exclusivos (X): se usan para operaciones que modifican datos, como las instrucciones INSERT, UPDATE y DELETE que requieren bloqueos exclusivos. No más de una transacción puede tener un bloqueo exclusivo en un recurso. Si hay un bloqueo exclusivo en un recurso, ninguna otra transacción puede acceder a ese recurso.
  • Bloqueo de intención: establece una jerarquía de bloqueo. Por ejemplo, si una transacción tiene un bloqueo exclusivo en una fila, SQL Server coloca un bloqueo de intención en la tabla. Cuando otra transacción solicita un bloqueo en una fila de la tabla, SQL Server sabe que debe verificar las filas para ver si tienen bloqueos. Si una tabla no tiene un bloqueo de intención, puede emitir el bloqueo solicitado sin verificar cada fila para un bloqueo.
  • Bloqueo de actualización (U): este tipo de bloqueo generalmente se coloca en una página antes de realizar una actualización. Cuando SQL Server está listo para actualizar la página, el bloqueo se promoverá a un bloqueo de página exclusivo.
  • Bloqueo de esquema: se usa para evitar que una tabla o índice que se está utilizando en otra sesión se descarte o se modifique su esquema. Cuando un recurso está bloqueado con un bloqueo de esquema, no se puede acceder al objeto.
  • Bloqueos de actualización masiva (BU): se usa para evitar que otros procesos accedan a una tabla mientras se procesa el procedimiento de carga masiva. Sin embargo, permitirá el tratamiento de procesos concurrentes de carga masiva, lo que le permitirá ejecutar cargas paralelas. Un procedimiento de carga masiva se realiza mediante el uso de un programa de copia masiva (bcp) o BULK INSERT.


Bloqueo optimista y pesimista

Son los dos términos que se usan comúnmente para describir los métodos de bloqueo:

  • El bloqueo pesimista bloquea los recursos a medida que se adquieren, manteniendo bloqueos en la duración de la transacción. Es más probable que cause bloqueos. Un punto muerto se produce cuando dos transacciones bloquean el acceso a los recursos necesarios para la otra transacción.
  • El bloqueo optimista supone que los conflictos entre transacciones no son probables, pero podría suceder. Las transacciones se pueden ejecutar sin bloquear recursos. La única vez que los recursos están controlados por un conflicto es cuando se realizan cambios en los datos. Si se produce un conflicto, la transacción se revierte.

Versión de fila

Desde el lanzamiento de SQL Server 2005, Microsoft introdujo el control de versiones de filas como alternativa a los bloqueos compartidos. Con el control de versiones de filas, las filas se leen en tempdb al comienzo de una transacción, y la transacción utiliza esa copia de esas filas a lo largo de la transacción. El control de versiones de fila protege la 
transacción de:

  • Dirty reads
  • Nonrepeatable reads
  • Phantoms

Incluso cuando se utiliza el control de versiones de filas, SQL Server aún tiene un bloqueo exclusivo en una fila antes de actualizarlo.

El control de versiones de filas permite una concurrencia óptima (múltiples usuarios acceden a los datos al mismo tiempo), a la vez que proporciona una buena protección. Sin embargo, debe asegurarse de que tempdb tenga suficiente espacio en disco disponible. El uso de versiones de fila también puede degradar el rendimiento debido a los recursos necesarios para mover datos dentro y fuera de tempdb.


Aislamiento de transacción

El aislamiento de transacciones protege las actividades de transacciones realizadas fuera de la transacción, cumpliendo con los requisitos de aislamiento de una transacción ACID. Administra el aislamiento de transacciones como una configuración de nivel de sesión que afecta todas las operaciones en sus sesiones. Puede anular el nivel de aislamiento utilizando las cuentas individuales de bloqueo de cuentas.

Para establecer el nivel de aislamiento de la transacción, ejecuta:

    SET TRANSACTION ISOLATION LEVELREAD COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE

 

Los niveles de aislamiento se definen de la siguiente manera:

  • READ UNCOMMITED: las lecturas sucias son posibles. No se adquiere un bloqueo compartido y no se respetan los bloqueos exclusivos.
  • READ COMMITED: Evita lecturas sucias mediante el uso de bloqueos compartidos o versiones de filas. El método utilizado depende de la configuración de la opción de base de datos READ_COMMITED_SNAPSHOT. Si está activado, SQL Server utiliza el control de versiones de filas. Si está desactivado, SQL Server usa bloqueos compartidos. El uso del control de versiones de filas mejora la concurrencia porque SQL Server administra el control de versiones de filas en un nivel de instrucción, no repetible, que son causadas por cambios de datos entre lecturas.
  • REPEATABLE READS: No se pueden realizar lecturas sucias y lecturas no repetibles. Los bloqueos de lectura se mantienen hasta que la transacción se confirma o se retrotrae.
  • SNAPSHOT: Los cambios de datos realizados fuera de la transacción después de que comience la transacción no son visibles dentro de la transacción. Utiliza el control de versiones de filas. No se mantienen bloqueos compartidos. Cuando intenta actualizar los datos, SQL Server compara los datos actuales con los almacenados en tempdb. Si son diferentes, la actualización falla y la transacción se retrotrae. Solo se usará si la opción de base de datos está EN ALERTA_SALIDA_ISOLACIÓN .
  • SERIALIZABLE: otras transacciones no pueden actualizar o insertar ninguna nueva fila que haya leído la transacción hasta que se haya confirmado la transacción actual.

Después de cambiar el nivel de aislamiento, puede ejecutar USEROPTIONS DBCC para 
verificar que el cambio se realizó en la conexión actual.

Hints de Tabla

También puede usar sugerencias de tabla para anular el bloqueo utilizado para una sola instrucción SELECT, UPDATE, INSERT o DELETE. Las sugerencias de tabla se especifican en la cláusula FROM de la instrucción DML y afectan solo a la tabla o vista a la que se hace referencia en esa cláusula. La siguiente es la sintaxis general para especificar sugerencias de tabla:

WITH  ( <table_hint> [ [, ]…n ] )

        <table_hint> ::=

        [ NOEXPAND ] {

    INDEX  ( index_value [ ,…n ] )

  | INDEX =  ( index_value )   

  | FORCESEEK [( index_value ( index_column_name  [ ,… ] ) ) ]

  | FORCESCAN

  | FORCESEEK

  | HOLDLOCK

  | NOLOCK

  | NOWAIT

  | PAGLOCK

  | READCOMMITTED

  | READCOMMITTEDLOCK

  | READPAST

  | READUNCOMMITTED

  | REPEATABLEREAD

  | ROWLOCK

  | SERIALIZABLE

  | SPATIAL_WINDOW_MAX_CELLS = integer

  | TABLOCK

  | TABLOCKX

  | UPDLOCK

  | XLOCK

}

<table_hint_limited> ::=

{

    KEEPIDENTITY

  | KEEPDEFAULTS

  | HOLDLOCK

  | IGNORE_CONSTRAINTS

  | IGNORE_TRIGGERS

  | NOLOCK

  | NOWAIT

  | PAGLOCK

  | READCOMMITTED

  | READCOMMITTEDLOCK

  | READPAST

  | REPEATABLEREAD

  | ROWLOCK

  | SERIALIZABLE

  | TABLOCK

  | TABLOCKX

  | UPDLOCK

  | XLOCK

}

 

   

Para obtener más información sobre los consejos de tabla y las sugerencias relacionadas con el bloqueo, consulte Sugerencias de tabla (Transact-SQL).


Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Límite de tiempo se agote. Por favor, recargar el CAPTCHA por favor.