TCL 事务控制语言
SQL的四种语言
- DDL（
Data Definition Language）数据库定义语言
建表, 建库, 修改表结构 - DML（
Data Manipulation Language）数据操纵语言
增删改查, select, insert, update, delete - DCL（
Data Control Language）数据库控制语言
修改用户权限, 重置密码 - 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;
事务的创建
-
开启事务
set autocommit = 0; start transaction; // 可选 编写事务中的sql语句(
select,insert,update,delete) 不包括DDL(create,drop,alter)-
结束事务
- 提交事务
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种隔离级别
- 读未提交数据(
read uncommitted)- 允许事务读取未被其他事务提交的变更
-
脏读,不可重复读和幻读, 都会出现
- 读已提交数据(
read commited)- 只允许事务读取已经被其他事务提交的变更, 可以避免脏读
- 但
不可重复读和幻读问题仍然可能出现
- 可重复读(
repeatable read)- 确保事务可以从一个字段中读取相同的值, 在这个事务持续期间, 禁止其他事务对这个字段进行更新
- 可以避免
脏读和不可重复读, 但是幻读问题让然存在
- 串行化(
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
