开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 【MySQL】事务篇 -> 正文阅读

[大数据]【MySQL】事务篇

文章目录

MySQL：事务篇

MySQL：事务篇

笔记来源：

黑马程序员 MySQL数据库入门到精通
小林coding

1.事务简介

事务：是一组操作的集合，它时一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

什么是事务

在MySQL中的事务（Transaction）是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 DDL、DML、DCL 操作，比如 insert,update,delete 语句，默认是自动提交的。

理解事务

事务相当于一些操作的集合，这些操作要么全都完成，要么全都不完成。

在银行转账时，必须保证转账绝对安全，这时需要事务参与:

update account set money = money - 200 where id = 1; 
update account set money = money + 200 where id = 2;

一个人给另一个人转账，那么转账人的金额必须减少，被转账人的金额必须增加，两件事是捆绑在一起的。

假如在第一次update之后，出现了意外、异常，没有执行第二次update，这时转账就出现了错误。

所以事务就是用来保证数据安全的一个重要举措，当发生异常时可以进行回滚，恢复已经执行的操作。

2.事务操作

模拟转账操作

首先我们先创建一个account表。

CREATE TABLE account (
	id int PRIMARY KEY,
	`name` VARCHAR(10),
	money int 
);
INSERT into account(id,name,money) VALUES(1,'张三',2000)
INSERT into account(id,name,money) VALUES(2,'李四',2000)

实现转账操作

-- 1.查询张三账户余额
select money FROM account where name='张三';

-- 2.将张三余额-1000
UPDATE account 
SET money = money - 1000 
WHERE
	NAME = '张三'

-- 3.将李四余额+1000
UPDATE account 
SET money = money + 1000 
WHERE
	NAME = '李四'

我们先来执行一遍转账操作，执行结果：没有问题

现在我们模拟转账过程中出现了异常的状况

先将两个人的余额恢复为2000

UPDATE account
set money = 2000
WHERE name='张三' or name ='李四'

模拟异常

-- 1.查询张三账户余额
select money FROM account where name='张三';

-- 2.将张三余额-1000
UPDATE account 
SET money = money - 1000 
WHERE
	NAME = '张三';
模拟异常
-- 3.将李四余额+1000
UPDATE account 
SET money = money + 1000 
WHERE
	NAME = '李四';

执行结果

此时我们就发现了异常，张三余额减了1000，而李四余额没有加上。

为什么会出现这样的结果：

因为sql语句是自动提交的，没执行一次语句就自动提交上去了，当执行发现了异常就不在执行，后面的语句也不执行，但异常前的语句执行完不会在变了。所以就导致了信息不对等的结果。这也就是为什么有事务的原因。

事务的一些操作

-- 查看事务提交方式
SELECT @@autocommit;
-- 将自动提交改为手动提交
SET @@autocommit = 0; 
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

设置事务提交方式为手动提交之后，我们再次执行转账操作

-- 1.查询张三账户余额
select money FROM account where name='张三';

-- 2.将张三余额-1000
UPDATE account 
SET money = money - 1000 
WHERE
	NAME = '张三';
-- 3.将李四余额+1000
UPDATE account 
SET money = money + 1000 
WHERE
	NAME = '李四'

执行完后，我们发现account表中的数据并没有发生变化，这是因为设置手动提交之后，执行的语句只是在当前会话中，如果我们自己不执行commit操作，那执行的语句就不会提交给数据库。也就不会修改里面的内容。

我们执行commit语句。

执行完过后，我们就会发现数据已经更改了。

现在我们再来模拟一遍转账出现异常的操作。

1.先将表中的余额都修改回2000.

UPDATE account
set money = 2000
WHERE name='张三' or name ='李四';
COMMIT;

2.模拟出错：

-- 1.查询张三账户余额
select money FROM account where name='张三';

-- 2.将张三余额-1000
UPDATE account 
SET money = money - 1000 
WHERE
	NAME = '张三';
模拟异常
-- 3.将李四余额+1000
UPDATE account 
SET money = money + 1000 
WHERE
	NAME = '李四'

此时已经发生了错误，我们就不能在进行手动提交事务，而是需要回滚事务

3.回滚事务

-- 回滚事务
ROLLBACK;

执行完之后我们就发现数据没有发生变化。

通过另一种方式开启事务：

1、开启事务：Start Transaction

任何一条DML语句(insert、update、delete)执行，标志事务的开启
命令：BEGIN 或 START TRANSACTION

2、提交事务：Commit Transaction

成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步，如果没有提交，会发现内存的数据发生改变，但硬盘内的数据不变。
命令：COMMIT

3、回滚事务：Rollback Transaction

失败的结束，将所有的DML语句操作历史记录全部清空
命令：ROLLBACK

-- 开启事务
START TRANSACTION;
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

模拟转账异常

-- 改为自动提交
SET @@autocommit = 1; 

-- 开启事务
START TRANSACTION;

-- 执行转账操作

-- 1.查询张三账户余额
select money FROM account where name='张三';

-- 2.将张三余额-1000
UPDATE account 
SET money = money - 1000 
WHERE
	NAME = '张三';
模拟异常
-- 3.将李四余额+1000
UPDATE account 
SET money = money + 1000 
WHERE
	NAME = '李四';
	
-- 提交事务
COMMIT;

-- 如果转账中的任何一条出现问题，则回滚事务 ,这个要和jdbc结合才能使用
-- 回滚事务
ROLLBACK;

3. 事务四大特性

原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性：事务完成时，必须时所有的数据保持一致状态。
隔离性：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性：事务一旦提交或回滚，它对数据库中的数据的改变是永久。

4.并发事务问题

脏读：一个事务读到另一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条数据，但两次读取的数据不同。
幻读：一个事务按照条件查询数据时，没有对象的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻觉”。

脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

假设有A和B这两个事务同时在处理，事务A先开始读取金额，然后执行更新操作，此时事务A还没有提交事务，而此时正好事务B也从数据库中读取小林的余额数据，你们事务B读取到的余额是刚才事务A更新后的数据，即使没有提交事务。但事务A可能会因为出错而回滚事务。所以事务B读到的数据是过期的数据，这就是脏读。

不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

假设又A和B两个事务在同时处理，事务A现开始从数据库中读取余额，然后继续执行代码逻辑，在这时如果事务B更新了这条数据，并提交了事务，那么当事务A再次读取该数据时，就会发现前后两次读到的数据不一致，这就叫不可重复读。

幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

假设有A和B两个事务同时在处理，事务A先开始从数据库查询账户余额大于100万的记录，发现共有五条，然后B也按相同的搜索条件也是查询除了5条数据记录。但是，接下来事务A插入了一条余额超过100的记录，并且提交了事务，此时事务B再次查询余额大于100万的记录，发现记录有6条。发现和前一次读到的记录数量不一样了，就感觉发生了幻觉一样，这就叫幻读

5. 事务隔离级别

当多个事务并发执行时可能遇到「脏读、不可重复读、幻读」的现象，这些现象会对事务的一致性产生不同程序的影响。

脏读：读取到其他事务未提交的数据
不可重复读：前后读取的数据不一致
幻读：前后读取到的记录数量不一致

这三个现象的严重性排序：

SQL标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

读未提交：指一个事务还未提交时，它做的变更就能被其他事务看到。
读提交：指一个事务提交之后，它做的改变才能被其他事务看到。
可重复读：指一个事务执行过程中看到的数据，一致跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
串行化：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成之后，才能继续执行。

按隔离级别水平高低排序如下：

对于不同的隔离级别，并发事务时可能发生的现象也会不同。

在读未提交的隔离级别下，可能发生脏读、不可重复读和幻读现象。
在读提交的隔离级别下，可能会发生不可重复读和幻读的现象。
在可重复读的隔离级别下，可能会发生幻读的现象
在串行化的隔离级别下，这些现象都不会发生。

所以，要解决脏读现象，起码要升级到读提交以上的级别，要解决不可重复读的现象，起码要升级到可重复读的隔离级别。

但是，要解决幻读现象不建议将隔离级别升级到串行化，因为这样会导致很多事务无法并行操作，在并发事务时性能很差。

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象，解决的方案有两种：

针对快照读（普通select语句），通过MVCC（多版本并发控制）方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一致跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的。所以很好的避免了幻读问题。
针对当前读（select … for update 等语句），是通过**next-key lock (记录锁+间隙锁）**方式解决了幻读，因为当执行select ... for update语句的时候，会加上next-key lock，如果有其他事务在next-key lock范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

接下来，举个具体的例子来说明这四种隔离级别，有一张账户余额表，里面有一条账户余额为 100 万的记录。然后有两个并发的事务，事务 A 只负责查询余额，事务 B 则会将我的余额改成 200 万，下面是按照时间顺序执行两个事务的行为：

在不同隔离级别下，事务A执行过程中查询到的余额可能会不同：

在读未提交隔离级别下，事务B修改余额后，虽然没有提交事务，但是此时的余额已经可以被事务A看见了，于是事务A中余额V1的值是200万，余额V2、V3自然也是200万了。
在读提交隔离级别下，事务B修改余额后，但没有提交事务，所以余额V1还是100万，等事务B提交之后，最新的余额次啊能被事务A看见，因此余额V2、V3都是200万。
在可重复读隔离级别下，事务A只能看见启动事务时的数据，所以V1，V2的值都是100万，当事务A提交完食物后，就能看见最新的余额数据了，所以余额V3的值时200万。
在串行化隔离级别下，事务B在执行将余额100万修改未200万时，由于此前事务A执行了读操作，这样就发生了读写冲突，于是就会被锁住，直到事务A提交后，事务B才能执行。所以从A的角度来看，余额V1、V2都是100万，而V3的值是200万。

接下来我们用sql语句具体实现这些隔离级别

-- 查看事务隔离级别
-- 版本低的用：
select @@tx_isolation;
-- 版本高的用：
SELECT @@TRANSACTION_isolation;

-- 设置隔离级别
set SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;

我们用两个命令行窗口来演示两个并发事务。

读未提交

读提交

可重复读

串行化

事务的隔离级别-操作

-- 查看隔离级别 
show variables like '%isolation%';

-- 设置隔离级别
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、read committed、repeatable read、serializable

-- 设置read uncommitted
set session transaction isolation level read uncommitted;
 
-- 设置read committed
set session transaction isolation level read committed;
 
-- 设置repeatable read
set session transaction isolation level repeatable read;
 
-- 设置serializable
set session transaction isolation level serializable;