SQL transaccions

Manipular les bases de dades és quelcom molt delicat i on un error pot suposar una pèrdua de moltes dades. Això pot passar molt fàcilment amb les comandes DELETE i UPDATE, ja que cal que continguin una clàusula de condició WHERE, i si ens equivoquem en aquesta podem afectar a totes les dades.

Necessitem, doncs algun tipus de UNDO (oepració de "desfer els canvis"). Aquesta funcionalitat és la que s'implementa amb les transaccions. Podeu veure la sintaxi complerta a la web de MySQL.

Les operacions relacionades amb transaccions només funcionen amb motors "transaction-safe" com INNODB o NDBCLUSTER (o NDB). Per tant, NO funcionen amb MyISAM o similars.

Les comandes de transaccions són les següents:

• start transaction: tots els canvis es refereixen des del punt en què executem aquesta comanda. Si desfem els canvis, tornarem a aquest punt.
• rollback: equival a UNDO. Desfà tots els canvis i torna a la situació immediatament posterior al "start transaction".
• commit: consolida els canvis i els fa permanents, sense possibilitat de tornar enrere.

Molt relacionades amb aquestes hi ha les instruccions de més baix nivell per protecció de concurrència: LOCK TABLES. Amb aquestes podem bloquejar les operacions sobre les taules per part d'altres usuaris o sessions:

• lock read: bloqueja la taula però les operacions de lectura pels altres sessions estan permeses.
• lock write: bloqueja la taula completament.

Autocommit

Si no iniciem la transacció amb START TRANSACTION estem en mode autocommit. Això significa que cada pas que fem porta implícit un commit. Per tant, cada canvi serà visible ment per tots els usuaris i sessions.

Per canviar el mode autocommit:

mysql> SET autocommit=0;

...a partir d'aquest moment totes les operacions seran transaccionals. Això significa que ningú veurà els canvis fins fer COMMIT.

Per suposat, fer un START TRANSACTION també desactiva el mode autocommit.

Exercicis

Amb ajuda de 2 consoles concurrents de mysql i una BD adient, comprova els modes de treball de les transaccions i de LOCK TABLE.

Per cada exercici has de realitzar alguna prova que demostri al professor que has entès els conceptes.

1. Transaccions i bloqueig de taules.
   1. Comprova la diferència entre LOCK TABLE READ i LOCK TABLE WRITE.
   2. És el mateix per les taules InnoDB que per les MyISAM?
   3. Comprova que quan inicies una transacció els canvis no es consoliden fins que fas COMMIT. Caldrà que inventis un exemple.
   4. És el mateix per les taules InnoDB que per les MyISAM?
2. El motor InnoDB disposa també de diverses modalitats de bloqueig de files o LOCK ROWS per InnoDB. Llegeix l'article de MySQL.
   1. Deadlocks
      1. Què és un deadlock?
      2. Prova de reproduir l'exemple que es mostra en el link de MySQL indicat.
      3. En aquest exemple, s'ha produït finalment el deadlock o no?
   2. Lock Row
      1. Provoca el lock d'una sessió a nivell de ROW. Tria una taula amb motor InnoDB i prova de crear aquesta situació obrint una transacció en una shell.
      2. Quina/es sentència/es de manipulació de dades SQL pot provocar un LOCK a nivell de ROW? (select, insert, delete, update?)
      3. Què passa si deixem un thread a l'espera en un LOCK durant més d'1 minut?