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)

Table Lock vs Row Lock

Avantages du verrouillage au niveau de la ligne:

• Moins de conflits de verrous lors de l'accès à différentes lignes dans de nombreux threads.
• Moins de changements pour les annulations.
• Permet de verrouiller une seule ligne pendant une longue période.

Inconvénients du verrouillage au niveau de la ligne:

• Prend plus de mémoire que les verrous au niveau de la page ou de la table.
• Est plus lent que les verrous au niveau de la page ou de la table lorsqu'il est utilisé sur une grande partie de la table car vous devez acquérir beaucoup plus de verrous.

Avec les verrous de niveau supérieur, vous pouvez également prendre en charge plus facilement des verrous de différents types pour optimiser l'application, car la charge de verrouillage est inférieure à celle des verrous de niveau ligne.

Les verrous de table sont supérieurs aux verrous de page ou de ligne dans les cas suivants:

• La plupart des instructions pour la table sont des lectures.

Table Lock

Read Lock

Un verrou READ présente les caractéristiques suivantes:

•  Un verrou READ pour une table peut être acquis par plusieurs sessions simultanément. De plus, d'autres sessions peuvent lire des données de la table sans acquérir le verrou.
• La session qui détient le verrou READ peut uniquement lire les données de la table, mais ne peut pas écrire. De plus, les autres sessions ne peuvent pas écrire de données dans la table tant que le verrou READ n'est pas libéré. Les opérations d'écriture d'une autre session seront mises dans les états en attente jusqu'à ce que le verrou READ soit libéré.
• Si la session est terminée, normalement ou de manière anormale, MySQL libérera tous les verrous implicitement. Cette fonctionnalité est également pertinente pour le verrou WRITE.