Entre las habilidades de todo Sistema Gestor de
Bases de Datos
Relaciones tiene que estar la de permitir al programador crear
transacciones. El SQL Server nos
permite trabajar con transacciones de manera sencilla y
eficaz.
Una transacción es un conjunto de operaciones que
van a ser tratadas como una única unidad. Estas
transacciones deben cumplir 4 propiedades fundamentales
comúnmente conocidas como ACID (atomicidad, coherencia,
asilamiento y durabilidad).
La transacción más simple en SQL Server
es una única sentencia SQL. Por ejemplo una sentencia como
esta:
UPDATE Products SET UnitPrice=20 WHERE ProductName
=’Chai’
Es una transacción.
Esta es una transacción
‘autocommit’, una transacción
autocompletada.
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 para una
transacción
Como decíamos una transacción es un
conjunto de operaciones tratadas como una sola. Este conjunto de
operaciones debe marcarse como transacción para que todas
las operaciones que la conforman tengan éxito o
todas fracasen.
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’.
Trabajaremos con la base de datos Northwind en
nuestros ejemplos.
Vamos a realizar una transacción que modifica
el precio de dos
productos de
la base de datos.
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
Como se puede ver para cada sentencia que se ejecuta
miramos si se ha producido o no un error, y si detectamos un
error ejecutamos el bloque de código que deshace la
transacción.
Hay una interpretación incorrecta en cuanto al
funcionamiento de las transacciones que esta bastante extendida.
Mucha gente cree que si tenemos varias sentencias dentro de una
transacción y una de ellas falla, la transacción se
aborta en su totalidad.
¡Nada más lejos de la
realidad!
Si tenemos dos sentencias dentro de una
transacción.
USE NorthWind
BEGIN TRAN
UPDATE Products SET UnitPrice=20 WHERE
ProductName=’Chang’
UPDATE Products SET UnitPrice=20 WHERE
ProductName=’Chang’
COMMIT TRAN
Estas dos sentencias se ejecutarán como una
sola. Si por ejemplo en medio de la transacción
(después del primer update y antes del segundo) hay un
corte de electricidad,
cuando el SQL Server se recupere se encontrará en medio de
una transacción y, o bien la termina o bien la deshace,
pero no se quedará a medias.
El error está en pensar que si la
ejecución de la primera sentencia da un error se
cancelará la transacción. El SQL Server sólo
se preocupa de ejecutar las sentencias, no de averiguar si lo
hacen correctamente o si la lógica
de la transacción es correcta. Eso es cosa
nuestra.
Por eso en el ejemplo que tenemos más arriba
para cada sentencia de nuestro conjunto averiguamos si se ha
producido un error y si es así actuamos en consecuencia
cancelando toda la operación.
Otra de las posibilidades que nos ofrece el SQL
Server es utilizar transacciones anidadas.
Esto quiere decir que podemos tener transacciones
dentro de transacciones, es decir, podemos empezar una nueva
transacción sin haber terminado la
anterior.
Asociada a esta idea de anidamiento existe una
variable global @@TRANCOUNT que tiene valor 0 si no
existe ningún nivel de anidamiento, 1 si hay una
transacción anidada, 2 si estamos en el segundo nivel de
anidamiento… y así sucesivamente.
La dificultad de trabajar con transacciones anidadas
está en el comportamiento
que tienen ahora las sentencias ‘COMMIT TRAN’ y
‘ROLLBACK TRAN’
ROLLBACK TRAN: Dentro de una transacción
anidada esta sentencia deshace todas las transacciones internas
hasta la instrucción BEGIN TRANSACTION más
externa.
COMMIT TRAN: Dentro de una transacción
anidada esta sentencia únicamente reduce en 1 el valor de
@@TRANCOUNT, pero no "finaliza" ninguna transacción ni
"guarda" los cambios. En el caso en el que @@TRANCOUNT=1 (cuando
estamos en la última transacción) COMMIT TRAN hace
que todas las modificaciones efectuadas sobre los datos desde el
inicio de la transacción sean parte permanente de la base
de datos, libera los recursos
mantenidos por la conexión y reduce @@TRANCOUNT a
0.
Quizás estos dos gráficos nos ayuden a entender el
comportamiento de estas sentencias cuando hay varios niveles de
anidamiento
Comportamiento del COMMIT TRAN
Comportamiento de ROLLBACK TRAN
Como siempre un ejemplo es lo mejor para entender como
funciona.
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
Por cierto que lo de usar nombre para las transacciones
es por claridad, puesto que COMMIT TRAN como ya hemos dicho
solamente reduce en 1 el valor de @@TRANCOUNT.
Veamos ahora un ejemplo de transacción anidada
con ROLLBACK TRAN
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
En este caso no se inserta nada puesto que el ROLLBACK
TRAN deshace todas las transacciones dentro de nuestro
anidamiento hasta la transacción más externa y
además hace @@TRANCOUNT=0
¿Supone este funcionamiento asimétrico del
COMMIT y del ROLLBACK un problema?
Pues la verdad es que no. La manera de tratar las
transacciones por el SQL Server es la que nos permite programar
de manera natural los anidamientos.
De todos modos, si queremos ir un poco más lejos
hay una cuarta sentencia para trabajar con transacciones: SAVE
TRAN
Esta sentencia crea un punto de almacenamiento
dentro de una transacción. Esta marca sirve para
deshacer una transacción en curso sólo hasta ese
punto. Por supuesto nuestra transacción debe continuar y
terminar con un COMMIN TRAN (o los que hagan falta) para que todo
se guarde o con un ROLLBACK TRAN para volver al estado previo al
primer BEGIN TRAN.
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)
SAVE TRAN Guadada
BEGIN TRAN TranInterna2 — @@TRANCOUNT ahora es
3.
SELECT 'El nivel de anidamiento es',
@@TRANCOUNT
INSERT INTO Test VALUES (3)
ROLLBACK TRAN Guadada — se deshace lo hecho el punto
guardado.
SELECT 'El nivel de anidamiento es',
@@TRANCOUNT
–Ahora podemos decidir si la transacción se
lleva a cabo
–o se deshace completamente
–Para deshacerla un ROLLBACK bastará como
hemos visto
–Pero para guardar la transacción hace falta
reducir @@TRANCOUNT a 0
COMMIT TRAN TranInterna1 — Reduce @@TRANCOUNT a
2.
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
Si no ponemos el nombre del punto salvado con SAVE
TRAN al hacer un ROLLBACK TRAN se deshace la transacción
más externa y @@TRANCOUNT se pone a 0.
Como podemos ver el uso de transacciones no es
complicado, e incluso las transacciones anidadas si se tratan con
cuidado son fáciles de manejar. Como siempre si hay alguna
duda la mejor fuente de ejemplos y soluciones son
los BOL del SQL Server.
http://www.microsoft.com/sqlserver
BOL del SQL Server
Autor:
Cesar Manivesa