Transacciones

Sumario

Teoría: 10 min
Ejercicios: 30 min

Preguntas

• ¿Qué pasa si inserto un registro erróneo? ¿Puedo deshacer?

• ¿Si voy a insertar muchos registros, es mejor insertarlos de una vez (1 transaccion)?

• ¿Como afecta esto a los usuarios de la base de datos?

Objetivos

• Usar transacciones para trabajar a prueba de fallos

Ahora que tenemos un par de duplas insertadas, podemos comprobar lo que es una transacción. De forma resumida, una transacción es una unidad única de trabajo (esta unidad puede representar la inserción de 1 registro o de miles). 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.

Para probar el funcionamiento, trabajaremos con nuestra base de datos de Uniprot. En mi caso, esta contiene:

>SELECT accnumber, description FROM SWISSENTRY;

accnumber   id          
----------  ------------
M3WAS9      M3WAS9_FELCA
A0A1J5SEU6  A0A1J5SEU6_9

>SELECT * FROM ACCNUMBERS;

main_accnumber  accnumber 
--------------  ----------
M3WAS9          M3WAS9    
A0A1J5SEU6      A0A1J5SEU6

Transacción satisfactoria

Ahora, vamos a insertar nuevos datos usando una transacción, si todo es correcto aceptaremos la transacción y los datos se guardarán en la base de datos automáticamente, sabiendo que no habrá ningun problema.

Toda transacción comienza con el comando BEGIN. Posteriormente usaremos comandos SQL para insertar (INSERT), borrar (DELETE) o actualizar (UPDATE) registros. Vamos primero a insertar un dato con algún error y después lo corregiremos:

>BEGIN;

>INSERT INTO SWISSENTRY VALUES ('P23456',"ABCD_JIUMAN","22-OCT-2019", "Unknown protein", "KSVKGTVKKYVPPRLVPVHYDETEAEREKKRLERAR","89");
>SELECT accnumber, description FROM SWISSENTRY;

accnumber   id          
----------  ------------
M3WAS9      M3WAS9_FELCA
A0A1J5SEU6  A0A1J5SEU6_9
P23456      ABCD_JIUMAN

>UPDATE SWISSENTRY SET id='ABCD_HUMAN' where id='ABCD_JIUMAN';
>SELECT accnumber, description FROM SWISSENTRY;

accnumber   id          
----------  ------------
M3WAS9      M3WAS9_FELCA
A0A1J5SEU6  A0A1J5SEU6_9
P23456      ABCD_HUMAN 

>INSERT INTO ACCNUMBERS VALUES ('P23456',"ABCD_HUMAN");

Como todo parece estar correcto y no he tenido ningun problema de síntaxis, debería aceptar los cambios. Pero antes de hacerlo, para comprobar el funcionamiento, abriremos otra terminal y entraremos en la misma base de datos. En esta nueva terminal de SQLite, si hacemos un SELECT de la tabla SWISSENTRY veremos que el registro nuevo no existe. Esto es debido a que está bajo una transacción y hasta que no se finalice, esos datos no son guardados. Como en nuestro caso, todo está bien insertado, aceptaremos los cambios. Para ello escribimos desde la terminal en la que inicamos la transacción:

> COMMIT;

Ahora, tras ejecutar esto, en ambas instancias de la misma base de datos, veremos los mismos registros y hemos evitado que alguien vea nuestro error “JIUMAN” vs “HUMAN”.

Transacción fallida

En este caso vamos a insertar unos datos y forzaremos a que SALite nos de un error, asi gracias al commando ROLLBACK, desharemos estos cambios y la base de datos no se verá afectada. De nuevo empezamos la transacción con BEGIN y proseguimos con sentencias SQL:

>BEGIN;

>INSERT INTO SWISSENTRY (accnumber, id, description, seq, molweight) VALUES ('P234568',"ABCD1_HUMAN", "Unknown protein", "KSVKGTVKKYVPPRLVPVHYDETEAEREKKRLERARETEAEREK",112);
>INSERT INTO ACCNUMBERS VALUES ('P234568',"ABCD1_HUMAN");

#Como podéis ver, al no fijar ninguna fecha de última actualización, según el esquema que hemos creado (.schema SWISSENTRY), se fija automáticamente la fecha de hoy

>INSERT INTO SWISSENTRY (accnumber, id, description, seq) VALUES ('P2345610',"ABCD3_HUMAN", "Unknown protein", "KSVKGTVKKYVPPRLVPVHYDETEAEREKKRLERARETEAEREKRA");

#El campo del peso molecular es obligatorio

>INSERT INTO SWISSENTRY (accnumber, id, description, seq, molweight) VALUES ('P2345610',"ABCD3_HUMAN", "Unknown protein", "KSVKGTVKKYVPPRLVPVHYDETEAEREKKRLERARETEAEREKRA", 114);

>INSERT INTO ACCNUMBERS VALUES ('P2345610',"ABCD3_HUMAN");

>INSERT INTO SWISSENTRY VALUES ('P234568',"ABCD6_HUMAN","18-OCT-2018", "Unknown protein", "KSVKGTVKKYVPPRLVPVHYDETEAEREKKRLERAR",89);

#Este registro contiene un error, porque el accnumber ya está insertado en la base de datos y éste debe ser único (lo es en Uniprot), con lo cual este dato contiene algún error que debe resolverse, así que deshacemos los cambios.

>SELECT accnumber, description FROM SWISSENTRY;
>ROLLBACK;
>SELECT accnumber, description FROM SWISSENTRY;

Hemos intentado insertar tres tuplas con los id, ABCD1_HUMAN, ABCD3_HUMAN y ABCD6_HUMAN. La última parece contener un error porque el accnumber (único en Uniprot), aparece repetido y por ello el constrain de clave única de nuestro esquema nos da un error (Fijaos la importancia de un buen esquema de tabla). Con lo cual deshacemos los cambios usando el comando ROLLBACK y todo lo que estaba en la transcacción se pierde.

No olvidar

• BEGIN inicia una transacción.

• COMMIT cierra la transcacción guardando los cambios

• ROLLBACK cierra la transacción sin guardar los cambios