尚硅谷MySQL学习笔记(Day_5)-TCL语言介绍：事务

TCL语言

Transaction Control Language ：事务控制语言

事务

概念：
       一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

案例：转账

      宋万达 1000
      宋千达 1000
      update 表 set 宋万达的余额= 500 where name = ‘宋万达’
      【意外】
      update 表 set 宋千达的余额=1500 where name = ‘宋千达’

      一般两个修改语句同时执行，一旦中途出现意外，整个单元将会回滚。

事务的特性（ACID）：
      原子性：一个事务不可再分割，要么都执行要么都不执行
      一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
      隔离性：一个事务的执行不受其他事务的干扰
      持久性：一个事务一旦提交，则会永久的改变数据库的数据

事务的创建
      隐式事务：事务没有明显的开启和结束的标记
            比如insert、update、delete语句
            delete from 表 where id = 1; #自动开启事务

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

            步骤1：开启事务
                  set autocommit = 0;
                  start transaction;可选的
            步骤2：编写事务中的sql语句 ( select insert update delete )
                  语句1；
                  语句2；
                 【savepoint 节点名;设置保存点‘】
                  …；
            步骤3：结束事务
                  commit;提交事务
                  rollback;回滚事务
                  roll back to 节点名;

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

查看隔离级别
       select @@tx_isolation
设置隔离级别
       SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别

SHOW ENGINES; #查看存储引擎
# mysql中用的最多的存储引擎有：innodb、myisam、memory。
# 其中innodb支持事务，myisam、memory等不支持事务
SHOW VARIABLES LIKE 'autocommit' #查询事务状态

一、演示事务的使用步骤

1.初始表创建

DROP TABLE IF EXISTS account;
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000)

2.演示事务的使用步骤

#开启事务
SET autocommit = 0;
START TRANSACTION

#编写一组事务的语句
UPDATE account SET balance = 500 WHERE username = '张无忌';
UPDATE account SET balance = 1500 WHERE username = '赵敏';

#结束事务
COMMIT;
SELECT * FROM account

#开启事务
SET autocommit = 0;
START TRANSACTION

#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username = '张无忌';
UPDATE account SET balance = 1000 WHERE username = '赵敏';

#结束事务
ROLLBACK; #结果保持不变

二、演示事务的隔离级别

#查看隔离级别
SELECT @@tx_isolation
#修改隔离级别为：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

一次提交修改

提交先后读取没有变化，此为可重复读。

此时提交此次事务，再开一个事务读取，即改变。

可以发现该隔离级别解决了脏读、可重复读，但未解决幻读。

——————————————————————————————————————————————————————
查询结束后一次还未执行修改语句的事务

此时另一个事务进行了提交插入操作

此时执行修改语句，发现4行受影响，而当时查询只有3行，此即为幻读。

此时采用最高隔离级别

#修改隔离级别为：serializable（串行化）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

同上未执行修改语句的事务

此时将阻塞另一个事务的插入操作

只有当前一个事务提交，才能进行插入操作。


三、演示savepoint的使用

SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id = 28;
ROLLBACK TO a;#回滚到保存点
#回滚到a导致28号未删除

四、delete和truncate在事务使用时的区别

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

       delete支持回滚

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

       truncate不支持回滚