【MySQL 06】Traitement des transactions

1. Propriétés ACIDES des transactions

Les transactions ont 4 caractéristiques : Atomicité, Cohérence, Isolement et Durabilité.

Prenons l'exemple du « virement bancaire » :

• Atomicité : Les instructions qui composent une transaction forment une unité logique, et seule une partie de celle-ci ne peut pas être exécutée. Autrement dit, une transaction est la plus petite unité indivisible. Par exemple : lors du processus de virement bancaire, le montant du transfert doit être soustrait d'un compte et ajouté à un autre compte en même temps. Il n'est pas raisonnable de modifier un seul compte.

• Cohérence : La base de données est cohérente avant et après l'exécution de la transaction. Autrement dit, les transactions doivent transformer correctement l’état du système. Par exemple : lors du processus de virement bancaire, soit le montant du virement est transféré d'un compte à un autre, soit les deux comptes restent inchangés, et il n'y a pas d'autre situation.

• Isolement : Une transaction n'a aucun impact sur une autre transaction. C'est-à-dire qu'il est impossible pour une transaction de voir une transaction dans un état incomplet. Par exemple, lors d'un virement bancaire, avant que la transaction de transfert ne soit soumise, une autre transaction de transfert ne peut être qu'en attente.

• Durabilité : Les effets du traitement des transactions peuvent être préservés de manière permanente. D’un autre côté, les transactions doivent être capables de résister à toutes les pannes, y compris les pannes de serveur, de processus, de communication, de support, etc. Par exemple : lors du processus de virement bancaire, l'état du compte après le virement doit être enregistré.

2. Statut de la transaction

SET AUTOCOMMIT = 0 , 禁止自动提交 SET AUTOCOMMIT = 1, 开启自动提交
START TRANSACTION：开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT
COMMIT：提交事务，保存更改，释放锁
ROLLBACK：回滚本事务对数据库的所有更改，然后结束事务，释放锁
SAVEPOINT savepoint_name：创建一个savepoint识别符来ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT savepoint_name：回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交
SET TRANSACTION：允许设置事务的隔离级别
LOCK TABLES：允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCK TABLES语句之前显式的commit或rollback。
我们一般所以一般在事务代码里不会使用LOCK TABLES

3. Opération de transaction

(1) Créez d'abord la table de données des employés :

mysql> create table employee(
    -> employeeID char(4),
    -> name varchar(20) not null,
    -> job varchar(20),
    -> departmentID int
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into employee value ('7513' , 'Nora Edwar' , 'Programmer', 128);
mysql> insert into employee value ('9006' , 'Candy Burn' , 'Systems Ad',128 );
mysql> insert into employee value ( '9842' , 'Ben Smith' ,  'DBA' , 42);
mysql> insert into employee value ('9843',  'Pert Park'  , 'DBA' , 42 );
mysql> insert into employee value ('9845' , 'Ben Patel'  , 'DBA' , 128 );
mysql> insert into employee value ('9846' , 'Red Right' ,  null, 128 );
mysql> insert into employee value ('9847' , 'Run Wild'  ,  null , 128 );
mysql> insert into employee value ('9848' , 'Rip This J' , null , 128 );
mysql> insert into employee value ('9849' , 'Rip This J' , null  , 128 );
mysql> insert into employee value ( '9850' , 'Reader U' ,   null , 128 );
mysql> insert into employee value ('6651',  'Ajay Patel' , 'Programmer', 128 );

mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+

(2) SET AUTOCOMMIT=0 :

mysql> set autocommit = 0;//禁止自动提交

mysql> insert into employee values(null,'test1',null,128);

mysql> savepoint s1;//创建一个savepoint识别符

mysql> insert into employee values(null,"test2",null,128);

mysql> savepoint s2;//创建一个savepoint识别符

mysql> insert into employee values(null,"test3",null,128);

mysql> savepoint s3;//创建一个savepoint识别符mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 || NULL       | test3      | NULL    |    128 |
+------------+------------+------------+--------------+

(3) ROLLBACK AU POINT DE SAVE :

mysql> rollback to savepoint s1;//回滚到s1标签处：mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 || NULL       | test1      | NULL       |      128 |
+------------+------------+------------+--------------+

(4) COMMIT :

mysql> commit;//提交事务
mysql> rollback to savepoint s2;
//一旦事务提交了，就不能再回滚ERROR 1305 (42000): SAVEPOINT s2 does not exist

(5) SET AUTOCOMMIT=1 :

mysql> set autocommit = 1;//自动提交事务

mysql>  insert into employee values(null,"test4",null,128);

mysql> savepoint s4;//一旦创建，自动提交mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 |
| NULL       | test3      | NULL       |          128 || NULL       | test4      | NULL       |          128 |
+------------+------------+------------+--------------+mysql> rollback to s4;//此时就无法回滚了
ERROR 1305 (42000): SAVEPOINT s4 does not exist

4. Verrous

verrouillage partagé, verrouillage exclusif, verrouillage pessimiste, verrouillage optimiste, niveau de ligne Verrous, verrous au niveau de la table

• verrous partagés : lors de la lecture de données, ajoutez un verrou partagé aux données. Le partage n'entre pas en conflit avec le partage direct, mais il entre en conflit avec les verrous exclusifs.

• Verrouillage exclusif : lors de la mise à jour des données, installez un verrou exclusif et interdisez toutes les autres actions.

• Verrou pessimiste : utilisé lorsqu'il y a de nombreuses mises à jour et peu de requêtes. Le verrouillage pessimiste n'est pas un véritable verrou dans la base de données, c'est l'attitude des gens envers les transactions.

• Verrouillage optimiste : il existe peu de mises à jour et il est utilisé pour de nombreuses requêtes. Le verrouillage optimiste n'est pas un véritable verrou dans la base de données, c'est l'attitude des gens envers les transactions.

5. Traitement simultané

• Lecture sale : une transaction lit les données qui n'ont pas été soumises par une autre transaction
  Transaction 1 : mise à jour Une donnée
  ————>Transaction 2 : Lire l'enregistrement mis à jour par la transaction 1
  Transaction 1 : Appeler le commit pour soumettre
  Les données lues par la transaction 2 à ce moment sont les données stockées dans le mémoire de base de données, appelée lecture sale.
  Les données lues sont des données sales
  Explication détaillée :
  Lecture sale signifie : lorsqu'une transaction accède aux données et a modifié les données, mais que cette modification n'a pas encore été soumise à la base de données, ceci à ce moment ,
  une autre transaction accède également à ces données et utilise ensuite ces données. Étant donné que ces données n'ont pas encore été soumises, les données lues par une autre
  transaction sont des données sales et les opérations basées sur les données sales peuvent être incorrectes.

• Lecture non répétable : Dans la même transaction, les mêmes données sont lues deux fois et le contenu est différent
  Transaction 1 : Interroger un enregistrement
  ————- > Transaction 2 : mettre à jour les enregistrements interrogés par la transaction 1
  ————->Transaction 2 : appeler la validation pour soumettre
  Transaction 1 : interroger à nouveau le dernier enregistrement
  À ce moment, la transaction 1 interroge les mêmes données deux fois, le contenu disponible est différent, ce qu'on appelle une lecture non répétable

• 幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同 
  事务1：查询表中所有记录 
  ———->事务2：插入一条记录 
  ———->事务2：调用commit进行提交 
  事务1：再次查询表中所有记录 
  此时事务1两次查询到的记录是不一样的，称为幻读 
  详细解释： 
  幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改， 
  这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表 
  中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行， 
  就好象发生了幻觉一样。

6、事务隔离

事务隔离五种级别：

TRANSACTION_NONE 不使用事务。 
TRANSACTION_READ_UNCOMMITTED 允许脏读。 
TRANSACTION_READ_COMMITTED 防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别 
TRANSACTION_REPEATABLE_READ 可以防止脏读和不可重复读， 
TRANSACTION_SERIALIZABLE 可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率

以上的五个事务隔离级别都是在Connection接口中定义的静态常量，

使用setTransactionIsolation(int level) 方法可以设置事务隔离级别。 
如:con.setTransactionIsolation(Connection.REPEATABLE_READ);

注意：事务的隔离级别受到数据库的限制，不同的数据库支持的的隔离级别不一定相同

summary： 
（1）Serializable：可避免脏读、不可重复读、虚读情况的发生。 
（2）Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读，是 MySQL 默认的事务隔离级别） 
（3）Read committed：可避免脏读情况发生。（读取已提交的数据） 
（4）Read uncommitted：最低级别，以上情况均无法保证。（读取到了未提交的数据）

以上就是 【MySQL 06】事务处理的内容，更多相关内容请关注PHP中文网（www.php.cn）！