TRANSACCIONES EN SQL SERVER
I.
TRANSACCIONES EN SQL SERVER:
1.
DEFINICIÓN:
Es una
unidad única de trabajo. 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.
Es
considerado como un grupo de una o varias instrucciones de base de datos
totalmente confirmadas o totalmente revertidas. Cada transacción es atómica,
coherente, aislada y durable (ACID).
El
principio y el final de las transacciones dependen de la configuración de
AUTOCOMMIT y de las instrucciones BEGIN TRANSACTION, COMMIT y ROLLBACK.
2.
PROPIEDADES
ACID:
v Atomicidad: Las
operaciones que componen una transacción deben considerarse como una sola.
Una
transacción es una unidad de trabajo en la que se produce una serie de
operaciones entre las instrucciones BEGIN TRANSACTION y END TRANSACTION de una
aplicación. Una transacción se ejecuta exactamente una vez y tiene carácter
"atómico" (de subdivisión), es decir, el trabajo se realiza en su
totalidad o no se realiza en ningún caso.
Las
operaciones asociadas a una transacción comparten normalmente un objetivo común
y son interdependientes. Si el sistema ejecutase únicamente una parte de las
operaciones, podría poner en peligro el objetivo final de la transacción. La
atomicidad elimina la posibilidad de procesar un subconjunto de operaciones.
v Consistencia: Una
operación nunca deberá dejar datos inconsistentes.
Una
transacción es una unidad de integridad porque mantiene la coherencia de los
datos, transformando un estado coherente de datos en otro estado de datos
igualmente coherente.
Se
requiere que los datos enlazados mediante una transacción se mantengan en
términos de semántica. Una parte de la responsabilidad para mantener la
coherencia recae en el programador de la aplicación que debe asegurarse de que
ésta exija todas las restricciones de integridad conocidas.
v Aislamiento: Los
datos "sucios" deben estar aislados, y evitar que los usuarios
utilicen información que aún no está confirmada o validada.
El aislamiento requiere que parezca que cada transacción sea la única
que manipula el almacén de datos, aunque se puedan estar ejecutando otras
transacciones al mismo tiempo. Una transacción nunca debe ver las fases
intermedias de otra transacción.
Las transacciones alcanzan el nivel máximo de aislamiento cuando se
pueden serializar. En este nivel, los resultados obtenidos de un conjunto de
transacciones concurrentes son idénticos a los obtenidos mediante la ejecución
en serie de las transacciones.
Como un
alto grado de aislamiento puede limitar el número de transacciones
concurrentes, algunas aplicaciones reducen el nivel de aislamiento en el
intercambio para mejorar el rendimiento.
v Durabilidad: Una
vez completada la transacción los datos actualizados ya serán permanentes y
confirmados.
Si una
transacción se realiza satisfactoriamente, el sistema garantiza que sus
actualizaciones se mantienen, aunque el equipo falle inmediatamente después de
la confirmación. El registro especializado permite que el procedimiento de
reinicio del sistema complete las operaciones no finalizadas, garantizando la
permanencia de la transacción.
ü De Confirmación Automática: El
Gestor de Datos inicia una transacción automáticamente por cada operación que
actualice datos. De este modo mantiene siempre la consistencia de la base de
datos, aunque puede generar bloqueos.
ü Implícitas: Cuando el Gestor de Datos
comienza una transacción automáticamente cada vez que se produce una
actualización de datos, pero el que dicha transacción se confirme o se deshaga,
lo debe indicar el programador.
Se
inicia implícitamente una nueva transacción cuando se ha completado la
anterior, pero cada transacción se completa explícitamente con una instrucción
COMMIT o ROLLBACK.
ü Explícitas: Son las que iniciamos
nosotros "a mano" mediante instrucciones SQL, los programadores son
los que indican qué operaciones va a abarcar.
Cada
transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se
termina explícitamente con una instrucción COMMIT o ROLLBACK.
ü Transacciones de Ámbito de Lote: Una
transacción implícita o explícita de Transact-SQL que se inicia en una sesión
de MARS (conjuntos de resultados activos múltiples), que solo es aplicable a
MARS, se convierte en una transacción de ámbito de lote. Si no se confirma o
revierte una transacción de ámbito de lote cuando se completa el lote, SQL
Server la revierte automáticamente.
4.
COMANDOS:
ü BEGIN TRANSACTION:
Marca
el punto de inicio de una transacción explícita.
Representa
un punto en el que los datos a los que hace referencia una conexión son lógica
y físicamente coherentes. Si se producen errores, se pueden revertir todas las
modificaciones realizadas en los datos después de BEGIN TRANSACTION para
devolver los datos al estado conocido de coherencia.
Cada
transacción dura hasta que se completa sin errores y se emite COMMIT
TRANSACTION para hacer que las modificaciones sean una parte permanente de la
base de datos, o hasta que se produzcan errores y se borren todas las
modificaciones con la instrucción ROLLBACK TRANSACTION.
ü COMMIT
Marca
el final de una transacción explícita o de confirmación automática. Esta
instrucción hace que los cambios en la transacción se confirmen permanentemente
en la base de datos. La instrucción COMMIT es idéntica a COMMIT WORK, COMMIT
TRAN y COMMIT TRANSACTION.
Si la transacción que se ha confirmado era una transacción Transact-SQL
distribuida, COMMIT TRANSACTION hace que MS DTC utilice el protocolo de
confirmación en dos fases para confirmar los servidores involucrados en la
transacción.
Si una transacción local afecta a dos o más bases de datos de la misma
instancia del Motor de base de datos, la instancia utiliza una confirmación
interna en dos fases para confirmar todas las bases de datos involucradas en la
transacción.
ü ROLLBACK
Revierte
una transacción al principio de la misma. No se confirman cambios para la
transacción en la base de datos. La instrucción ROLLBACK es idéntica a ROLLBACK
WORK, ROLLBACK TRAN y ROLLBACK TRANSACTION.
Una
transacción no se puede revertir después de ejecutar una instrucción COMMIT
TRANSACTION, excepto cuando COMMIT TRANSACTION está asociada a una transacción
anidada incluida en la transacción que se revierte. En esta instancia, la
transacción anidada se revierte, incluso si ha emitido una instrucción COMMIT
TRANSACTION para ella.
5.
EJEMPLOS:
a)
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
b)
BEGIN TRAN
-- Primer BEGIN TRAN y
ahora @@TRANCOUNT = 1
BEGIN TRAN
-- Ahora @@TRANCOUNT = 2
COMMIT TRAN
-- Volvemos a @@TRANCOUNT = 1
-- Pero no se guarda nada ni se hacen efectivos los posibles
cambios
COMMIT TRAN
-- Por fin @@TRANCOUNT = 0
-- Si hubiera cambios
pendientes se llevan a la base de datos
-- Y volvemos a un estado
normal con la transacción acabada
c) Uso del COMMIT
BEGIN TRAN
-- Primer BEGIN TRAN y
@@TRANCOUNT = 1
BEGIN TRAN
-- Ahora @@TRANCOUNT = 2
COMMIT TRAN
-- Como antes @@TRANCOUNT = 1
--Y como antes nada se guarda
ROLLBACK TRAN
-- Se cancela TODA la transacción. Recordemos que el
COMMIT
-- de antes no guardo nada, solo redujo @@TRANCOUNT
-- Ahora
@@TRANCOUNT = 0
COMMIT TRAN
-- No vale para nada
porque @@TRANCOUNT es 0 por el efecto del ROLLBACK
d) Uso del ROLLBACK
En cuanto al SAVE TRAN
podemos recordarlo con el siguiente ejemplo:
CREATE TABLE Tabla1 (Columna1
varchar(50))
GO
BEGIN TRAN
INSERT INTO Tabla1 VALUES
('Primer valor')
SAVE TRAN Punto1
INSERT INTO Tabla1 VALUES ('Segundo valor')
ROLLBACK TRAN Punto1
INSERT INTO Tabla1 VALUES ('Tercer valor')
COMMIT TRAN
SELECT * FROM Tabla1
Columna1
--------------------------------------------------
Primer valor
Tercer valor
(2 filas afectadas)
Un
ROLLBACK a un SAVE TRAN no deshace la transacción en curso ni modifica
@@TRANCOUNT, simplemente cancela lo ocurrido desde el 'SAVE TRAN nombre' hasta
su 'ROLLBACK TRAN nombre'
RESUMEN:
Una transacción es una unidad
de trabajo compuesta por diversas tareas, cuyo resultado final debe ser que se
ejecuten todas o ninguna de ellas.
Por regla general en un sistema
de base de datos todas las operaciones relacionadas entre sí que se ejecuten
dentro un mismo flujo lógico de trabajo deben ejecutarse en bloque. De esta
manera si todas funcionan la operación conjunta de bloque tiene éxito, pero si
falla cualquiera de ellas, deberán retrocederse todas las anteriores que ya se
hayan realizado. De esta forma evitamos que el sistema de datos quede en un
estado incongruente.
Si una transacción es válida, se hace. La
instrucción COMMIT garantiza que todas las modificaciones de la transacción se
conviertan en una parte permanente de la base de datos. La instrucción COMMIT
también libera recursos que utiliza la transacción como, por ejemplo, los
bloqueos.
Hay que utilizar COMMIT TRANSACTION solo en el punto
donde todos los datos a los que hace referencia la transacción sean lógicamente
correctos.
Si se produce un error en una
transacción o el usuario decide cancelar la transacción, hay que echar para
tras la transacción. Esto se hace con la instrucción ROLLBACK que devuelve los
datos al estado en que estaban al inicio de la transacción. La instrucción
ROLLBACK también libera los recursos que mantiene la transacción.
SUMMARY:
A
transaction is a unit of work composed of various tasks, the final result of
which must be all or none of them.
As
a general rule, in a database system, all related operations that run within
the same logical workflow must be executed en bloc. In this way, if all of them
work, the joint operation of the block is successful, but if any of them fails,
all previous ones that have already been made must be rolled back. In this way
we avoid that the data system remains in an incongruent state.
If
a transaction is valid, it is done. The COMMIT statement ensures that all
modifications to the transaction become a permanent part of the database. The
COMMIT statement also releases resources that the transaction uses, such as
locks.
You
must use COMMIT TRANSACTION only at the point where all the data referred to by
the transaction is logically correct.
If
an-error occurs in a transaction or the user decides to cancel the transaction,
the transaction must be removed. This is done with the ROLLBACK statement that
returns the data to the state they were in at the beginning of the transaction.
The ROLLBACK statement also releases the resources that the transaction
maintains.
RECOMENDACIONES:
ü Trate de romper las transacciones más grandes en varias transacciones
más pequeñas y luego ejecute estas operaciones por lotes. a que los bloqueos
exclusivos y de actualizaciones se mantienen más tiempo para las grandes
transacciones, demasiadas transacciones grandes pueden bloquear otras
actividades y llevar a un punto muerto.
ü La ejecución de grandes transacciones en lotes ayuda a minimizar los
viajes redondos de la red durante la operación, lo que reduce los retrasos en
la realización de la transacción y la liberación de los bloqueos.
ü Reduzca el tiempo de transacción asegurándose de que usted no está
realizando las mismas lecturas una y otra vez. Si la aplicación tiene que leer
los mismos datos más de una vez, entonces se pueden almacenar en caché los
datos en variables, tablas temporales o variables de tabla.
CONCLUCIONES:
ü
Una transacción es una colección de operaciones que forman una única
unidad lógica de trabajo en una BD realizada por una o más sentencias SQL
estrechamente relacionadas.
ü Una transacción es una unidad
de la ejecución de un programa que lee y escribe datos a y desde la Base de
Datos. Puede consistir en varias operaciones de acceso a la base de datos. Está
delimitada por constructoras como begin-transaction
y end-transaction (SQL-Server).
ü
Una transacción es
un conjunto de comandos, que se está ejecutado completamente o no ejecutado en
absoluto: todo o nada. Por ejemplo, si una suma de dinero fue trasladada de una
cuenta bancaria a otra, y hay que actualizar ambas cuentas sobre el depósito y
la retirada; es obligatorio que ambas cuentas se actualizan juntas, o ninguna
(en caso de que una de las actualizaciones falla); para evitar consecuencias
inconsistentes de un depósito sin ninguna retirada, o viceversa. Por lo tanto,
una transacción es una secuencia de operaciones realizadas como una sola unidad
lógica de trabajo.
APRECIACIÓN DEL EQUIPO:
ü Sql nos ofrece la forma de realizar transferencias vie electrónica o
virtual dichas operaciones son fáciles y confiables siempre y cuando se cumpla
con las propiedades ACID y se manejen los comandos rollback y commit
adecuadamente.
ü El crecimiento de la base de datos o del registro de transacciones en
entornos de producción puede degradar el rendimiento pues todas las
transacciones deben hacer cola y esperar al SQL Server para hacer crecer el
archivo de registro o de datos antes de que pueda comenzar a procesar las
transacciones de nuevo. Esto puede crear un cuello de botella.
GLOSARIO DE TÉRMINOS:
· MARS: Es un conjunto de
resultados activos múltiples que habilita la ejecución intercalada de múltiples
solicitudes dentro de una única conexión. Es decir, permite que se ejecute un
lote y, dentro de su ejecución, permite que se ejecuten otras solicitudes. Sin
embargo, tenga en cuenta que MARS se define en términos de entrelazado, no en
términos de ejecución paralela.
· COMMIT: Es una instrucción que
garantiza que todas las modificaciones de la transacción se conviertan en una
parte permanente de la base de datos. La instrucción COMMIT también libera
recursos que utiliza la transacción como, por ejemplo, los bloqueos.
· COMMIT WORK: Marca
el final de una transacción.
· ROLLBACK: Es un
comando que causa que todos los cambios de datos desde la última sentencia
BEGIN WORK , o START TRANSACTION sean descartados por el sistema de gestión de
base de datos relacional (RDBMS), para que el estado de los datos sea revertida
a la forma en que estaba antes de que aquellos cambios tuvieran.
· MS DTC: Es el proceso que se
encarga del Microsoft Personal Web Server y del Microsoft SQL Server.
· TRANSACCIÓN ANIDADA: Es una
transacción
de bases de datos que se inicia mediante una
instrucción en el ámbito de una transacción ya que ha empezado; es decir, que
podemos empezar una nueva transacción sin haber terminado la anterior.
LINKOGRAFÍA:
ü https://www.campusmvp.es/recursos/post/Fundamentos-de-SQL-Transacciones.aspx
ü https://technet.microsoft.com/es-es/library/ms172353(v=sql.110).aspx
ü http://raknarrok.blogspot.com/2011/03/introduccion-transacciones.html
ü http://myy.haaga-helia.fi/~dbms/dbtechnet/download/SQL-Transactions_handbook_SP.pdf
ü https://es.slideshare.net/LisbethOcaaBueno/transacciones-100536237
Comentarios
Publicar un comentario