Transaction

Une transaction, c'est un ensemble de requêtes qui sont exécutées en un seul bloc. Ainsi, si une des requêtes du bloc échoue, on peut décider d'annuler tout le bloc de requêtes (ou de quand même valider les requêtes qui ont réussi). On peut donc considérer que chaque requête constitue une transaction qui est automatiquement commitée. Par défaut, MySQL est donc en mode "autocommit".

Pour quitter ce mode, il suffit de lancer la requête suivante :

set autocommit=0; 
Query OK, 0 rows affected (0,00 sec) 

Inserons une donnée dans une table

 mysql> use transaction; 
 Database changed 
 mysql> create table testtransaction(i integer) engine='INNODB'; 
 Query OK, 0 rows affected (0,09 sec) 

mysql> begin;
Query OK, 0 rows affected (0,00 sec)
mysql> insert into testtransaction values (2); 
Query OK, 1 row affected (0,00 sec) 
mysql> select * from testtransaction;

mysql> select * from testtransaction;

Empty set (0,01 sec) 

Lors du commit:

mysql> commit ;
Query OK, 0 rows affected (0,10 sec)

select * from testtransaction;
+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0,00 sec)

L'operation de rollback permet d'annuler une transaction:

mysql> begin;
Query OK, 0 rows affected (0,00 sec)

mysql> insert into testtransaction values (5);
Query OK, 1 row affected (0,00 sec)

mysql> select * from testtransaction;
+------+
| i    |
+------+
|    2 |
|    5 |
+------+
2 rows in set (0,00 sec)

mysql> rollback;
Query OK, 0 rows affected (0,01 sec)

mysql> select * from testtransaction;
+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0,00 sec)

Il faut faire attention que les comit/rollback en concerne pas les opération ddl (create table, alter ...)

mysql> begin
    -> ;
Query OK, 0 rows affected (0,00 sec)

mysql> alter table testtransaction add column testcolumn integer;
Query OK, 0 rows affected (0,12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from testtransaction;
+------+------------+
| i    | testcolumn |
+------+------------+
|    2 |       NULL |
+------+------------+
1 row in set (0,00 sec)

Lors du rollback:

mysql> rollback ;
Query OK, 0 rows affected (0,00 sec)

mysql> select * from testtransaction;
+------+------------+
| i    | testcolumn |
+------+------------+
|    2 |       NULL |
+------+------------+
1 row in set (0,00 sec)

Transaction Level

Il est possible de savoir le niveau des transaction via:

-- mysql> show variables like 'tx_isolation';
-- sous mysql 8 tx_isolation a été renommé en transation_isolation
mysql> show  variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0,00 sec)

Repeatable Read

C'est le niveau d'isolation par défaut pour InnoDB. Des lectures cohérentes dans la même transaction lisent l'instantané établi par la première lecture. Cela signifie que si vous émettez plusieurs instructions SELECT simples (non verrouillables) dans la même transaction, ces instructions SELECT sont également cohérentes.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0,00 sec)

mysql> create table transactionlevel (i integer);
Query OK, 0 rows affected (0,12 sec)

mysql> begin 
    -> ;
Query OK, 0 rows affected (0,00 sec)

mysql> insert into transactionlevel values (2);
Query OK, 1 row affected (0,00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ;

Query OK, 0 rows affected (0,00 sec)

mysql> begin;
Query OK, 0 rows affected (0,00 sec)

mysql> select * from transactionlevel;
Empty set (0,00 sec)

Apres le commit de la première connexion, on obtient:

commit;
Query OK, 0 rows affected (0,06 sec)

mysql> select * from transactionlevel;
+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0,00 sec)

 mysql> select * from transactionlevel;
Empty set (0,00 sec)

Il faut attendre le commit de la transaction pour voir les changements;

mysql> select * from transactionlevel;
Empty set (0,00 sec)

mysql> commit;
Query OK, 0 rows affected (0,00 sec)

mysql> select * from transactionlevel;
+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0,00 sec)