Mysql数据库：事务管理

一、Mysql事务的概述

1、Mysql事务的概念

2、事务的ACID四大特性

3、事务之间的相互影响

4、事务的四种隔离级别

5、MySQL与Oracle自动提交事务的区别

6、事务隔离级别的作用范围

二、Mysql事务相关操作

1、查询和设置事务隔离级别

1.1 全局级事务隔离级别

1.1.1 查询

1.1.2 设置

1.2 会话级事务隔离级别

1.2.1 查询

1.2.2 设置

2、设置控制事务

3、事务控制语句

3.1 提交事务

3.2 回滚事务

3.3 多点回滚

三、总结

1、事务的ACID特性

2、事务的隔离级别

一、Mysql事务的概述

1、Mysql事务的概念

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料，又需要删除和该人员相关的信息，如信箱、文章等等。这样，这些数据库操作语句就构成一个事务

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
事务是通过事务的整体性以保证数据的一致性

总之，所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位

2、事务的ACID四大特性

ACID，指在可靠数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。这是可靠数据库所应具备的几个特性。

原子性：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

①事务是一个完整的操作，事务的各元素是不可分的
②事务中的所有元素必须作为一个整体提交或回滚
③如果事务中的任何元素失败，则整个事务将失败

案例:
A给B转帐100元钱的时候只执行了扣款语句，就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到加款，在生活中就会引起纠纷。这种情况就需要事务的原子性来保证事务要么都执行，要么就都不执行

一致性：事务在执行前后，数据库都必须保持一致性状态。这意味着事务执行前后，数据库的完整性约束没有被破坏

①当事务完成时，数据必须处于一致状态
②在事务开始前，数据库中存储的数据处于一致状态
③在正在进行的事务中，数据可能处于不一致的状态
④当事务成功完成时，数据必须再次回到已知的一致状态

案例:
对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致

隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

①对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
②修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

①指不管系统是否发生故障，事务处理的结果都是永久的
②一旦事务被提交，事务的效果会被永久地保留在数据库中

3、事务之间的相互影响

原则上一个事务的执行不能被其他事务干扰，事务之间的相互影响主要体现在隔离性上，这种相互影响可能导致以下几种问题：

脏读（Dirty Reads）：一个事务读取到另一个事务未提交的数据。未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。如果这些数据最终被回滚，那么第一个事务读取的数据就是不一致的
不可重复读（Non-Repeatable Reads）：在同一个事务中，多次读取同一数据集合时，由于其他事务的提交，导致后续读取的数据和前面的不一致
幻读（Phantom Reads）：在同一个事务中，两次执行相同的查询，由于其他事务插入或删除了数据，导致第二次查询的结果集中出现了额外的“幻影”数据
丢失更新（Lost Updates）：当两个或多个事务读取同一数据，并基于读取的值更新它时，其中一个事务的修改可能会被其他事务的修改所覆盖。

4、事务的四种隔离级别

读未提交（read uncommitted）：最低的隔离级别，允许读取未提交的数据，即使未提交，本事务也可能读取到其他会话中未提交事务修改的数居。可能会导致脏读、不可重复读和幻读

安全性最差，但性能最好（一般不使用）

读已提交（read committed）：允许从已经提交的事务中读取数据。这可以避免脏读，但不可重复读和幻读仍然可能发生，Oracle等多数数据库默认都是该级别

安全性较差，性能较好（oracle 数据库默认）

可重复读（repeatable read）：保证在同一个事务中多次读取同一数据的结果是一致的，避免了脏读和不可重复读，是MySQL的默认隔离级别。但在某些情况下，幻读仍然可能发生

安全性较高，性能较差（mysql 数据库默认）

串行化（serializable）：最高的隔离级别，通过强制事务串行执行，每次读都需要获得表级共享锁，读写相互都会阻塞，避免了脏读、不可重复读和幻读，但可能会导致大量的超时和锁争用问题

安全性高，性能差（一般不使用）

5、MySQL与Oracle自动提交事务的区别

MySQL数据库和Oracle数据库在处理自动提交事务方面有一些基本的区别，这些区别主要源于它们对事务管理的默认行为和配置选项的不同

MySQL的自动提交

默认行为：MySQL默认启用自动提交模式。这意味着每个单独的SQL语句都被视为一个事务，除非明确地开始一个事务（使用start transaction或begin语句）。在执行语句后，事务会立即被提交，结果被永久保存
配置：可以通过设置autocommit变量来更改这个行为。设置SET autocommit=0;会禁用自动提交，直到你显式地提交事务（使用commit语句）或回滚事务（使用rollback语句）

Oracle的自动提交

默认行为：Oracle数据库默认不启用自动提交。在Oracle中，事务会一直保持开启状态，直到你显式地提交（使用commit语句）或回滚（使用rollback语句）。这意味着你可以执行多个SQL语句作为同一个事务的一部分，而不是每个语句都作为一个单独的事务
配置：虽然Oracle默认不使用自动提交，但客户端工具和应用程序可以设置自动提交。例如，在SQL*Plus中，可以使用set autocommit on命令来启用自动提交。此外，许多数据库连接池和ORM工具也允许配置自动提交行为

6、事务隔离级别的作用范围

MySQL数据库中，事务隔离级别可以在全局级别和会话级别进行设置，这两者的主要区别在于设置的作用范围和持久性

全局级别(Global Level)

作用范围：全局级别的设置会影响到整个数据库系统中所有新建立的会话和事务。这意味着，一旦全局级别的事务隔离级别被设置，它会应用于之后所有新启动的会话，除非在会话级别进行了覆盖
持久性：全局级别的设置通常在数据库的配置文件中进行，或者通过管理员会话临时设置。如果是通过配置文件设置，更改是持久的，会在数据库重启后仍然有效。如果是在会话中通过SQL命令设置，那么重启数据库后，这些设置会丢失，除非再次进行设置

会话级别(Session Level)

作用范围：会话级别的设置仅影响到当前会话中的事务。这意味着，对事务隔离级别的更改只会影响到当前的数据库连接和通过该连接执行的事务，对其他会话和事务没有影响
持久性：会话级别的设置仅在当前会话中有效，一旦会话结束，设置就会失效。下一次会话不会继承上一次会话的设置，除非再次进行设置

二、Mysql事务相关操作

1、查询和设置事务隔离级别

1.1 全局级事务隔离级别

1.1.1 查询

方法一：show global variables like '%isolation%';
方法二：select @@global.tx_isolation;

1.1.2 设置

set global transaction isolation level 事务隔离级别;
#事务隔离级别有四种，分别为读未提交（read uncommitted）、读已提交（read committed）、可重复读（repeatable read）、串行化（serializable）

set global transaction isolation level read committed;
#将全局事务隔离级别设置为读已提交（read committed）

注：

设置全局隔离级别会影响新建立的会话的默认隔离级别，但不会立即改变已经存在的会话的隔离级别

1.2 会话级事务隔离级别

1.2.1 查询

方法一：show session variables like '%isolation%';
方法二：select @@session.tx_isolation; 
方法三：select @@tx_isolation;

1.2.2 设置

set session transaction isolation level 事务隔离级别;
#事务隔离级别有四种，分别为读未提交（read uncommitted）、读已提交（read committed）、可重复读（repeatable read）、串行化（serializable）

set session transaction isolation level read committed;
#将会话级事务隔离级别设置为读已提交（read committed）

2、设置控制事务

自动提交是指每个SQL语句是否立即执行并且立即提交到数据库。当自动提交开启时，每个单独的SQL语句都会被立即执行并提交到数据库，而当自动提交关闭时，需要使用显式提交命令将一系列SQL语句的执行结果提交到数据库

set autocommit=0;						
#禁止自动提交
set autocommit=1;						
#开启自动提交，Mysql默认为1
show variables like 'autocommit';		
#查看Mysql中的autocommit值

如果开起自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit，不需要显式地调用提交命令

如果关闭自动提交，当前会话连接的mysql的所有操作都会当成一个事务，直到你输入rollback|commit；当前事务才算结束，当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果

3、事务控制语句

前提：先新建一个表，然后插入一些数据

create table test(id int(3) primary key not null,name varchar(20),money double);
insert into test values(1,'dhx',1000); 
insert into test values(2,'ytc',1000);
select * from test;

3.1 提交事务

使用begin开始事务，而后开始处理、修改数据，最后使用commit提交事务，算一次完整的事务，则在事务中的所有操作生效，否则不生效

begin; 或 start transaction;
#显式地开启一个事务
commit; 或 commit work;
#提交事务，并使已对数据库进行的所有修改变为永久性的

begin;
update test set money = money - 200 where id=1;
commit;

3.2 回滚事务

使用rollback指令回滚未提交的事务，使数据回到事务开始前的状态

rollback; 或 rollback work;
#回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

begin;
update test set money = money - 300 where id = 1;
select * from test;
rollback;

3.3 多点回滚

允许在一个长事务中标记多个回滚点，从而在需要时只回滚到某个特定点，而不是全部撤销事务中的所有操作。在长事务中，如果某个部分失败，无需回滚整个事务，只需回滚到最近的保存点，减少了不必要的重做工作

savepoint S1;
#允许在事务中创建一个回滚点，一个事务中可以有多个savepoint，“S1”代表回滚点名称
rollback to [savepoint] S1;
#把事务回滚到标记的点

begin;
update test set money = money + 300 where id = 1;
savepoint s1;
update test set money = money - 300 where id = 2;
savepoint s2;
select * from test;
update test set money = money - 300 where id = 2;
rollback to s2;
rollback to s1;

使用savepoint s1 进行节点标记

再使用 rollback to savepoint 节点名称进行回滚到你需要的节点状态

此操作不可逆，如果回滚到s1之后，不能回到之后的s2节点，只能往前rollback回滚到事务开始之前的数据

三、总结

1、事务的ACID特性

事务的ACID特性
原子性（Atomicity）	事务中的所有操作作为一个整体提交，要么全部执行成功，要么全部失败
一致性（Consistency）	事务在执行前后，数据库都必须保持一致性状态
隔离（Isolation）	在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间
持久性（Durability）	一旦事务提交，对数据库的修改将被永久保存，即使在系统崩溃的情况下也不会丢失

2、事务的隔离级别

事务的隔离级别
读未提交（Read Uncommitted）	最低隔离级别，允许脏读、不可重复读和幻读
读已提交（Read Committed）	解决了脏读问题，但仍可能出现不可重复读和幻读
可重复读（Repeatable Read）	MySQL默认的隔离级别，解决了脏读和不可重复读问题，但幻读仍有可能发生
串行化（Serializable）	最高隔离级别，可以避免所有并发问题，但可能会导致性能下降，几乎等同于事务串行执行