Una transacción en SQL Server es una grupo secuencial de declaraciones o consultas para realizar tareas únicas o múltiples en una base de datos. Cada transacción puede tener operaciones únicas de lectura, escritura, actualización o eliminación o una combinación de todas estas operaciones. Cada transacción debe suceder dos cosas en SQL Server:
- O todas las modificaciones son exitosas cuando se confirma la transacción.
- O bien, todas las modificaciones se deshacen cuando se revierte la transacción.
Una transacción no puede tener éxito hasta que se completen todas las operaciones del conjunto. Significa que si algún argumento falla, la operación de transacción fallará. Cada transacción comienza con la primera instrucción SQL ejecutable y finaliza cuando encuentra una confirmación o una reversión, ya sea explícita o implícitamente. Utiliza el COMPROMETERSE o RETROCEDER declaraciones explícitamente, así como implícitamente cuando se utiliza una declaración DDL.
La siguiente representación gráfica explica el proceso de transacción:
El siguiente ejemplo explicará el concepto de transacción:
recorrido en orden
Este ejemplo utilizará el sistema de base de datos bancaria para explicar el concepto de transacción. Supongamos que un cliente de un banco quiere retirar dinero de su cuenta utilizando el modo cajero automático. El cajero automático puede realizar esta operación en los tres pasos:
- El primer paso es verificar la disponibilidad del monto solicitado en la cuenta.
- El segundo paso deduce el monto de la cuenta si el monto está disponible y luego actualiza el saldo de la cuenta.
- El tercer paso es escribir la operación de retiro de dinero en el archivo de registro. Este paso escribe acerca de que la transacción fue exitosa o fallida. Si tiene éxito, escriba la modificación de datos en la base de datos. De lo contrario, la transacción volverá a su estado anterior.
El principio básico detrás de las transacciones es que si una de las declaraciones devuelve un error, todo el conjunto de cambios se revierte para garantizar la integridad de los datos. Y si las transacciones tienen éxito, todos los cambios serán permanentes en la base de datos. De esta forma, si hay un corte de luz u otros problemas a la hora de retirar dinero de un cajero automático, las transacciones garantizan que nuestro saldo se mantenga constante. Una declaración de transacción realiza mejor estas operaciones porque las cuatro propiedades clave de la transacción hacen que todas las operaciones sean más precisas y consistentes. Las cuatro propiedades de la transacción se denominan ACID.
Propiedades de transacción
Las propiedades de transacción se conocen como propiedad ACID (Atomicidad, Consistencia, Aislamiento, Durabilidad), que se analizan en detalle a continuación:
Atomicidad: Esta propiedad garantiza que todas las declaraciones u operaciones incluidas en la transacción se realicen correctamente. De lo contrario, se cancelará toda la transacción y todas las operaciones volverán a su estado anterior cuando falle alguna operación.
Consistencia: Esta propiedad garantiza que la base de datos cambie de estado sólo cuando una transacción se confirme correctamente. También es responsable de proteger los datos contra fallas.
Aislamiento: Esta propiedad garantiza que todas las transacciones estén aisladas de otras transacciones, lo que significa que cada operación de la transacción se realiza de forma independiente. También garantiza que las declaraciones sean transparentes entre sí.
Durabilidad: Esta propiedad garantiza que el resultado de las transacciones confirmadas persista en la base de datos de forma permanente incluso si el sistema falla o falla.
Modos de transacción en SQL Server
Hay tres modos de transacción diferentes que SQL Server puede utilizar:
Modo de transacción de confirmación automática: Es el modo de transacción predeterminado de SQL Server. Evaluará cada declaración SQL como una transacción y los resultados se confirmarán o revertirán en consecuencia. Por lo tanto, las declaraciones exitosas se confirman inmediatamente, mientras que las declaraciones fallidas se revierten inmediatamente.
Modo de transacción implícita. Este modo permite que SQL Server comience la transacción implícita para cada declaración DML, pero requiere explícitamente el uso de comandos de confirmación o reversión al final de las declaraciones.
Modo de transacción explícita: Este modo lo define el usuario y nos permite identificar exactamente los puntos de inicio y fin de una transacción. Abortará automáticamente en caso de un error fatal.
Control de transacciones
Los siguientes son los comandos utilizados para controlar las transacciones:
NOTA: Solo podemos usar declaraciones DML (INSERT, UPDATE y DELETE) para los comandos del lenguaje de control de transacciones. No podemos usarlos al crear o eliminar tablas porque estas operaciones se confirman en la base de datos automáticamente.
Estado de la transacción
Indica cómo van las transacciones durante su vida. Describe el estado actual de la transacción y cómo se procesará en el futuro. Estos estados definen las reglas que determinan si una transacción se confirma o se aborta.
Describamos los estados de cada transacción en SQL Server:
Estado activo: La transacción está en un estado activo mientras se ejecutan las instrucciones de la transacción. Cambia a la 'Estado parcialmente comprometido' si todas las operaciones de 'lectura y escritura' se completan sin errores. Si alguna instrucción falla, cambia al 'estado fallido'.
Parcialmente comprometido: Cuando se completan todas las operaciones de lectura y escritura, el cambio se realiza en la memoria principal o el búfer local. El estado iría a 'estado comprometido' si los cambios se hacen permanentes en la base de datos. De lo contrario, pasa al 'estado fallido'.
Estado fallido: Una transacción pasa al estado fallido cuando falla alguna instrucción de transacción o falla una modificación permanente en la base de datos.
Estado abortado: La transacción pasa de un 'estado fallido' a una 'estado abortado' cuando ocurre cualquier tipo de falla. Los cambios se eliminan o revierten porque estos cambios solo se realizan en el búfer local o la memoria principal en estados anteriores.
Estado comprometido: Una transacción se completa y pasa a este estado cuando los cambios se hacen permanentes en la base de datos y finalizan en el 'estado terminado'.
Estado terminado: Si no hay reversión y la transacción está en el 'estado comprometido' el sistema es consistente y está listo para una nueva transacción mientras se finaliza la anterior.
Implementación de Transacción en SQL Server
Tomemos algunos ejemplos para comprender cómo podemos implementar la transacción en SQL Server. Aquí usaremos el 'Producto' tabla para demostrar todos los estados de transacción.
Los siguientes scripts SQL crean la tabla Producto en la base de datos seleccionada:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
A continuación, ejecute los siguientes scripts para insertar datos en esta tabla:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Ejecute la instrucción SELECT para verificar los datos:
Ejemplo de transacción COMMIT
Es una buena idea dividir las declaraciones SQL utilizadas en la transacción en varias partes lógicas. Y luego, podemos decidir si confirmamos o revertimos los datos. Los siguientes pasos ilustran cómo crear una transacción:
- Inicie la transacción utilizando el COMENZAR LA TRANSACCIÓN dominio.
- Escribir las sentencias SQL y dividirlas según nuestras necesidades.
- Utilizar el COMPROMETERSE declaración para completar la transacción y guardar los cambios permanentemente.
A continuación se muestran los comandos que explican las operaciones COMMIT en SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Si no se encuentra ningún error, veremos el siguiente resultado donde cada declaración SQL de transacción se ejecuta de forma independiente:
Las declaraciones INSERT y UPDATE no se pueden revertir una vez confirmada la transacción. Cuando verificamos la tabla después de la operación de confirmación, veremos los siguientes datos:
Ejemplo de transacción ROLLBACK
Usaremos el comando ROLLBACK para deshacer cualquier transacción que aún no se haya guardado en la base de datos y regresar al punto donde comenzó la transacción. El siguiente ejemplo explica la operación ROLLBACK en SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Una vez que ejecutamos la transacción anterior, podemos ver que se ejecutará con éxito. Sin embargo, no afectará ningún cambio en la base de datos porque hasta que no ejecutemos la instrucción COMMIT o ROLLBACK, los cambios no pueden volverse permanentes. Por lo tanto, tenemos la opción de utilizar el comando de transacción ROLLBACK para revertir todas las operaciones de la base de datos. Aquí está el estado de cuenta completo de la transacción:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Uso de la variable @@Error Global en transacciones:
Esta variable es Se utiliza para comprobar si hay un error o no. El siguiente ejemplo explica su concepto. Aquí primero iniciaremos la transacción usando el comando BEGIN y luego escribiremos dos declaraciones de inserción. A continuación, usaremos la variable del sistema global. @@ERROR en el declaración SI para comprobar un error. Si el valor es mayor que 0, significa que hay algún error. Ahora, la transacción se revierte; de lo contrario, la transacción se confirma.
gimp guardando como jpeg
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Cuando se ejecute la transacción anterior, notaremos que se ha revertido. Se debe a nuestro intento de insertar un valor duplicado en la columna Clave principal.
Transacción de reversión automática
La mayoría de las transacciones contienen más de una consulta. Mientras se ejecuta la transacción, si alguna de las declaraciones SQL produce un error, no se producen modificaciones en la base de datos y las declaraciones restantes no se ejecutan. Este concepto se conoce como transacción de reversión automática en SQL Server. Usemos un ejemplo simple para demostrar este proceso.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Esta transacción produce el siguiente resultado:
En este resultado, podemos ver que la declaración de inserción se ejecutó correctamente. Sin embargo, al ejecutar la declaración de actualización se encontró un error debido al problema de conversión del tipo de datos. En este caso, SQL Server no permite ningún cambio en la base de datos, lo que significa que la operación de inserción no agrega ningún valor y la instrucción de selección no se ejecuta.
Punto de guardado en transacciones
El punto de guardado inserta una marca especial en una transacción que nos permite deshacer todos los cambios ejecutados después del punto de guardado. También se utiliza para revertir cualquier parte específica de la transacción en lugar de toda la transacción. Podemos definirlo usando el GUARDAR TRANSACCIÓN sp_name declaración. El siguiente ejemplo explicará el uso del punto de guardado en transacciones que confirman la declaración de inserción y revierten la declaración de eliminación.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Vea el resultado a continuación donde podemos ver que la identificación del producto 116 se elimina y 117 se inserta en la primera salida. Sin embargo, en el segundo resultado, la operación de eliminación se revierte debido al punto de guardado.
¿Cómo liberar un punto de guardado en una transacción?
El punto de guardado de liberación se utiliza para eliminar el punto de guardado nombrado de la transacción actual sin revertir los resultados de las consultas ejecutadas después del punto de guardado. MySQL tiene este comando, pero SQL Server no proporciona ningún comando para liberar un punto de guardado. En cambio, se liberan automáticamente al final de una transacción de confirmación o reversión, por lo que no tenemos que preocuparnos por ellos de forma intermedia.
Transacción implícita en SQL Server
Podemos definir una transacción implícita habilitando la opción IMPLICIT_TRANSACTIONS. El siguiente ejemplo explicará este concepto fácilmente:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
En esta transacción, hemos utilizado dos opciones. @@OPCIÓN y @@TRANCOUNT. @@OPTOPN proporciona información sobre las opciones SET actuales y @@TRANCOUNT proporciona la instrucción BEGIN TRANSACTION en la sesión actual.
Ahora, al ejecutar la transacción se obtendrá el siguiente resultado:
inteligencia artificial y agentes inteligentes
Transacción explícita en SQL Server
Se debe definir una transacción explícita mediante el comando BEGIN TRANSACTION porque identifica el punto de inicio de la transacción explícita. Podemos definir la transacción explícita en SQL Server de la siguiente manera:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
En la sintaxis, la opción trans_name indica un nombre único de una transacción. El @trans_name_var indica una variable definida por el usuario que almacena el nombre de la transacción. Finalmente, el MARCA La opción nos permite marcar una transacción específica en el archivo de registro.
La transacción explícita a través del comando BEGIN TRANSACTION adquirió un bloqueo dependiendo del nivel de aislamiento de los recursos relacionados con la transacción. Ayuda a reducir los problemas de bloqueo. Vea el siguiente ejemplo:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Aquí está el resultado:
Transacción marcada en SQL Server
La transacción marcada se utiliza para agregar una descripción a una transacción específica en los archivos de registro. Podemos usarlo como punto de recuperación en lugar de una fecha y hora al restaurar una base de datos a un estado anterior. Debemos saber que la marca se agrega en los archivos de registro solo cuando la transacción marcada modifica la base de datos. Podemos entender su concepto usando el siguiente ejemplo.
Supongamos que hemos modificado la base de datos accidentalmente y no sabemos el momento exacto de la alteración de los datos; en ese caso, la recuperación de datos puede llevar mucho tiempo. Sin embargo, si utilizamos las transacciones marcadas, puede ser una herramienta útil para determinar el momento exacto de las modificaciones de los datos.
La siguiente sintaxis ilustra la transacción marcada en SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Aquí debemos definir el nombre de la transacción y luego agregar la opción CON MARCA. En el siguiente ejemplo, eliminaremos registros y agregaremos la marca en el archivo de registro:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
El historia de la marca de registro La tabla está incluida en el base de datos msdb y almacena información sobre cada transacción marcada que se ha cometido. Ejecute la siguiente declaración para obtener los detalles de la tabla logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Transacción con nombre en SQL Server
También podemos proporcionar un nombre para nuestra transacción en SQL Server. Siempre se recomienda utilizar la transacción nombrada cuando se trabaja con muchas transacciones en una sola consulta. El siguiente ejemplo explica cómo cambiar el nombre de una transacción:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Aquí está el resultado:
Conclusión
Este artículo brindará una descripción completa de la transacción en declaraciones de SQL Server. Las transacciones son útiles en los sistemas de bases de datos relacionales porque garantizan la integridad de la base de datos.