Transaccions
Llicència: CC BY-NC-SA 4.0
(Reconeixement - NoComercial - CompartirIgual) 🅭
Introducció
Cita
"An SQL-transaction (transaction) is a sequence of executions of SQL-statements that is atomic with respect to recovery.
That is to say: either the execution result is completely successful, or it has no effect on any SQL-schemas or SQL-data."
— The SQL Standard
Una transacció és un conjunt d'operacions que es consideren com una unitat atòmica.
Això vol dir que o bé totes les operacions s'executen amb èxit, o bé no es fa cap canvi a la base de dades.
Documentació
Documentació oficial sobre transaccions:
Propietats de les transaccions
Les transaccions tenen quatre propietats importants, conegudes com a ACID
(Atomicity, Consistency, Isolation, Durability).
-
Atomicitat:
El SGBD ha de garantir que, o són executades totes les tasques que
intervenen en una transacció, o bé no se n'executa cap.
-
Consistència:
La base de dades ha d'estar en un estat quan acaba la transacció que ha
de ser coherent amb l'estat que tenia abans de començar la transacció.
Una transacció no pot trencar les restriccions d'integritat de la base de
dades.
-
Aïllament:
L'aïllament es refereix a l'habilitat de les aplicacions de fer que les operacions
en una transacció estiguin aïllades de totes les altres operacions.
Això vol dir que cap operació externa a la transacció podrà veure
les dades modificades dins de una transacció que encara no ha acabat.
-
Definitivitat:
La definitivitat o durabilitat es refereix a la garantia
que una vegada que una transacció s'ha efectuat amb èxit,
els canvis produïts persistiran a la base de dades.
START TRANSACTION
La sentència START TRANSACTION s'utilitza per a iniciar una transacció.
A partir d'aquest punt, totes les sentències DML (Data Manipulation Language)
(INSERT, UPDATE i DELETE) que s'executen no tindran un efecte inmediat sobre la base de dades.
La sentència START TRANSACTION deshabilita el mode autocommit,
que indica si les sentències DML s'han de fer efectives immediatament o no.
-- Deshabilitar autocommit
SET autocommit = 0;
Documentació
Documentació oficial sobre START TRANSACTION:
Té la següent sintaxi:
START TRANSACTION;
-- Sentències SQL dins de la transacció
COMMIT
La sentència COMMIT s'utilitza per a finalitzar una transacció amb èxit,
aplicant tots els canvis efectuats dins de la transacció a la base de dades
i fent-los permanents.
Documentació
Documentació oficial sobre COMMIT:
Té la següent sintaxi:
START TRANSACTION;
-- Sentències SQL dins de la transacció
COMMIT; -- Aplica tots els canvis i acaba la transacció
ROLLBACK
La sentència ROLLBACK s'utilitza per a finalitzar una transacció sense èxit,
descartant tots els canvis efectuats dins de la transacció, que no s'aplicaran
a la base de dades.
Documentació
Documentació oficial sobre ROLLBACK:
Té la següent sintaxi:
START TRANSACTION;
-- Sentències SQL dins de la transacció
ROLLBACK; -- Descarta tots els canvis i acaba la transacció
SAVEPOINT
La sentència SAVEPOINT s'utilitza per a crear un punt de seguretat dins d'una transacció.
En cas que es produisca un error, es pot fer un ROLLBACK fins a aquest punt.
El motor InnoDB de MySQL/MariaDB suporta les operacions:
SAVEPOINT: per a crear un punt de seguretat.ROLLBACK TO SAVEPOINT: per a desfer els canvis fins a un punt de seguretat.RELEASE SAVEPOINT: per a eliminar un punt de seguretat.
La sentència ROLLBACK sense un punt de seguretat desfarà tots els canvis de la transacció.
En canvi, ROLLBACK TO SAVEPOINT desfarà els canvis fins a un punt de seguretat concret
i esborra tots els punts de seguretat posteriors a aquest.
Documentació
Documentació oficial sobre SAVEPOINT:
Té la següent sintaxi:
START TRANSACTION;
-- Sentències SQL dins de la transacció
SAVEPOINT nom_punt; -- Crea un punt de seguretat
-- Sentències SQL dins de la transacció
ROLLBACK TO SAVEPOINT nom_punt; -- Desfer els canvis fins a un punt de seguretat
RELEASE SAVEPOINT nom_punt; -- Eliminar un punt de seguretat
ROLLBACK; -- Descarta tots els canvis i acaba la transacció
Exemple
Informació
En aquest exemple, es crea la base de dades banc amb una taula compte_bancari.
CREATE DATABASE IF NOT EXISTS banc;
USE banc;
CREATE TABLE IF NOT EXISTS compte_bancari (
id INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(100) NOT NULL,
saldo DECIMAL(10, 2) NOT NULL
);
INSERT INTO compte_bancari (nom, saldo) VALUES
('Pep', 1000),
('Maria', 2000),
('Mar', 3000);
Exemple
Es realitza una transferència de 100€ de l'usuari Pep a l'usuari Maria
mitjançant una transacció.
Per observar el comportament de les transaccions,
s'utilitzaran dues sessions simultànies.
Advertència
Per poder connectar-se a la base de dades en dues sessions diferents simultànies,
heu d'executar MySQL Workbench dues vegades.
Per fer-ho, cal que modifiqueu la configuració de MySQL Workbench per a permetre
múltiples instàncies. Això es pot fer des de Edit > Preferences > Others > Allow multiple instances.
-
Sessió 1
START TRANSACTION;
UPDATE compte_bancari
SET saldo = saldo - 100
WHERE id = 1;
SAVEPOINT diners_retirats;
UPDATE compte_bancari
SET saldo = saldo + 100
WHERE id = 2;
COMMIT;
-
Sessió 2
-- A l'inici de la transacció
SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom | saldo |
+----+-------+-------+
| 1 | Pep | 1000 |
| 2 | Maria | 2000 |
+----+-------+-------+
-- En el punt de seguretat 'diners_retirats'
SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom | saldo |
+----+-------+-------+
| 1 | Pep | 1000 |
| 2 | Maria | 2000 |
+----+-------+-------+
-- Al final de la transacció
SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom | saldo |
+----+-------+-------+
| 1 | Pep | 900 |
| 2 | Maria | 2100 |
+----+-------+-------+
Es pot observar que, fins que no s'executa el COMMIT a la sessió 1,
els canvis no es fan efectius a la base de dades i no es poden veure des de la sessió 2.
Bibliografia
📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent
a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
