漫谈MySQL九-MySQL中事务的使用

最新推荐文章于 2022-04-21 15:40:24 发布

王老狮

最新推荐文章于 2022-04-21 15:40:24 发布

阅读量791

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 sql 事务隔离级别事务

本文链接：https://blog.youkuaiyun.com/b379685397/article/details/123017464

版权

mysql 专栏收录该内容

14 篇文章

订阅专栏

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

二、MySQL 中的隔离级别

2.1.如何设置事务的隔离级别

2.1.1.使用GLOBAL 关键字（在全局范围影响）：

2.1.2.使用 SESSION 关键字（在会话范围影响）：

2.1.3.上述两个关键字都不用（只对执行语句后的下一个事务产生影响）：

3.3.3.关于 MySQL 复制的一些语句

3.3.4.其它的一些语句

大家好，我是王老狮，上篇我们简单了解了什么是事务，今天我们就来具体聊聊，事务在SQL中是如何使用的吧。

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

我们上边介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题也有轻重缓急之分，我们给这些问题按照严重性来排一下序：

脏读 > 不可重复读 > 幻读

我们上边所说的舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，越严重的问题就越可能发生。有一帮人（并不是设计 MySQL 的大叔们）制定了一个所谓的 SQL 标准，在标准中设立了4 个隔离级别：

READ UNCOMMITTED：未提交读。
READ COMMITTED：已提交读。
REPEATABLE READ：可重复读。
SERIALIZABLE：可串行化。

SQL 标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

也就是说：

READ UNCOMMITTED 隔离级别下，可能发生脏读、不可重复读和幻读问题。

READ COMMITTED 隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。

REPEATABLE READ 隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。

SERIALIZABLE 隔离级别下，各种问题都不可以发生。

二、MySQL 中的隔离级别

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

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

2.1.如何设置事务的隔离级别

我们可以通过下边的语句修改事务的隔离级别：

SET [GLOBAL |SESSION] TRANSACTION ISOLATION LEVEL level;

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

设置事务的隔离级别的语句中，在 SET 关键字后可以放置 GLOBAL 关键字、 SESSION 关键字或者什么都不放，这样会对不同范围的事务产生不同的影响，具体如下：

2.1.1.使用GLOBAL 关键字（在全局范围影响）：

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

注意：执行完该语句则之后的会话都会起作用，但已经存在的会话是无效的

2.1.2.使用 SESSION 关键字（在会话范围影响）：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

只对当前会话的所有后续的事务有效

该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。如果在事务之间执行，则对后续的事务有效。

2.1.3.上述两个关键字都不用（只对执行语句后的下一个事务产生影响）：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

只对当前会话中下一个即将开启的事务有效。下一个事务执行完后，后续事务将恢复到之前的隔离级别。该语句不能在已经开启的事务中间执行，会报错的。

2.1.4.启动参数修改

如果我们在服务器启动时想改变事务的默认隔离级别，可以修改启动参数transaction-isolation 的值，比方说我们在启动服务器时指定了--transaction-isolation=SERIALIZABLE ，那么事务的默认隔离级别就从原来的 REPEATABLE READ 变成了 SERIALIZABLE。

想要查看当前会话默认的隔离级别可以通过查看系统变量transaction_isolation 的值来确定：

SHOW VARIABLES LIKE 'transaction_isolation';

或者使用更简便的写法：

SELECT @@transaction_isolation;

注意：transaction_isolation 是在 MySQL 5.7.20 的版本中引入来替换 tx_isolation 的，如果你使用的是之前版本的 MySQL ，请将上述用到系统变量 transaction_isolation 的地方替换为 tx_isolation。

三、MySQL 事务的使用

3.1.事务基本语法

3.1.1.事务提交

事务的提交有三种方式

begin
START TRANSACTION （推荐）
begin work

3.1.2.事务回滚

rollback

3.1.3.事务提交

commit

3.2.保存点

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

定义保存点的语法如下：

SAVEPOINT  保存点名称;

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

ROLLBACK TO [SAVEPOINT]  保存点名称;

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

RELEASE SAVEPOINT  保存点名称;

savepoint
show variables like '%autocommit%';     自动提交事务是开启的 set autocommit=0;
insert into testdemo values(5,5,5);
savepoint order_exp;
insert into testdemo values(6,6,6);
savepoint order_exp_2;
insert into testdemo values(7,7,7);
savepoint s3;
select * from testdemo
rollback to savepoint order_exp_2
rollback

3.3.隐式提交

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

3.3.1.执行 DDL

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

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

BEGIN;
SELECT ... #  事务中的一条语句
UPDATE ... #  事务中的一条语句
... #  事务中的其它语句
CREATE TABLE ... #  此语句会隐式的提交前边语句所属于的事务

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

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

3.3.2.加载数据的语句

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

3.3.3.关于 MySQL 复制的一些语句

使用 START SLAVE 、STOP SLAVE 、RESET SLAVE 、CHANGE MASTER TO 等语句时也会隐式的提交前边语句所属的事务。

3.3.4.其它的一些语句

使用 ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH 、 LOAD INDEX INTO CACHE 、OPTIMIZE TABLE 、REPAIR TABLE 、RESET 等语句也会隐式的提交前边语句所属的事务。