Introduction détaillée au traitement des transactions dans Mysql

Qu'est-ce que le traitement des transactions ? ? Le traitement des transactions est utilisé pour maintenir l'intégrité de la base de données. Il garantit que les lots d'opérations MySQL sont entièrement exécutés ou non.

1. Concept de transaction MySQL

Les transactions MySQL sont principalement utilisées pour traiter des données avec des opérations volumineuses et une grande complexité. Une unité d'exécution logique est composée d'une ou plusieurs séquences d'opérations de base de données. Cette série d'opérations est soit entièrement exécutée, soit toute exécution est abandonnée. Dans MySQL, seules les bases de données ou les tables utilisant le moteur de base de données Innodb prennent en charge les transactions. Les transactions sont utilisées pour gérer les instructions d'insertion, de mise à jour et de suppression.

2. Caractéristiques de la transaction : Atomicité (atomicité), Cohérence (stabilité, cohérence), isolation (Isolement) et Durabilité (continuité, fiabilité). Ces quatre propriétés sont également appelées propriétés ACID.

1. Atomicité : Une transaction est la plus petite unité d'exécution dans une application, tout comme un atome est la plus petite particule dans la nature et a la caractéristique d'être indivisible. Une transaction est le plus petit corps d'exécution logique qui ne peut pas être divisé dans une application. Un groupe de transactions peut réussir ou être retiré.

2. Stabilité et cohérence : Le résultat de l'exécution de la transaction doit faire passer la base de données d'un état de cohérence à un autre état de cohérence. Une base de données est dans un état cohérent lorsqu'elle contient uniquement les résultats des transactions validées avec succès. La cohérence est garantie par l’atomicité. Il y a des données illégales (contraintes de clé étrangère, etc.) et la transaction est retirée.

3. Isolation : L'exécution de chaque transaction n'interfère pas les unes avec les autres et les opérations internes de toute transaction sont isolées des autres transactions concurrentes. C'est-à-dire : les transactions exécutées simultanément ne peuvent pas voir l'état intermédiaire des autres, et les transactions exécutées simultanément ne peuvent pas s'influencer mutuellement. Les transactions s'exécutent de manière indépendante. Si le résultat d'une transaction affecte d'autres transactions, les autres transactions seront retirées. L’isolation à 100 % des transactions nécessite de sacrifier la vitesse.

4. Continuité, fiabilité : La continuité, également appelée durabilité, signifie qu'une fois qu'une transaction est soumise, toute modification apportée aux données doit être enregistrée dans un stockage permanent, généralement dans une base de données physique. Après une panne du logiciel ou du matériel, le pilote de table de données InnoDB utilisera le fichier journal pour le reconstruire et le modifier. La fiabilité et la vitesse élevée sont incompatibles. L'option innodb_flush_log_at_trx_commit détermine quand enregistrer les transactions dans le journal.

Remarque : Le moteur de stockage MyISAM ne prend pas en charge les transactions, mais le moteur de stockage InnoDB prend en charge les transactions. Les transactions ne sont valides que pour les instructions qui affectent les données. show moteurs Afficher les moteurs de données pris en charge par le verrouillage mysql.

3. Concept de lecture de données

1. Lectures sales : la lecture dite sale est la lecture de données sales, et les données sales font référence à celles qui ne sont pas validées. données. Une transaction modifie un enregistrement. Avant que la transaction ne soit terminée et validée, les données sont dans un état en attente (peuvent être validées ou annulées). À ce moment, une deuxième transaction lit les données non validées et poursuit le traitement en fonction de cela. générera des dépendances de données non validées. Ce phénomène est appelé lecture sale.

2. Lectures non répétables : Une transaction lit successivement le même enregistrement, mais les données lues deux fois sont différentes. Nous appelons cela une lecture non répétable. Autrement dit, les données ont été modifiées par d'autres transactions entre les deux lectures de cette transaction.

3. Lectures fantômes : une transaction relit les données précédemment récupérées selon les mêmes conditions de requête, mais constate que d'autres transactions ont inséré de nouvelles données qui satisfont à ses conditions de requête. Ce phénomène est appelé lecture fantôme.

4. Niveau d'isolement des transactions

Modifier la syntaxe du niveau d'isolement des transactions :

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

1. Lecture non validée (lecture non autorisée) , lire non validé) : Il s'agit du niveau d'isolement le plus bas, permettant aux autres transactions de voir les données non validées. Ce niveau peut conduire à des lectures sales. Si une transaction a commencé à écrire des données, une autre transaction n'est pas autorisée à écrire des données en même temps, mais d'autres transactions sont autorisées à lire cette ligne de données. Ce niveau d'isolement peut être atteint grâce à un "verrouillage en écriture exclusif". La perte de mise à jour est évitée, mais des lectures incorrectes peuvent se produire. En d'autres termes, la transaction B a lu les données non validées de la transaction A. L'instruction SELECT est exécutée de manière non verrouillable, de sorte que les données sales peuvent être lues et que le niveau d'isolement est le plus bas.

SET session transaction isolation level read uncommitted ;
SET global transaction isolation level read uncommitted;/*全局建议不用*/
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

Créez une table étudiant simple, définissez les champs id, name et num, démarrez la transaction 1, ajoutez la table à la procédure stockée, la transaction n'est pas soumis, affichez l'état actuel de la transaction de la base de données, vous pouvez voir une transaction de données, le niveau de transaction est LIRE UNCOMMITTED :

drop table if exists student;
create table student(
id int primary key auto_increment comment 'id',
name varchar(100) comment '名称',
num int
);
drop procedure if exists proc_on_sw;
delimiter ;;
create procedure proc_on_sw()
begin
start transaction;
insert into student(name,num) value('aaa',1);
select * from information_schema.INNODB_TRX;
end
;;
delimiter ;;
call proc_on_sw();

Créez une nouvelle transaction 2, interrogez l'étudiant table, nous sommes au niveau READ UNCOMMITTED, vous pouvez voir les données non validées d'autres transactions : vérifiez à nouveau l'état de la transaction de la base de données et nous verrons que l'état est normal.

start transaction ;
select * from student;
commit;
select * from information_schema.INNODB_TRX;

2. Lecture validée (lecture autorisée, validation de lecture) : La transaction qui lit les données permet à d'autres transactions de continuer à accéder à la ligne de données, mais non validées écrit La transaction empêchera les autres transactions d'accéder à la ligne. Ce niveau d'isolement évite les lectures incorrectes, mais des lectures non répétables peuvent se produire. La transaction A lit les données à l'avance, la transaction B met à jour les données et valide la transaction, et lorsque la transaction A lit à nouveau les données, les données ont changé.

SET session transaction isolation level read committed ;
SET global transaction isolation level read committed; /*全局建议不用*/

drop procedure if exists proc_on_up;
delimiter ;;
create procedure proc_on_up()
begin
set autocommit=0;
update student set name='cc' where id=1;
commit;
set autocommit=1;
end
;;
delimiter ;;
call proc_on_up();
select * from student;

　　3.repeatable read(可重复读取):就是在开始读取数据（事务开启）时，不再允许修改操作,事务开启，不允许其他事务的UPDATE修改操作，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。避免了不可重复读取和脏读，但是有时可能出现幻读。这可以通过“共享读锁”和“排他写锁”实现。

set session transaction isolation level repeatable read;

　　4.串行化、序列化：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

set session transaction isolation level serializable;

　　　　　　隔离等级 　　脏读 　　不可重复读　　 幻读
　　　　　　读未提交 　　YES　　 YES 　　　　　YES
　　　　　　读已提交 　　NO 　　 YES 　　　　    YES
　　　　　　可重复读 　　NO 　　 NO　　　　　   YES
　　　　　　串行化 　　    NO 　　 NO 　　　　　  NO

五、完整例子包括提交和回滚完整例子

drop procedure if exists pro_new;
delimiter;;
create procedure pro_new(out rtn int)
begin
declare err INT default 0;
-- 如果出现异常，会自动处理并rollback
declare exit handler for sqlexception ROLLBACK ; 
-- 启动事务
set autocommit=0;
start transaction;
insert into student(name,num) values(NULL,2.3);
-- set err = @@IDENTITY; -- =  获取上一次插入的自增ID;
set err =last_insert_id(); -- 获取上一次插入的自增ID
insert into student(name,num) VALUEs('ccc',err);
-- 运行没有异常，提交事务
commit;
-- 设置返回值为1
set rtn=1;
set autocommit=1;
end
;;
delimiter ;;
set @n=1;
call pro_new(@n);
select @n;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!