Transacciones en SQL Server

¿Qué es una transacción en SQL Server?

Una transacción en SQL Server es un conjunto de instrucciones del Transact SQL que se ejecutan como un único bloque, si una de las instrucciones reporta error todo el conjunto de instrucciones se anula, si ninguna instrucción reporta un error, todas las instrucciones se confirman y ejecutan con éxito.

Características de las transacciones en SQL Server

  1. Atomicidad: significa que las instrucciones de la transacción tienen éxito o fallan juntas. A menos que todos las instrucciones se ejecuten correctamente la transacción será completada.
  2. Consistencia: significa que las instrucciones en una transacción tiene un estado consistente. La transaccion lleva la base de datos subyacente de un estado estable a otro, sin reglas violadas antes de la comenzando o después del final de la transacción.
  3. Aislamiento: cada transacción es una entidad independiente. Una transacción no afectará a ninguna otra transacción que se ejecuta al mismo tiempo.
  4. Durabilidad: cada transacción se mantiene en un medio confiable que no se puede deshacer mediante fallas del sistema. Además, si una falla del sistema ocurre en medio de una transacción,los pasos completados deben deshacerse o los pasos incompletos deben ejecutarse para terminar la transacción. Esto suele ocurrir mediante el uso de un registro que se puede reproducir para volver el sistema a un estado consistente.

Alcance de las transacciones en SQL Server

Dependiendo de la forma de como trabajan las transacciones pueden ser de dos tipos:
Transacciones Locales: las que trabajan en una sola base de datos.
Transacciones distribuidas: las que trabajan en multiples bases de datos.

Transacciones Locales en SQL Server

Las transacciones que trabajan en una sola base de datos se llaman transacciones locales, estas transacciones tienen cuatro modos diferentes:

  1. AutoCommit
  2. Explicit
  3. Implicit
  4. Batch-scope

Transacciones en modo AutoCommit

El modo AutoCommit, llamado transacción de confirmación automática es el modo de transacción predeterminado.En este modo, SQL Server garantiza la seguridad de los datos durante toda la vida útil de la ejecución de la consulta, independientemente de si ha solicitado o no una transacción. Por ejemplo, si ejecuta una instrucción de lenguaje de manipulación de datos (DML) (ACTUALIZAR,INSERTAR, o ELIMINAR), los cambios se confirmarán automáticamente (si no se producen errores) o se revertirán (deshecho) en caso contrario. (Ver Insertar registros, Editar registros, Eliminar registros)

Transacciones en modo Explicit

El modo de transacción AutoCommit permite ejecutar instrucciones individuales de manera transaccional, pero con frecuencia, se requiere que un lote de instrucciones funcione dentro de una sola transacción. En ese escenario, se debe utilizar transacciones explícitas. En el modo de transacción explícita, se solicita explícitamente los límites de una transacción. En otras palabras, se especifica con precisión cuándo comienza la transacción y cuándo termina.
Al terminar el modo Explicit, SQL Server continúa trabajando en el modo de transacción AutoCommit hasta que solicite una excepción a la regla, por lo que si desea ejecutar una serie de sentencias Transact-SQL (T-SQL) como un solo lote, usa el modo de transacción explícito en su lugar.

Transacciones en SQL Server en modo Implicit

SQL Server Management Studio (SSMS) o SQL Server Data Tools (SSDT) por defecto tienen conexión en modo de transacción automática lo que significa que al ejecutar una instrucción DML, los cambios se guardan automáticamente. El modo Implicit en las transacciones en SQL Server requiere que los cambios sean confirmados usando Commit o anulados usando Rollback.
Para configurar la conexión de la base de datos al modo de transacción implícita se utliza la sentencia
SET IMPLICIT_TRANSACTIONS {ON | OFF}
Una transacción se inicia automáticamente cuando se ejecuta cualquiera de los siguientes instrucciones:
ALTER table, Create, Delete, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, o UPDATE.
El término implícito se refiere al hecho de que una transacción se inicia implícitamente sin una Declaración explícita BEGIN TRANSACTION. Por lo tanto, siempre es necesario que explícitamente confirme la transacción posteriormente para guardar los cambios (o revertirla para descartarlos).
Con el modo de transacción implícita, la transacción que comienza implícitamente no se termina o revierte a menos que se solicite explícitamente. Esto significa que si emite una sentencia UPDATE, SQL Server mantendrá un bloqueo en los datos afectados hasta que emita un COMMIT o ROLLBACK. Si
no emite una instrucción COMMIT o ROLLBACK, la transacción se anula cuando el usuario desconecta

Transacciones en SQL en modo Batch-Scope

Desde SQL Server 2005, se admiten múltiples conjuntos de resultados activos (MARS) en la misma conexión, esto no significa que haya ejecución paralela de comandos. El comando ejecución todavía está intercalado con reglas estrictas que rigen qué declaraciones pueden sobrepasar otras declaraciones.

Las conexiones que utilizan MARS tienen un entorno de ejecución por lotes asociado. En la ejecución por lotes el entorno contiene varios componentes, como las opciones SET, el contexto de seguridad, el contexto de la base de datos, y las variables de estado de ejecución, que definen el entorno en el que se ejecutan los comandos. Cuando MARS está habilitado, puede tener múltiples lotes intercalados ejecutándose al mismo tiempo, por lo que todos
los cambios realizados en el entorno de ejecución se aplican al lote específico hasta la ejecución de ese lote esta completo Una vez finalizada la ejecución del lote, se copian los ajustes de ejecución al entorno por defecto. Por lo tanto, se dice que una conexión utiliza el modo de transacción de ámbito por lotes si está ejecutando una transacción, tiene habilitado MARS en él y tiene varios lotes intercalados ejecutándose al mismo tiempo.

Ejercicios

Usando la base de datos Northwind
use Northwind
go

Ejercicio 01: Insertar un registro en la tabla Region usando transacción. (Ver Insertar registros)
Begin transaction InsertaRegion
insert into Region ([RegionID], [RegionDescription])
values (30,’Lima’)
if @@ERROR <> 0
Begin
Rollback Transaction InsertaRegion
Print ‘Anulada… no se insertó’
End
Else — No hay error
Begin
Commit Transaction InsertaRegion
Print ‘Se insertó la región’
End
go

Ejercicio 02: Crear un Store Procedure (Ver procedimientos almacenados) para insertar una región.
Create procedure spRegionInserta
(
@RegionID int,
@RegionDescription nvarchar(50)
)
As
Begin transaction InsertaRegion
insert into Region ([RegionID], [RegionDescription])
values (@RegionID,@RegionDescription)
if @@ERROR <> 0
Begin
Rollback Transaction InsertaRegion
End
Else — No hay error
Begin
Commit Transaction InsertaRegion
End
go

Ejercicio 03: Crear un trigger en la tabla Region que no permita insertar un registro con la descripción duplicada. (Ver Triggers)
if exists (select * from sys.triggers where name = ‘trRegionNoPermiteDuplicados’)
Begin
Drop trigger trRegionNoPermiteDuplicados
End
go
Create trigger trRegionNoPermiteDuplicados
on Region for Insert, update
As
Begin
if (select count(*) from Region, inserted
where Region.RegionDescription = inserted.RegionDescription)>1
Begin
Rollback Transaction
Print ‘Ya existe una región con el nombre insertado’
End
Else
Begin
Print ‘Se insertó el registro, mensaje desde Trigger’
End
End
go

Ejercicio 04: Este código muestra transacciones anidadas. Tiene en cuenta el registro de una Factura y el detalle de la misma.
Begin transaction GuardaFactura


… Instrucciones para guardar la factura
Begin Transaction GuardaDetalleFactura
Declare @vDetalle nchar(1) = ‘S’

If @@ERROR = 0 — Error al guardar el detalle
Begin
Commit tran GuardaDetalleFactura
End
Else
Begin
Set @vDetalle = ‘E’
Rollback tran GuardaDetalleFactura
End
— Comprueba error al guardar el detalle
if @vDetalle = ‘E’
Begin
Rollback tran GuardaFactura
End
Else
Begin
Commit tran GuardaFactura
End
go