第20课 事务

TCL 事务控制语言

SQL的四种语言

1. DDL（Data Definition Language）数据库定义语言
   建表, 建库, 修改表结构
2. DML（Data Manipulation Language）数据操纵语言
   增删改查, select, insert, update, delete
3. DCL（Data Control Language）数据库控制语言
   修改用户权限, 重置密码
4. TCL（Transaction Control Language）事务控制语言
   创建事务, 回滚

什么是事务

一组SQL语句组成的执行单元, 这一组SQL语句, 要么全部执行, 要么全部不执行.

举例

• 郭靖向黄蓉转账(交工资)
• 郭靖这个月发了5000, 银行卡余额5000
• 黄蓉, 银行卡余额5000,000
• update set 郭靖的银行卡余额 - 5000;
• update set 黄蓉的银行卡余额 + 5000;
• 我们需要两条SQL语句都要成功, 否则我们宁愿没执行过, 所以就是, 要么全部执行, 要么全部不执行.

事务：

• 事务由单独单元的一个或多个SQL语句组成
• 在这个单元中，每个MySQL语句是相互依赖的
• 而整个单独单元作为一个不可分割的整体
• 如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚
• 所有受到影响的数据将返回到事物开始以前的状态
• 如果单元中的所有SQL语句均执行成功，则事物被顺利执行

举例2:

• 软件安装, 会有很多下一步, 如果点击取消, 则全部回滚.

image.png

事务 vs 存储引擎(表类型)

什么是存储引擎(表类型):

• MySQL中的数据用各种不同的技术存储在文件(或者内存)中。
• 这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。
• 通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
• 如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎(memory)。
• 内存存储引擎能够在内存中存储所有的表格数据。
• 又或者，你需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力), 那就用innodb。
• 这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。

为什么需要多个存储引擎?

• 每种引擎都有各自的优势和不足, 没有一种完美的存储引擎
• 所以MySQL可以针对不同的表, 选择不同的存储引擎
• 就像雷达图

image.png image.png image.png

如何设置表的存储引擎?

create table tb (id int) engine = myisam;
-- 或者
alter table tb engine = memory;

查看所有存储引擎

show engines;

image.png

innodb:

• 支持事务, 查询速度没有myisam快, 容量没有myisam大

myisam:

• 速度快, 容量大, 不支持事务

memory:

• 速度超快, 容量取决于内存, 所以比较小, 不支持事务

事务的属性(ACID)

• 原子性 atomicity

  • 事务是一个整体, 不可分割, (因为原子不可再分)
  • 要么全部执行, 要么全部不执行

• 一致性 consistency

  • 参见能量守恒定律
  • 能量既不会凭空产生，也不会凭空消失，只能从一个物体传递给另一个物体，从一种形式变成另一种形式, 在变换过程中, 系统的总能量保持不变。
  • 事务必须是数据库从一个一致性状态到另一个一致性状态
  • 比如转账(事务)前后, 郭靖, 黄蓉的银行卡余额之和是固定不变的.

• 隔离性(isolation)

  • 一个事务的执行, 不受其他事务的干扰
  • 郭靖向黄蓉转账的时候, 黄药师也可以向黄蓉转账(需要配置隔离级别)

• 持久性(durability)

  • 事务一旦成功, 不可撤销
  • 删除就是一个事务, 删库只能跑路
  • 转账成功, 不能撤销, 除非对方再转给你, 这是另一个事务

事务通常包括多条SQL语句(DML), 其实单独的DML语句, 也是一个事务

• 隐式事务(自动提交)

  • 事务没有明显的开启和结束标记
  • 比如 insert,update,delete

• 显式事务

  • 数据具有明显的开始和结束标记
  • 前提, 必须设置自动提交功能为禁用 set autocommit = 0;
  • 否则每条语句都是一个事务

创建事务

查看变量

show variables like 'autocommit';
-- 或者
select @@autocommit;

关闭自动提交

-- 当前会话有效
set autocommit = 0; 
-- 或者
set session autocommit = 0; 
-- 或者
set @@autocommit = 0;
-- 或者
set @@session.autocommit = 0;

事务的创建

1. 开启事务

   set autocommit = 0;
   start transaction; // 可选
   

2. 编写事务中的sql语句(select, insert, update, delete) 不包括DDL(create, drop, alter)

3. 结束事务

   • 提交事务 commit
   • 回滚事务 rollback

事务实操

drop table if exists test_tb;

CREATE TABLE `test_tb` (
  `id` int(5) unsigned NOT NULL,
  `age` tinyint(5) unsigned NOT NULL,
  `account` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8;

INSERT INTO `test_tb`(`id`, `age`, `account`, `name`) VALUES (1, 11, 5000, '张三');
INSERT INTO `test_tb`(`id`, `age`, `account`, `name`) VALUES (2, 12, 5000, '李四');

事务正常提交

set autocommit = 0;
start TRANSACTION;
update test_tb set account = account - 4000 where name = '张三';
update test_tb set account = account + 4000 where name = '李四';
commit;

事务回滚

set autocommit = 0;
start TRANSACTION;
update test_tb set account = account - 4000 where name = '张三';
update test_tb set account = account + 4000 where name = '李四';
rollback;

同时运行多个事务的时候...

对于同时运行的多个事务, 如果没有采用必要的隔离机制, 就会导致各种并发问题

事务冲突.png

脏读

• 对于两个事务T1,T2, T1读取了已经被T2更新但是还没有被提交的字段之后
• 如果T2回滚, T1读取的内容就是临时并且无效的
• 比如, 有一天, 你查询余额, 发现多了100万!
• 你难以压抑自己激动的心情, 给亲朋好友挨个打电话, 约他们吃饭
• 等打完电话, 再看余额, 发现100万又没有了...
• 原来是银行把钱打错了, 刚刚撤销了之前的转账...

幻读

• 对于两个事务T1,T2, T1从一个表中读取了一些数据
• 然后T2在该表中插入/删除了一些新的行之后, 如果T1再次读取同一个表, 就会多出/少了几行.
• 仿佛出现了幻觉
• 比如, 你看见屋里只有两个人, 干干巴巴, 麻麻赖赖, 一点都不圆润
• 知道吃得少, 所以你想耍一下大方
• 就说"我今天请所有人吃饭!"
• 刚说完, 又从外面进来两个, 他们问你:"你说请所有人吃饭? 好的! 好的!"
• 你不敢相信自己的眼睛, 仿佛出现了幻觉...

image.png

不可重复读

• 对于两个事务T1,T2, T1读取了一个字段,
• 然后T2更新了该字段并提交, T1再次读取同一个字段, 值就不同了.
• 比如上午你去淘宝买东西, 一个手机1999, 果断下单
• 到下午再看的时候, 手机已经发货, 但是售价变成了1799
• 发生悲剧的原因是, 上午卖家修改了价格, 但是没有提交, 下午已经提交了
• 所以上午和下午, 你看到的价格不一样...

数据库事务的隔离性:

• 数据库系统必须具有隔离并发运行各个事务的能力, 使他们不会互相影响, 避免各种并发问题.
• 一个事务与其他事务隔离的程度称为隔离级别
• 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度
• 隔离级别越高, 数据一致性越好, 但并发性越弱
• 就像一次只做一件事, 没有其他事情的干扰, 肯定不容易出错, 但是效率也会比较低

4种隔离级别

1. 读未提交数据(read uncommitted)
   • 允许事务读取未被其他事务提交的变更
   • 脏读, 不可重复读和幻读, 都会出现
2. 读已提交数据(read commited)
   • 只允许事务读取已经被其他事务提交的变更, 可以避免脏读
   • 但不可重复读和幻读问题仍然可能出现
3. 可重复读(repeatable read)
   • 确保事务可以从一个字段中读取相同的值, 在这个事务持续期间, 禁止其他事务对这个字段进行更新
   • 可以避免脏读和不可重复读, 但是幻读问题让然存在
4. 串行化(serializable)
   • 确保事务可以从一个表中读取相同的行
   • 在这个事务持续期间, 禁止其他事务对该表执行插入, 更新和删除操作, 所有并发问题都可以避免
   • 但是性能十分低下

image.png

MySQL支持以上全部四种事务隔离级别, 默认 repeatable read(可重复读)

查看当前隔离级别

select @@tx_isolation;
-- 或者
show variables like "tx_isolation";

设置隔离级别

set session transaction isolation level repeatable read; // 当前会话
-- 或者
set global transaction isolation level repeatable read; // 全局

隔离级别验证

感兴趣的小伙伴们, 可以测试一下各个隔离级别的不同
下面是测试需要时, 用到的数据

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    id INT auto_increment PRIMARY KEY,
    NAME VARCHAR ( 10 ) NOT NULL,
    account INT ( 11 ) NOT NULL,
    age TINYINT ( 1 ) NOT NULL,
    sex CHAR ( 1 ) NOT NULL DEFAULT '男' 
);
INSERT INTO test ( NAME, account, age )
VALUES
    ( '张三', 3000, 18 ),
    ( '李四', 4000, 28 ),
    ( '王五', 5000, 38 ),
    ( '赵六', 6000, 48 ),
    ( '孙七', 2000, 19 ),
    ( '周八', 1000, 29 ),
    ( '吴老九', 9000, 39 ),
    ( '冯老十', 8000, 49 );

冲突的级别

脏读、不可重复读、幻读的级别高低是：

脏读 < 不可重复读 < 幻读。

所以，设置了最高级别的serializable就不用在设置repeatable read和read committed了

回滚点(savepoint)

就像玩游戏时的存盘点, 如果游戏人物死了, 就会在存盘点复活

直接上例子, 以上面的数据为例

set autocommit = 0;
start TRANSACTION;
update test set account = 9999 where id = 1;
SAVEPOINT a;
update test set account = 9999 where id = 2;
rollback to a;

select * from test;

image.png