mysql基础 | 13.事务控制语言（TCL）

 一、事务控制语言

#TCL
 /*
 Transaction Control Language 事务控制语言
 
 事务：
 一个或一组sql语言组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
 
 案例：转账

张三丰 1000
郭襄   1000

update 表 set 张三的余额=500 where name='张三'
意外（下面的没更新）
update 表 set 郭襄的余额=1500 where name='郭襄'


事务的ACID（acid）属性：
1.原子性（Atomicity）
指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

2.一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态（例如转账金额之和不变）

3.隔离性（Isolation）
指一个事务的执行不能被其他事务干扰

4.持久性（Durability）
一个事务一旦被提交，它对数据库中数据的改变就是永久性的


事务的创建：
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

set autocommit=0 #自动执行

步骤1：开启事务
set autocommit=0;
start transaction; 可选的

步骤2：编写事务中的sql语句(select、insert、update、delete)
语句1;
语句2;
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务

savepoint 节点名; 设置保存点


事务的隔离级别：
			脏读	不可重复读	幻读
read uncommitted：	√	√		√
read committed：	×	√		√
repeatable read：	×	×		√
serializable：		×	×		×
mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed

查看隔离级别
select @@transaction_isolation; 

设置隔离级别
set session|global transaction isolation level 隔离级别;



开启事务的语句;
update 表 set 张三的余额=500 where name='张三'
意外（下面的没更新）
update 表 set 郭襄的余额=1500 where name='郭襄'
结束事务的语句;


*/

SHOW VARIABLES LIKE 'autocommit'; #查看autocommit这个变量
SHOW ENGINES;  #显示引擎

#1.演示事务的使用步骤

#①开启事务
SET autocommit=0;
START TRANSACTION;
#②编写一组事务的语句
UPDATE account SET balance=1000 WHERE username='张无忌';
UPDATE account SET balance=1000 WHERE username='赵敏';
#③结束事务
ROLLBACK;
#commit;

SELECT * FROM account;


#2.delete和truncate在事务使用时的区别

#演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

#演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

#delete还能回滚，truncate会直接清空数据，回滚不了了


#3.演示savepoint的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=1;
SAVEPOINT a; #设置保存点
DELETE FROM account WHERE id=2;
ROLLBACK TO a; #回滚到保存点
#删除id=1的操作执行了，删除id=2的操作被回滚了

二、并发问题与隔离级别

1、并发问题

2、隔离级别

3、演示事务的隔离级别：

1.read uncommitted

• 停止mysql运行，重启

• 登录mysql

• 查看隔离级别

目前的变量名为@@transaction_isolation

• 将隔离级别修改为最低级别read uncommitted

• 创建一个事务，但不提交

• 重开一个cmd窗口登录mysql，查看隔离级别，并修改为最低

• 查看此时test库中account的数据，发现张无忌已被改为john，这就是脏读

• 若是数据库黑色一端rollback（回滚），蓝色端再次查看数据，会发现john又变回张无忌，这也可称为不可重复读

2.read committed

• 将隔离级别改为read committed，然后启动事务，将id=1的用户名改为张飞，不提交

• 在另一端数据库同时修改隔离级别为read committed，查看数据库，可见数据未被修改

• 在黑色端提交事务后，再在蓝色端查询，发现数据更新

说明read committed可以避免脏读，无法避免不可重复读

3.repeatable read

• 在黑色端开启事务，但不提交；在蓝色端查看，数据未修改

说明repeatable可以避免脏读

• 在黑色端提交（结束）事务，蓝色端的数据仍未改变，说明repeatable可以避免不可重复读，但不支持幻读

注：需要两个窗口都各开一个事务

• 此时蓝色端再重开一个新事务，才能查看到更新的数据

• 幻读（前三个隔离级别都无法避免幻读）

原先数据只有两行，在黑色端开启事务的同时，蓝色端插入新的数据

黑色端更新用户名全为mm，理应数据是两行，但却提示变成了三行，就是因为在蓝色端的操作造成了幻读

4.serializable

• 在黑色端开启事务，准备修改用户名

• 同时在蓝色端，开启事务，插入新的数据，构造幻读，但回车后一直处于加载状态，等待黑色端的事务提交结束后，再执行蓝色端的事务

说明serializable通过这种“锁”的方式来避免幻读