事务及事务的隔离级别

原创

LiveGohyper 2023-04-21 11:06:00 博主文章分类：MySQL ©著作权

文章标签 数据隔离级别 MySQL MySQL事务 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者LiveGohyper的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、为什么需要事务

事务是数据库管理系统(DBMS)执行过程中不可再分割的逻辑单位，由一个有限的数据库操作序列构成（多个DML(Data manipulation Language)语句，select查询语句不包含事务），事务要么都成功，要么都不成功。

如果A给B账户转100块钱：

UPDATE table_money SET money = money - 100 where person_name = 'A'
UPDATE table_money SET money = money + 100 where person_name = 'B'

二、事务的特性

事务有ACID四个属性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（durability）。

原子性（Atomicity）

一个事务被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部提交失败，不能只执行一部分操作。

例如：给A打100块钱给B，A账户必须减100而B账户必须加100，否则A、B账余额不能改变。

一致性（Consistency）

事务将数据从一种一致性转换到另一种一致性状态，在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。

例如：A给B打钱之前，A账户余额 + B账户余额与 A 给 B打钱之后，A账户余额 + B账户余额相等。或者说，A账户减少的Money与B账户增加的Money相等。

隔离性（Isolation）

一个事务的执行不能被其他事务干扰。一个事务内部的操作以及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。

如果不保证隔离性，会导致什么问题？

例如：A账户有5000元，B账户有2000元，A给B转2次账，第一次A给B转1000元，第二次A又给B转了1000元。理论上，转完帐之后A账户余额为3000元，B账户余额为4000元。将两次转账操作记为T1和T2，可以先执行T1，也可以先执行T2，结果都是一样。但实际数据库操作中，可以T1和T2的操作交替执行，如下图：

事务及事务的隔离级别_隔离级别

事务T1，A账户在转账给B账户，还没有对A账户数据库进行操作的时候，事务T2就开始执行读取A账户内的余额，此时A账户的余额还未改变，读到的仍是5000。最终的结果就是A账户余额为4000而B账户余额为4000，平白无故多出来1000元，银行血亏。

所以不仅要保证操作以原子性的方式执行完成，而且要保证其它的状态转换不会影响到本次状态转化，事务各自之间的操作互不影响，称为隔离性。

持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失。

三、事务并发引起的问题

MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。

理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据，这样并发事务的执行就变成了串行化执行。这对串行化执行性能影响太大，我们既想保持事务的一定的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，当我们舍弃隔离性的时候，会带来如下一些数据问题。

脏读

含义：一个事务读取到另一个事务修改但是未提交的数据。

在事务A执行过程中，事务A对数据资源进行了修改，事务B读取到了事务A修改后的数据。由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据，这就是脏读（Dirty Read）。

事务及事务的隔离级别_数据_02

不可重复读

含义：当事务内相同的记录被读取两次，且两次得到的结果不相同，称不可重复读。

事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。

事务及事务的隔离级别_MySQL事务_03

幻读

含义：在事务执行过程中，另一个事务将新记录添加到正在读取的事务中，会发生幻读。

事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中，事务A新增了数据，导致事务B后一次读取到前一次查询没有看到的行。

幻读和不可重复度区别：幻读重点强调新增的记录，不可重复读强调数据在原来基础上做的修改。

事务及事务的隔离级别_MySQL事务_04

四、SQL标准中的四种隔离级别

事务并发导致的问题严重性：脏读 > 不可重复读 > 幻读

一些人制定了一个所谓的SQL标准，在标准中设立了4个隔离级别，隔离级别越低，越严重的问题越可能发生。为了舍弃一部分隔离性来换取一部分性能。

四个隔离级别，针对不同的隔离级别，并发事务可以发生不同严重程度的问题：

READ UNCOMMITTED：未提交读

可能发生脏读、不可重复读和幻读

READ COMMITTED：已提交读

可能发生不可重复读、幻读，但不会发生脏读

REPEATABLE READ：可重复读

可能发生幻读，但是不会发生脏读和不可重复读

SERIALIZABLE：可串行化

什么问题都不可以发生

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✔	✔	✔
READ COMMITTED		✔	✔
REPEATABLE READ			✔
SERIALIZABLE

五、MySQL中的隔离级别

不同数据库厂商对SQL标准中规定的四种隔离级别支持不一样，例如：Oracle就只支持 READ COMMITTED和SERIALIZABLE 隔离级别。虽然MySQL支持四种隔离级别，但是与SQL标准中所规定的各级隔离级别允许发生的问题有些出入，MySQL在REPEATABLE READ 隔离级别下，可以禁止幻读问题发生。

MySQL的默认隔离级别为REPEATABLE READ，可以手动修改事务的隔离级别。

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✔	✔	✔
READ COMMITTED		✔	✔
REPEATABLE READ			---
SERIALIZABLE

如何设置事务的隔离级别

SQL语句查询当前事务的隔离级别：

方式一：
SHOW VARIABLES LIKE '%TX_ISOLATION';
方式二：
SELECT @@TX_ISOLATION;
还可以使用下列语句分别查询全局和会话的事务隔离级别:
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@SESSION.TX_ISOLATION;

SESSION：表示查询的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
GLOBAL：表示查询的事务隔离级别将应用于所有 session（全局）中的所有事务；

@是用户变量，@@是系统变量。
select @a;
变量名，如果你不加的话，会认为这是一个列名，但是这列不存在，就报错了；
@变量名 : 定义一个用户变量.
= 对该用户变量进行赋值.
用户变量赋值有两种方式: 一种是直接用'='号，另一种是用':='号。
其区别在于:
使用set命令对用户变量进行赋值时，两种方式都可以使用；
用select语句时，只能用':='方式，因为select语句中，'='号被看作是比较操作符。
(@i:=@i+1)

事务及事务的隔离级别_隔离级别_05

提示：在MySQL 8.0.3 中，tx_isolation 变量被 transaction_isolation 变量替换了。在 MySQL 8.0.3 版本中查询事务隔离级别，只要把上述查询语句中的 tx_isolation 变量替换成 transaction_isolation 变量即可。

SQL语句修改事务的隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

其中的level可选值有4个：
level: {
    REPEATABLE READ
    READ COMMITTED
    READ UNCOMMITTED
    SERIALIZABLE
}

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; --- 只对执行完该语句之后产生的会话起作用。当前已经存在的会话无效。
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; --- 对当前会话的所有后续的事务有效，该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。如果在事务之间执行，则对后续的事务有效。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --- 只对当前会话中下一个即将开启的事务有效。下一个事务执行完后，后续事务将恢复到之前的隔离级别。该语句不能在已经开启的事务中间执行，会报错的。

其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：
SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

任何用户都能改变会话的事务隔离级别，但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。

事务及事务的隔离级别_MySQL事务_06

六、MySQL事务

事务基本语法

事务开始：

方式一：begin
方式二：START TRANSACTION（推荐）
方式三：begin work

事务回滚：

rollback

事务提交：

使用事务插入一行数据，rollback后数据没有了：

事务及事务的隔离级别_MySQL_07

使用事务插入一行数据，commit后数据还在：

事务及事务的隔离级别_MySQL_08

保存点

当开启一个事务，执行了很多语句，忽然发现某条语句有点问题，只好使用ROLLBACK语句来让数据库状态恢复到事务执行之前的样子，然后一切从头再来，但是可能根据业务和数据的变化，不需要全部回滚。所以MySQL里提出了一个保存点（英文：savepoint）的概念，就是在事务对应的数据库语句中打几个点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。定义保存点的语法如下：

SAVEPOINT 保存点名称;

当我们想回滚到某个保存点时，可以使用下边这个语句（语句中的SAVEPOINT是可有可无的）：

ROLLBACK TO [SAVEPOINT] 保存点名称;

不过如果ROLLBACK语句后边不跟随保存点名称的话，会直接回滚到事务执行之前的状态。

如果我们想删除某个保存点，可以使用这个语句：

RELEASE SAVEPOINT 保存点名称;

事务及事务的隔离级别_数据_09

事务及事务的隔离级别_数据_10

事务及事务的隔离级别_MySQL事务_11

注意：

如果出现 SAVEPOINT xxx does not exist，需检查MySQL自动提交是不是开启的
AUTOCOMMIT模式：在开启情况下，对于每条statement来说，都会自动形成一个commit，也就是会即时对开始和结束一个事务。所以，当出现rollback to savepoint出现这个错误时，第一步是检查autocommit模式是否有开启。

select @@autocommit;
1则代表开启，0则代表未开启。

也可以使用如下命令查看AUTOCOMMIT开启与关闭状态：
SHOW VARIABLES LIKE '%AUTOCOMMIT%';

如果需要关闭，则输入以下代码

set autocommit = 0 ;
这时候，就是必须对每一个事务都要手动开始或保存。代码为

START TRANSACTION
# OR
BEGIN
# OR
BEGIN WORK
要结束的话，就要comment或者rollback来结束这个阶段

事务及事务的隔离级别_MySQL_12

事务及事务的隔离级别_MySQL事务_13

七、隐式提交

当使用START TRANSACTION或者BEGIN语句开启了一个事务，或者把系统变量autocommit的值设置为OFF时，事务就不会进行自动提交，但是如果输入了某些语句之后系统就会悄悄的提交掉，就像输入了COMMIT语句了一样，这种因为某些特殊的语句而导致事务提交的情况称为隐式提交，这些会导致事务隐式提交的语句包括：

执行语句DDL

定义或修改数据库对象的数据定义语言（Datadefinition language，缩写为：DDL）。

所谓的数据库对象，指的就是数据库、表、视图、存储过程等等。当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时，就会隐式的提交前边语句所属于的事务，就像这样：

BEGIN;
SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
...    ... # 事务中的其它语句
CREATE TABLE ...

事务及事务的隔离级别_MySQL_14

执行建表语句时注意：
CREATE TABLE test(
pk_id int(32) NOT NULL AUTO_INCREMENT,
age int(2) NOT NULL,
gender char(1) DEFAULT '1',
PRIMARY KEY (pk_id)
)ENGINE=INNODB DEFAULT CHARSET = utf8mb4;
注意别把反引号和单引号混淆，字段和表名要么加上反引号，要么就是不写。

反单引号(backquote)，又读反引号，是西文字符中的附加符号，主要用于计算机相关领域。位置在键盘中数字键“1”的左边，其上档符号是“~ ”，使用Shift键可以换挡输入。由于计算机显示的原因，反单引号非常容易和单引号 ‘ 混淆。反单引号是西方符号，主要用在linux的bash中。

隐式使用或修改MySQL数据库中的表

使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。

SQL ALTER TABLE 语法

如需在表中添加列，请使用下列语法:
ALTER TABLE table_name ADD column_name datatype

要删除表中的列，请使用下列语法：
ALTER TABLE table_name DROP COLUMN column_name
注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。

要改变表中列的数据类型，请使用下列语法：
ALTER TABLE table_name ALTER COLUMN column_name datatype

事务及事务的隔离级别_数据_15

事务控制或关于锁定的语句

在一个会话里，一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上一个事务。

START TRANSACTION;
SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
INSERT INTO ... # 事务中的一条语句
... # 事务中的其它语句
START TRANSACTION; # 此语句会隐式的提交前边语句所属于的事务

事务及事务的隔离级别_MySQL_16

或者当前的autocommit系统变量的值为OFF，手动把它调为ON时，也会隐式的提交前边语句所属的事务。

或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。

事务及事务的隔离级别_MySQL_17

事务及事务的隔离级别_数据_18

注意：对于一个事务，开启 LOCK TABLES 表名，之后执行修改语句，会报错 table was locked，如果另外起一个事务，对锁定的表进行修改，会一直卡住，等表被释放，也就是执行 UNLOCK TABLES 语句后，该修改语句会执行。总之其他事务执行的语句会在表锁释放以后执行。

LOCK TABLES为当前线程锁定表
UNLOCK TABLES释放被当前线程持有的任何锁
当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表会自动被解锁。 

如果一个线程获得在一个表上的一个READ锁，该线程和所有其他线程只能从表中读。
如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。

加载数据的语句

比如使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。

LOAD DATA LOCAL infile 'C://Users//R22496//Desktop//无标题.xls' into table table_money
CHARACTER SET utf8 -- 可选，指定导入文件的编码，避免中文乱码问题。假如这里文件 my_user_info.txt 的编码为 gbk，那么这里编码就应该设为 gbk 了
FIELDS TERMINATED BY '||' -- 字段分隔符，每个字段(列)以什么字符分隔，默认是 \t
	OPTIONALLY ENCLOSED BY '' -- 文本限定符，每个字段被什么字符包围，默认是空字符
	ESCAPED BY '\\' -- 转义符，默认是 \
LINES TERMINATED BY '\n' -- 记录分隔符，如字段本身也含\n，那么应先去除，否则load data 会误将其视作另一行记录进行导入
(id, name, age, address, create_date) -- 每一行文本按顺序对应的表字段，建议不要省略


如果excel导入，可以先转为csv（Comma Separated Value）格式