Unidad 4 Tema 2: Transacciones en SQL Server

En SQL Server las instrucciones equivalentes a las genéricas que acabamos de ver son:

  • BEGIN TRANSACTION o BEGIN TRAN: marca el inicio de una transacción. TRAN es un sinónimo de TRANSACTION y se suele usar más a menudo por abreviar.
  • ROLLBACK TRANSATION o ROLLBACK TRAN: fuerza que se deshaga la transacción en caso de haber un problema o querer abandonarla. Cierra la transacción.
  • COMMIT TRANSACTION O COMMIT TRAN: confirma el conjunto de operaciones convirtiendo los datos en definitivos. Marca el éxito de la operación de bloque y cierra la transacción.

Los niveles de aislamiento que nos ofrece SQL Server son:

  • SERIALIZABLE: No se permitirá a otras transacciones la inserción, actualización o borrado de datos utilizados por nuestra transacción. Los bloquea mientras dura la misma.
  • REPEATABLE READ: Garantiza que los datos leídos no podrán ser cambiados por otras transacciones, durante esa transacción.
  • READ COMMITED: Una transacción no podrá ver los cambios de otras conexiones hasta que no hayan sido confirmados o descartados.
  • READ UNCOMMITTED: No afectan los bloqueos producidos por otras conexiones a la lectura de datos.
  • SNAPSHOT: Los datos seleccionados en la transacción se verán tal y como estaban al comienzo de la transacción, y no se tendrán en cuenta las actualizaciones que hayan sufrido por la ejecución de otras transacciones simultáneas.

Una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, es decir, si falla una operación Transact SQL fallan todas. Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos.

La transacción más simple en SQL Server es una única sentencia SQL. una transacción ‘autocommit’, una transacción autocompletada.

UPDATE clientes SET sexo='F' WHERE sexo ='FEMENINO'

Cuando enviamos esta sentencia al SQL Server se escribe en el fichero de transacciones lo que va a ocurrir y a continuación realiza los cambios necesarios en la base de datos. Si hay algún tipo de problema al hacer esta operación el SQL Server puede leer en el fichero de transacciones lo que se estaba haciendo y si es necesario puede devolver la base de datos al estado en el que se encontraba antes de recibir la sentencia.

Por supuesto este tipo de transacciones no requieren de nuestra intervención puesto que el sistema se encarga de todo. Sin embargo si hay que realizar varias operaciones y queremos que sean tratadas como una unidad tenemos que crear esas transacciones de manera explícita.

Sentencias

La sentencia que se utiliza para indicar el comienzo de una transacción es ‘BEGIN TRAN’. Si alguna de las operaciones de una transacción falla hay que deshacer la transacción en su totalidad para volver al estado inicial en el que estaba la base de datos antes de empezar. Esto se consigue con la sentencia ‘ROLLBACK TRAN’.

Si todas las operaciones de una transacción se completan con éxito hay que marcar el fin de una transacción para que la base de datos vuelva a estar en un estado consistente con la sentencia ‘COMMIT TRAN’.


Ejercicio 1 Para comenzar a utilizar las transacciones

Transacciones en SQL Server


Realizar lo siguiente:

1.- Bajar la base de datos:

https://gist.github.com/jmalarcon/e98d20735d17b3160766c041060d1902

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases

2.- Instalar o restaurar en SQL Server la base de datos.

3.- Realizar el siguiente ejercicio (Cada sentencia que se ejecuta revisamos si se ha producido o no un error, y si detectamos un error ejecutamos el bloque de código que deshace la transacción.)

USE NorthWind
DECLARE @Error int
--Declaramos una variable que utilizaremos para almacenar un posible código de error

BEGIN TRAN
--Iniciamos la transacción
UPDATE Products SET UnitPrice=20 WHERE ProductName ='Chai'
--Ejecutamos la primera sentencia
SET @Error=@@ERROR
--Si ocurre un error almacenamos su código en @Error
--y saltamos al trozo de código que deshara la transacción. Si, eso de ahí es un 
--GOTO, el demonio de los programadores, pero no pasa nada por usarlo
--cuando es necesario
IF (@Error<>0) GOTO TratarError

--Si la primera sentencia se ejecuta con éxito, pasamos a la segunda
UPDATE Products SET UnitPrice=20 WHERE ProductName='Chang'
SET @Error=@@ERROR
--Y si hay un error hacemos como antes
IF (@Error<>0) GOTO TratarError

--Si llegamos hasta aquí es que los dos UPDATE se han completado con 
--éxito y podemos "guardar" la transacción en la base de datos
COMMIT TRAN

TratarError:
--Si ha ocurrido algún error llegamos hasta aquí
If @@Error<>0 THEN
	BEGIN
	PRINT 'Ha ecorrido un error. Abortamos la transacción'
	--Se lo comunicamos al usuario y deshacemos la transacción
	--todo volverá a estar como si nada hubiera ocurrido
	ROLLBACK TRAN
	END
		

CREATE TABLE Test (Columna int)
GO
BEGIN TRAN TranExterna -- @@TRANCOUNT ahora es 1
   SELECT 'El nivel de anidamiento es', @@TRANCOUNT
   INSERT INTO Test VALUES (1)
   BEGIN TRAN TranInterna1 -- @@TRANCOUNT ahora es 2.
      SELECT 'El nivel de anidamiento es', @@TRANCOUNT
      INSERT INTO Test VALUES (2)
      BEGIN TRAN TranInterna2 -- @@TRANCOUNT ahora es 3.
            SELECT 'El nivel de anidamiento es', @@TRANCOUNT
            INSERT INTO Test VALUES (3)
      COMMIT TRAN TranInterna2 -- Reduce @@TRANCOUNT a 2.
      -- Pero no se guarda nada en la base de datos.
      SELECT 'El nivel de anidamiento es', @@TRANCOUNT
   COMMIT TRAN TranInterna1 -- Reduce @@TRANCOUNT a 1.
   -- Pero no se guarda nada en la base de datos.
   SELECT 'El nivel de anidamiento es', @@TRANCOUNT
COMMIT TRAN TranExterna -- Reduce @@TRANCOUNT a 0.
-- Se lleva a cabo la transacción externa y todo lo que conlleva.
SELECT 'El nivel de anidamiento es', @@TRANCOUNT
SELECT * FROM Test

BEGIN TRAN TranExterna -- @@TRANCOUNT ahora es 1
   SELECT 'El nivel de anidamiento es', @@TRANCOUNT
   INSERT INTO Test VALUES (1)
   BEGIN TRAN TranInterna1 -- @@TRANCOUNT ahora es 2.
      SELECT 'El nivel de anidamiento es', @@TRANCOUNT
      INSERT INTO Test VALUES (2)
      BEGIN TRAN TranInterna2 -- @@TRANCOUNT ahora es 3.
            SELECT 'El nivel de anidamiento es', @@TRANCOUNT
            INSERT INTO Test VALUES (3)
      ROLLBACK TRAN --@@TRANCOUNT es 0 y se deshace 
      --la transacción externa y todas las internas
      SELECT 'El nivel de anidamiento es', @@TRANCOUNT
SELECT * FROM Test

La transacción sigue activa hasta que emita una instrucción COMMIT o ROLLBACK. Una vez que la primera transacción se ha confirmado o revertido, se inicia automáticamente una nueva transacción la siguiente vez que la conexión ejecuta una instrucción para modificar datos.

Transacciones anidadas

Podemos anidar varias transacciones. Cuando anidamos varias transacciones la instrucción COMMIT afectará a la última transacción abierta, pero ROLLBACK afectará a todas las transacciones abiertas.

Un hecho a tener en cuenta, es que, si hacemos ROLLBACK de la transacción superior se desharan también los cambios de todas las transacciones internas, aunque hayamos realizado COMMIT de ellas.

Puntos de recuperacion (SavePoint)

Los puntos de recuperación (SavePoints) permiten manejar las transacciones por pasos, pudiendo hacer rollbacks hasta un punto marcado por el savepoint y no por toda la transacción.

Ejemplo:

 

Tags: