7. TCL - Transaccions

7.1. Definició

Les transaccions són un concepte fonamental de tots els sistemes de base de dades. Una transacció és un conjunt de sentències INSERT, UPDATE i/o DELETE que constitueixen una operació única. És a dir: podrem fer que s'executen totes les sentències de la transacció o cap.

Les sentències entre el BEGIN i COMMIT es diuen bloc de transacció o transacció.

Nota

Les transaccions no funcionen en el motor MyISAM de MySQL. Cal usar InnoDB.

7.2. Sintaxi

Les sentències que volem que formen part d'una transacció han de començar amb el comandament BEGIN (o START TRANSACTION) i acabar amb COMMIT (per a que es facen efectives les sentències) o ROLLBACK (per a anul·lar les sentències de la transacció):

COMMIT (confirma)ROLLBACK (anul·la)

BEGIN;
    (sentència 1);
    (sentència 2);
    ...
    (sentència N);
COMMIT;

BEGIN;
    (sentència 1);
    (sentència 2);
    ...
    (sentència N);
ROLLBACK;

7.3. Utilitats

1) Si ocorre algun error durant la transacció, podem anul·lar la transacció i així cap dels passos no afectarà la base de dades (es desfan les accions realitzades).

2) Els estats entremitjos en una transacció sí que són visibles en eixa transacció però no en unes altres transaccions concurrents. És a dir, altres sessions no veuen cap canvi d'eixa transacció fins que no s'ha fet el COMMIT.

3) Problema de l'ou i la gallina (inserció en dos taules interdependents).

4) Podem desfer les accions fetes mentre no confirmem la transacció.

5) Podem desfer part de les accions mentre no confirmem la transacció (SAVEPOINT).

Exemple utilitat 1: prevenció d'errors inesperats

Pensem en una base de dades d'un banc que conté saldos dels comptes dels clients. Volem enregistrar un pagament de 100 € des del compte d'Alícia fins al compte de Pep:

UPDATE comptes
    SET saldo = saldo - 100
    WHERE nom = 'Alícia';

UPDATE comptes
    SET saldo = saldo + 100
    WHERE nom = 'Pep';

Este tipus d'operacions sol ser més complicat i implicar més sentències (introduir la transferència en un històric de moviments, etc). Però el que importa és que hi ha més d'una sentència d'actualització per a realitzar esta transferència bancària.

Els clients del nostre banc voldran que se'ls assegure que: o bé estes 2 actualitzacions es fan, o que cap d'elles es fa. No voldrien que el saldo es decrementara d'Alícia i no s'incrementara en Pep.

Necessitem una garantia que si hi ha algun error en alguna d'eixes operacions anteriors, que cap dels passos executats tinga efecte. Agrupar les actualitzacions en una transacció ens dóna esta garantia:

BEGIN;
    UPDATE comptes SET saldo = saldo - 100 WHERE nom = 'Alícia';
    UPDATE comptes SET saldo = saldo + 100 WHERE nom = 'Pep';
COMMIT;

Si després del 1r UPDATE es perdera la connexió amb la BD, no es faria el 2n UPDATE però tampoc el COMMIT. Això fa que els efectes del 1r UPDATE no es facen efectius realment en la base de dades.

Exemple utilitat 2: canvis entremitjos transparents a altres sessions

Una altra propietat important de bases de dades transaccionals està relacionada amb la idea d'actualitzacions atòmiques: quan les transaccions múltiples estan corrent concurrentment (és a dir: simultàniament, al mateix temps), cada una no deu veure els canvis incomplets fets per altres.

Per exemple, si una transacció està ocupada sumant tots els saldos dels comptes, no sumarà els saldos fins que no haja acabat la transacció que està fent el traspàs d'un compte a altre.

SESSIÓ 1	SESSIÓ 2
BEGIN;
UPDATE comptes SET saldo = saldo - 100 WHERE nom = 'Alícia';	SELECT SUM(saldo) FROM comptes
UPDATE comptes SET saldo = saldo + 100 WHERE nom = 'Pep';
COMMIT;

Si la sessió 2 s'executa en el moment en què acaba el primer UPDATE, no veurà els efectes d'eixe UPDATE. Si no haguérem posat la sessió 1 dins d'una transacció, la sessió 2 s'hauria deixat 100 € per sumar, ja que haguera sumat el saldo d'Alícia sense els 100 i el saldo de Pep sense haver-li-ho sumat encara.

Exemple utilitat 3: problema de l'ou i la gallina

Suposem una base de dades d'una acadèmia d'informàtica, on hi ha tants alumnes com ordinadors. Cada alumne ha de tindre assignat un ordinador. I cada ordinador ha de tindre assignat un alumne:

ORDINADORS = codi + carac + alumne

ALUMNES = exp + nom + ordinador

Nota

Este disseny té redundància d'informació, però ens serveix per a l'exemple.

Per a introduir un ordinador deurà tindre assignat un alumne, però per a introduir un alumne deurà tindre assignat un ordinador.

Problema: el SGBD no ens deixarà inserir res (problema de l'ou i la gallina).

Solució: en la definició de les claus alienes, hem de posar la propietat DEFERRABLE, que farà que les comprovacions d'existència es realitzen en el moment en què acabe la transacció. Però! MySQL no admet el DEFERRABLE. Sí que és admès per SGBD com Oracle o PostgreSQL.

CREATE TABLE ordinadors (
    codi INTEGER PRIMARY KEY,
    carac CHAR(50),
    alumne INTEGER NOT NULL REFERENCES alumnes DEFERRABLE);

CREATE TABLE alumnes (
    exp INTEGER PRIMARY KEY,
    nom CHAR(40),
    ordinador INTEGER NOT NULL REFERENCES ordinadors DEFERRABLE);

BEGIN;
    INSERT INTO ordinadors VALUES (1, 'Pentium III', 1001);
    INSERT INTO alumnes VALUES (1001, 'Pep', 1);
COMMIT;

Quan inserim l'ordinador, no es comprova que existisca l'alumne 1001, ja que al ser la seua clau aliena de tipus DEFERRABLE, ho comprovarà en el moment del COMMIT. Igual passa en la inserció de l'alumne. Quan fem el COMMIT, es comprovaran les 2 coses i sí que es compliran.

Exemple utilitat 4: desfer canvis voluntàriament

Si durant l'execució de la transacció decidim que no volem acabar-la (potser ens adonem que el saldo d'Alícia es feia negatiu), podem executar l'ordre ROLLBACK en compte de COMMIT. Això farà que s'anul·le l'efecte de les actualitzacions de la transacció que estaven fetes:

BEGIN;
    UPDATE comptes
        SET saldo = saldo - 100
        WHERE nom = 'Alícia';

    -- Ací ens adonem que el compte d'Alícia és negatiu.
    -- Per tant, volem anul·lar l'update anterior, i farem el ROLLBACK

ROLLBACK;

Exemple utilitat 5: desfer parcialment la transacció en curs

És possible controlar les accions en una transacció d'una manera més detallada. És a dir, en compte de descartar tota la transacció, podem descartar només part de la transacció.

Això es fa amb els SAVEPOINTS.

SAVEPOINTS

Els savepoints són "punts de salvament", que permeten descartar selectivament parts de la transacció (admetent la resta de la transacció).

Exemple:

Suposem que traspassem 100 € del compte d'Alícia al compte de Pep, però que, només haver-ho fet, ens adonem que volíem traspassar-ho al compte de Maria i no al de Pep. Si ens haguérem curat en salut utilitzant transaccions i savepoints, podríem desfer només unes sentències de la transacció i no tota:

BEGIN;
    UPDATE comptes SET saldo = saldo - 100.00
        WHERE nom = 'Alícia';

    SAVEPOINT seguretat1;

    UPDATE comptes SET saldo = saldo + 100.00
        WHERE nom = 'Pep';

    -- Ai, no! No li ho havia d'haver sumat a Pep, sinó a Maria.

    ROLLBACK TO seguretat1;

    UPDATE comptes SET saldo = saldo + 100.00
        WHERE nom = 'Maria';
COMMIT;

Després d'haver definit un savepoint amb SAVEPOINT nom_del_savepoint, podré retrocedir, si cal, a eixe punt mitjançant l'ordre ROLLBACK TO nom_del_savepoint.

Les accions fetes en la transacció entre la definició del SAVEPOINT i el ROLLBACK TO corresponent es descarten, però es queden els canvis anteriors a la definició del SAVEPOINT.

Podem retrocedir a un savepoint sempre que volguem:

BEGIN;
    Acció 1;
    Acció 2;

    SAVEPOINT segur1;

    Acció 3;
    Acció 4;

    ROLLBACK TO segur1;

    Acció 5;

    ROLLBACK TO segur1;

    ...
COMMIT;

Ara bé, si retrocedint a un savepoint en concret, s'esborraran automàticament tots els savepoints definits després d'eixe savepoint. Açò ho fa el sistema per a alliberar recursos.

BEGIN;
    Acció 1;
    Acció 2;

    SAVEPOINT segur1;

    Acció 3;
    Acció 4;

    SAVEPOINT segur2;

    Acció 5;

    ROLLBACK TO segur2;

    Acció 6;

    ROLLBACK TO segur1;   -- Ací s'esborrarà el SAVEPOINT segur2

    ...
COMMIT;

Savepoints: exemple 1

La següent transacció inserirà els clients 1000 i 1004:

begin;
    insert into clients values (1000);

    savepoint s1;

    insert into clients values (1001);

    savepoint s2;

    insert into clients values (1002);

    rollback to s2;

    insert into clients values (1003);

    rollback to s1;

    insert into clients values (1004);
commit;

Savepoints: exemple 2

Què fa la següent transacció?

begin;
    insert into clients values (1000);

    savepoint s1;

    insert into clients values (1001);

    savepoint s2;

    insert into clients values (1002);

    rollback to s1;

    insert into clients values (1003);

    rollback to s2;

    insert into clients values (1004);
commit;

Donarà error en el rollback to s2 perquè el rollback to s1 esborra el savepoint s2.

Nota

Encara que no treballem amb transaccions, MySQL tracta cada sentència SQL com si fóra una transacció d'una sola sentència:

BEGIN;
    Sentència;
COMMIT;

És a dir, si per exemple s'està fent un UPDATE molt costós, mentre dura l'actualització, qualsevol altre intent d'actualització de la taula serà paralitzat fins que no acabe el primer UPDATE.

Exercicis sobre transaccions

1. Comprova que es poden desfer els canvis d'una transacció no acabada: a. Inicia una transacció. b. Fes diversos update/delete en algunes taules. c. Anul·la la transacció. d. Comprova que els canvis que has fet no han tingut efecte.

2. Comprova que mentre una transacció no acaba, una altra no pot vore els canvis fets: e. Inicia una transacció i fes algun canvi (update/delete) en una taula i comprova que, de moment, els canvis es poden veure. f. Obri en una altra sessió altra connexió a la mateixa bd. g. Comprova en esta 2a sessió que els canvis fets en la 1a sessió no es poden vore (ja que no està acabada la transacció). h. Ves a la 1a sessió i confirma la transacció. i. Ves a la 2a sessió i comprova que ara sí que estan els canvis fets.

3. Comprova que mentre una transacció no acaba, si havia fet una modificació en alguna taula, una altra sessió no pot fer cap modificació en eixa taula: j. Inicia una transacció i fes algun canvi (update/delete) en una taula i comprova que, de moment, els canvis es poden veure. k. Obri en una altra sessió altra connexió a la mateixa bd. l. Intenta fer un canvi (update/delete) en la mateixa taula. Voràs com el servidor deixa en espera eixa petició esperant que acabe la transacció de la primera sessió. m. Comprova que, quan acabe la transacció de la 1a sessió (tant si la confirmes com si l'acabes), l'update/delete de la 2a sessió acabarà d'immediat.

   Nota

   Depèn de la configuració, potser sí que deixe modificar un registre que no està sent modificat. Però no deixarà modificar el mateix registre que està sent modificat.

4. Comprova el funcionament dels savepoints: n. Inicia una transacció i fes algun canvi en una taula. o. Posa un savepoint. p. Fes algun altre canvi. q. Cancel·la la transacció només fins al savepoint. r. Confirma la transacció i comprova quin dels dos canvis ha quedat.

---

ANNEX: Còpies de seguretat

Fer una còpia de seguretat d'una Base de Dades és una feina d'administració obligatòria per mantenir la informació protegida. MySQL et permet realitzar esta senzilla tasca amb ordres des de la consola, una volta hem entrat en l'entorn textual de MySQL:

Exportar una base de dades

Sintaxi:

mysqldump -h servidor -u usuari -p nomBaseDades > nomFitxer

Exemple:

mysqldump -h localhost -u root -p lliga1213 > lliga1213.sql

Importar una base de dades

Sintaxi:

mysql -h servidor -u usuari -p nomBaseDades < nomFitxer

Exemple:

mysql -h localhost -u root -p lliga1213 < lliga1213.sql