<div class="page"> <div class="cover text-center"> <img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo"> # Transaccions <div class="text-end fit-content ms-auto my-3 mt-auto pt-3"> <p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p> <p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p> <p><strong>Curs:</strong> 2023/2024</p> </div> <div> <p class="fw-bold mb-0">Llicència: BY-NC-SA</p> <p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p> <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank"> <img class="mx-auto" src="/itb/images/license.png" alt="Licence"/> </a> </div><!--license--> </div><!--cover--> </div><!--page--> {:toc} ## Introducció ::: quote _"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. ::: docs Documentació oficial sobre transaccions: - MariaDB: https://mariadb.com/kb/en/transactions/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/commit.html ::: ## 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. ```sql -- Deshabilitar autocommit SET autocommit = 0; ``` ::: docs Documentació oficial sobre `START TRANSACTION`: - MariaDB: https://mariadb.com/kb/en/start-transaction/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/commit.html ::: Té la següent sintaxi: ```sql 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. ::: docs Documentació oficial sobre `COMMIT`: - MariaDB: https://mariadb.com/kb/en/commit/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/commit.html ::: Té la següent sintaxi: ```sql 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. ::: docs Documentació oficial sobre `ROLLBACK`: - MariaDB: https://mariadb.com/kb/en/rollback/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/commit.html ::: Té la següent sintaxi: ```sql 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. ::: docs Documentació oficial sobre `SAVEPOINT`: - MariaDB: https://mariadb.com/kb/en/savepoint/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/savepoint.html ::: Té la següent sintaxi: ```sql 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 ::: info En aquest exemple, es crea la base de dades `banc` amb una taula `compte_bancari`. ```sql 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); ``` ::: :::: example 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. ::: warning 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`. ::: <div class="row row-cols-1 row-cols-md-2"> <div class="col"> - __Sessió 1__ ```sql 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; ``` </div> <div class="col"> - __Sessió 2__ ```sql -- 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 | +----+-------+-------+ ``` </div> </div> 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 - https://ca.wikipedia.org/wiki/ACID