进阶15：TCL Transaction Control Language 事务控制语言

进阶15：TCL
Transaction Control Language 事务控制语言

1.什么是事务？
事务是由一个或一组SQL语句组成的一个执行单元（是一个整体），这个执行单元要么全部执行，要么全部不执行。

MySQL事务主要用于处理操作量大、复杂度高的数据。
比如说，在人员管理系统CRM中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱、文章等等，这样，这些数据库操作语句就构成一个事务。

（1）在MySQL 中只有使用了Innodb数据库引擎的数据库或表才支持事务。
（2）事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
（3）事务用来管理insert、update、delete语句。

2.案例：转账
ID 姓名 存款
1 张三丰 1000.00
2 郭襄 1000.00

转账：张三丰转500元给郭襄
update 表 set 张三丰的余额=存款-500 where name=‘张三丰’
意外
update 表 set 郭襄的余额=存款+500 where name=‘郭襄’

3.事务的特性ACID
原子性（Atomicity，又称不可分割性）：
一个事务不可再分割，要么都执行要么都不执行
一个事务（transaction）中的所有操作，要么全部执行，要么全部不执行，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性（Consistency）：
一个事务执行会使数据从一个一致状态切换到另外一个一致状态
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。（比如：A向B转账，不可能A扣了钱，B却没有收到）

隔离性（Isolation，又称独立性）：
一个事务的执行不受其他事务的干扰
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（比u人：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）

持久性（Durability）：
一个事务一旦提交，则会永久的改变数据库的数据.
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

锁
行级锁
表级锁

脏数据

悲观锁
乐观锁

4.事务的分类
事务分两类：隐式事务 和 显示事务
（1）隐式事务：即事务没有明显的开启和结束的标记。
比如insert、update、delete语句，具有自动提交功能。
MySQL默认是自动提交，即：set autocommit=1;

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

5.创建事务
创建事务前，先检查事物有没有开启（查找可变参数autocommit）
show variables like ‘autocommit’;

隐式事务：会自动提交
delete from 表 where id =1;
rollback; – 发现删除掉的记录不能恢复，因为delete具有自动提交功能。

显式事务：
前提设置禁用自动提交事务功能：set autocommit = 0;
步骤1：开启事务
start transaction; – 手动开启事务，可选的

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

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

1.演示事务的使用步骤
#开启事务
SET autocommit = 0;
START TRANSACTION;

#编写一组事务的语句
UPDATE account SET balance = 5800 WHERE userName = ‘张无忌’;
UPDATE account SET balance = 5600 WHERE userName = ‘赵敏’;

#结束事务

[结束事物用 回滚 还是用 提交

要结合下周学的逻辑语句if[成功commmit]_else[失败rollback]

COMMIT; # 提交事物，一组事物的语句都执行
#rollback; # 回滚，一组事物的语句都不执行
SELECT * FROM account;

2.演示事务对于delete和truncate的处理的区别
#delete不加条件就是删整表数据，标识列 紧跟 断点+1，不会重置
#truncate删除整表数据，重新开始标识列，重置数据

演示delete

SET autocommit = 0;
START TRANSACTION;
DELETE FROM account; #没有删除，可以回滚。
ROLLBACK;

演示truncate

SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account; #删除数据，不可以回滚。
ROLLBACK;

结论：truncate的删除 无法进行回滚控制，delete可以回滚

3.演示savepoint 的使用【设置撤销点】
SET autocommit = 0;
START TRANSACTION;

DELETE FROM account WHERE id = 1;

SAVEPOINT a; #设置保存点 ,名字为a

DELETE FROM account WHERE id = 4;

rollback; 回滚到事物开启位置

ROLLBACK to a; # 回滚到保存点a【撤销到a点】

SELECT * FROM account;

SET autocommit = 0;
USE student;

CREATE TABLE tab1(
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(20) NOT NULL UNIQUE,
upwd VARCHAR(20) NOT NULL
)ENGINE=INNODB;

START TRANSACTION;
INSERT INTO tab1(uname, upwd) VALUES('王五', '123');
INSERT INTO tab1(uname, upwd) VALUES('王五', '456');
COMMIT;

SHOW SESSION VARIABLES;
SHOW SESSION VARIABLES LIKE '%log%';
SELECT @@session.autocommit;


-- 声明一个变量,并给变量赋值
SET @a = 100;
-- 输出变量的值
SELECT @a;

SELECT salary INTO @n FROM employees;
SELECT @n;

SELECT @m := salary FROM employees;
SELECT @m;




SELECT @b;

SET @c = @a + @b;
SELECT @c;


BEGIN
	-- 局部变量
	DECLARE n INT;
	-- 赋值
	SET n=100;
	-- 输出
	SELECT n;
END


-- 创建存储过程
-- 定义存储过程的结束符
DELIMITER $$
CREATE PROCEDURE proc_1()  #无参的存储过程
BEGIN
	-- 定义一个局部变量
	DECLARE n INT;
	-- 查询出员工号为100的员工工资
	-- select salary into n from employees where employee_id=100;
	SET n=1000;
	SELECT n;
END $$

-- 调用存储过程
CALL proc_1();




DELIMITER $$
CREATE PROCEDURE proc_2()  #无参的存储过程
BEGIN
	-- 定义一个局部变量
	DECLARE n INT;
	-- 查询出员工号为100的员工工资
	SELECT salary INTO n FROM employees WHERE employee_id=100;
	SELECT n;
END $$

CALL proc_2();





DELIMITER $$
CREATE PROCEDURE proc_3()  #无参的存储过程
BEGIN
	SELECT @n := salary FROM employees WHERE employee_id=100;
	SELECT @n;
END $$

CALL proc_3();



-- 带参的存储过程
DELIMITER $$
CREATE PROCEDURE proc_4(IN empID INT, OUT sala DOUBLE)
BEGIN
	SELECT salary INTO sala FROM employees WHERE employee_id = empID;
END $$


SET @aaa=0;
CALL proc_4(100, @aaa);
SELECT @aaa;