笔记五

事务详解

• 什么是事务
  • 数据库中的事务是指对数据库执⾏⼀批操作，这些操作最终要么全部执⾏成功，要么全部失败，不会存在部分成功的情况
• 事务的⼏个特性(ACID)
  • 原⼦性(Atomicity) ：事务的整个过程如原⼦操作⼀样，最终要么全部成功，或者全部失败，这个原⼦性是从最终结果来看的，从最终结果来看这个过程是不可分割的
  • ⼀致性(Consistency) ：⼀个事务必须使数据库从⼀个⼀致性状态变换到另⼀个⼀致性状态
  • 隔离性(Isoladon) ：⼀个事务的执⾏不能被其他事务⼲扰。即⼀个事务内部的操作及使⽤的数据对并发的其他事务是隔离的，并发执⾏的各个事务之间不能互相⼲扰
  • 持久性(Durability) ：⼀个事务⼀旦提交，他对数据库中数据的改变就应该是永久性的。当事务提交之后，数据会持久化到硬盘，修改是永久性的
• Mysql中事务操作
  • mysql中事务默认是隐式事务，执⾏insert、update、delete操作的时候，数据库⾃动开启事务、提交或回滚事务
  • 是否开启隐式事务是由变量autocommit控制的
  • 隐式事务：事务⾃动开启、提交或回滚，⽐如insert、update、delete语句，事务的开启、提交或回滚由mysql内部⾃动控制的
  • 显式事务：事务需要⼿动开启、提交或回滚，由开发者⾃⼰控制。start transaction;开启事务，commit;提交，rollback;回滚
• savepoint关键字
  • 在事务中我们执⾏了⼀⼤批操作，可能我们只想回滚部分数据
  • 我们可以将⼀⼤批操作分为⼏个部分，然后指定回滚某个部分
  • 需要结合rollback to sp1⼀起使⽤，可以将保存点sp1到rollback to之间的操作回滚掉
• 只读事务
  • 表⽰在事务中执⾏的是⼀些只读操作，如查询，但是不会做insert、update、delete操作，数据库内部对只读事务可能会有⼀些性能上的优化
  • ⽤法如：start transaction read only;

-- savepoint示例
start transaction;
insert into test1 values (1);
savepoint part1;//设置⼀个保存点
insert into test1 values (2);
rollback to part1; //将savepint = part1的语句到当前语句之间所有的操作回滚
commit; //提交事务
-- [1]
select * from test1;
-- 只读事务中执⾏delete会报错
start transaction read only;
select * from test1;
-- Cannot execute statement in a READ ONLY transaction.
delete from test1;

• 事务中的⼀些问题
  • 脏读：⼀个事务在执⾏的过程中读取到了其他事务还没有提交的数据
  • 读已提交：即⼀个事务操作过程中可以读取到其他事务已经提交的数据
  • 可重复读：⼀个事务操作中对于⼀个读取操作不管多少次，读取到的结果都是⼀样的
  • 幻读：在可重复读的模式下才会出现，其他隔离级别中不会出现
  • 幻读现象例⼦如下

可重复读模式下，⽐如有个⽤户表，⼿机号码为主键，有两个事物进⾏如下操作
事务A操作如下：    1、打开事务    2、查询号码为X的记录，不存在    3、插⼊号码为X的数
据，插⼊报错（为什么会报错，先向下看）    4、查询号码为X的记录，发现还是不存在
（由于是可重复读，所以读取记录X还是不存在的）
事物B操作：在事务A第2步操作时插⼊了⼀条X的记录，所以会导致A中第3步插⼊报错
（违反了唯⼀约束）
上⾯操作对A来说就像发⽣了幻觉⼀样，明明查询X（A中第⼆步、第四步）不存在，但却
⽆法插⼊成功
幻读可以这么理解：事务中后⾯的操作（插⼊号码X）需要上⾯的读取操作（查询号码X
的记录）提供⽀持，但读取操作却不能⽀持下⾯的操作时产⽣的错误，就像发⽣了幻觉⼀
样。

• 事务的隔离级别
  • 读未提交：READ-UNCOMMITTED
    • 读未提交情况下，可以读取到其他事务还未提交的数据，多次读取结果不⼀样，出现了脏读、不可重复读
  • 读已提交：READ-COMMITTED
    • 读已提交情况下，⽆法读取到其他事务还未提交的数据，可以读取到其他事务已经提交的数据，多次读取结果不⼀样，未出现脏读，出现了读已提交、不可重复读
  • 可重复读：REPEATABLE-READ
    • 可重复读情况下，未出现脏读，未读取到其他事务已提交的数据，多次读取结果⼀致，即可重复读，但是会出现幻读情况
  • 串⾏：SERIALIZABLE
    • 让并发的事务串⾏执⾏（多个事务之间读写、写读、写写会产⽣互斥，效果就是串⾏执⾏，多个事务之间的读读不会产⽣互斥）。串⾏情况下不存在脏读、不可重复读、幻读的问题
  • 四种隔离级别越来越强，会导致数据库的并发性也越来越低
  • 读已提交（READ-COMMITTED）通常⽤的⽐较多

游标详解

• 游标定义：是处理数据的⼀种⽅法，为了查看或者处理结果集中的数据，游标提供了
  在结果集中⼀次⼀⾏遍历数据的能⼒。游标只能在存储过程和函数中使⽤。
• 游标作⽤：相当于⼀个指针，这个指针指向select的第⼀⾏数据，可以通过移动指针来遍历后⾯的数据
• 游标使⽤步骤
  • 声明游标：这个过程只是创建了⼀个游标，需要指定这个游标需要遍历的select查询，声明游标时并不会去执⾏这个sql
  • 打开游标：打开游标的时候，会执⾏游标对应的select语句
  • 遍历数据：使⽤游标循环遍历select结果中每⼀⾏数据，然后进⾏处理
  • 关闭游标：游标使⽤完之后⼀定要关闭。
• 游标执⾏过程详解
  • 声明游标：DECLARE 游标名称 CURSOR FOR 查询语句;，⼀个begin end中只能声明⼀个游标
  • 打开游标：open 游标名称;
  • 遍历游标：fetch 游标名称 into 变量列表;
    • 取出当前⾏的结果，将结果放在对应的变量中，并将游标指针指向下⼀⾏的数据
    • 当调⽤fetch的时候，会获取当前⾏的数据，如果当前⾏⽆数据，会引发mysql内部的NOT FOUND错误
  • 关闭游标：close 游标名称;，游标使⽤完毕之后⼀定要关闭
• 单游标⽰例
  • 可能有问题：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary
  • 解决办法1：执⾏ SET GLOBAL logbintrustfunctioncreators = 1;，不过 重启了就失效了，且有主从复制的时候从机必须要设置，不然会导致主从同步失败
  • 解决办法2：在my.cnf⾥⾯设置 log-bin-trust-function-creators=1，不过这个需要重启服务

/*删除函数*/
DROP FUNCTION IF EXISTS fun1;
/*声明结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun1(v_max_a int)
 RETURNS int
 BEGIN
 /*⽤于保存结果*/
 DECLARE v_total int DEFAULT 0;
 /*创建⼀个变量，⽤来保存当前⾏中a的值*/
 DECLARE v_a int DEFAULT 0;
 /*创建⼀个变量，⽤来保存当前⾏中b的值*/
 DECLARE v_b int DEFAULT 0;
 /*创建游标结束标志变量*/
 DECLARE v_done int DEFAULT FALSE;
 /*创建游标*/
 DECLARE cur_test1 CURSOR FOR SELECT a,b from test1 where
a<=v_max_a;
 /*设置游标结束时v_done的值为true，可以v_done来判断游标是否结束了*/
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;
 /*设置v_total初始值*/
 SET v_total = 0;
 /*打开游标*/
 OPEN cur_test1;
 /*使⽤Loop循环遍历游标*/
 a:LOOP
 /*先获取当前⾏的数据，然后将当前⾏的数据放⼊v_a,v_b中，如果当前⾏⽆数据，
v_done会被置为true*/
 FETCH cur_test1 INTO v_a, v_b;
 /*通过v_done来判断游标是否结束了，退出循环*/
 if v_done THEN
 LEAVE a;
 END IF;
 /*对v_total值累加处理*/
 SET v_total = v_total + v_a + v_b;
 END LOOP;
 /*关闭游标*/
 CLOSE cur_test1;
 /*返回结果*/
 RETURN v_total;
 END $
/*结束符置为;*/
DELIMITER ;

• 游标过程详解

游标中有个指针，当打开游标的时候，才会执⾏游标对应的select语句，这个指针会指向
select结果中第⼀⾏记录。
当调⽤fetch 游标名称时，会获取当前⾏的数据，如果当前⾏⽆数据，会触发NOT FOUND
异常。
当触发NOT FOUND异常的时候，我们可以使⽤⼀个变量来标记⼀下，如下代码：
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;
当游标⽆数据触发NOT FOUND异常的时候，将变量v_down的值置为TURE，循环中就可以
通过v_down的值控制循环的退出。
如果当前⾏有数据，则将当前⾏数据存到对应的变量中，并将游标指针指向下⼀⾏数据，
如下语句：
fetch 游标名称 into 变量列表;

• 嵌套游标⽰例

-- 写个存储过程，遍历test2、test3，将test2中的a字段和test3中的b字段任意组合，插⼊到test1表中
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc1;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc1()
 BEGIN
 /*创建⼀个变量，⽤来保存当前⾏中a的值*/
 DECLARE v_a int DEFAULT 0;
 /*创建游标结束标志变量*/
 DECLARE v_done1 int DEFAULT FALSE;
 /*创建游标*/
 DECLARE cur_test1 CURSOR FOR SELECT a FROM test2;
 /*设置游标结束时v_done1的值为true，可以v_done1来判断游标cur_test1是否结束
了*/
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done1=TRUE;
 /*打开游标*/
 OPEN cur_test1;
 /*使⽤Loop循环遍历游标*/
 a:LOOP
 FETCH cur_test1 INTO v_a;
 /*通过v_done1来判断游标是否结束了，退出循环*/
 if v_done1 THEN
 LEAVE a;
 END IF;
 BEGIN
 /*创建⼀个变量，⽤来保存当前⾏中b的值*/
 DECLARE v_b int DEFAULT 0;
 /*创建游标结束标志变量*/
 DECLARE v_done2 int DEFAULT FALSE;
 /*创建游标*/
 DECLARE cur_test2 CURSOR FOR SELECT b FROM test3;
 /*设置游标结束时v_done1的值为true，可以v_done1来判断游标cur_test2是否
结束了*/
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done2=TRUE;
 /*打开游标*/
 OPEN cur_test2;
 /*使⽤Loop循环遍历游标*/
 b:LOOP
 FETCH cur_test2 INTO v_b;
 /*通过v_done1来判断游标是否结束了，退出循环*/
 if v_done2 THEN
 LEAVE b;
 END IF;
 /*将v_a、v_b插⼊test1表中*/
 INSERT INTO test1 VALUES (v_a,v_b);
 END LOOP b;
 /*关闭cur_test2游标*/
 CLOSE cur_test2;
 END;
 END LOOP;
 /*关闭游标cur_test1*/
 CLOSE cur_test1;
 END $
/*结束符置为;*/
DELIMITER ;

• 总结
  • 游标⽤来对查询结果进⾏遍历处理
  • 游标的使⽤过程：声明游标、打开游标、遍历游标、关闭游标
  • 游标只能在存储过程和函数中使⽤
  • 游标只能在存储过程和函数中使⽤

异常捕获及处理详解

• 内部异常：当我们执⾏⼀些sql的时候，可能违反了mysql的⼀些约束，导致mysql内部报错，如插⼊数据违反唯⼀约束，更新数据超时等，此时异常是由mysql内部抛出的，我们将这些由mysql抛出的异常统称为内部异常

-- a字段为主键，模拟Mysql内部异常
-- 重点是：DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;
-- 当有sql异常的时候，会将变量hasSqlError的值置为TRUE。
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc2;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc2(a1 int,a2 int)
 BEGIN
 /*声明⼀个变量，标识是否有sql异常*/
 DECLARE hasSqlError int DEFAULT FALSE;
 /*在执⾏过程中出任何异常设置hasSqlError为TRUE*/
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;
 /*开启事务*/
 START TRANSACTION;
 INSERT INTO test1(a) VALUES (a1);
 INSERT INTO test1(a) VALUES (a2);
 /*根据hasSqlError判断是否有异常，做回滚和提交操作*/
 IF hasSqlError THEN
 ROLLBACK;
 ELSE
 COMMIT;
 END IF;
 END $
/*结束符置为;*/
DELIMITER ;

• 外部异常：当我们执⾏⼀个update的时候，可能我们期望影响1⾏，但是实际上影响的不是1⾏数据，这种情况：sql的执⾏结果和期望的结果不⼀致，这种情况也我们也把他作为外部异常处理，我们将sql执⾏结果和期望结果不⼀致的情况统称为外部异常

-- 模拟电商中下单操作
-- 2个窗同时执⾏第⼀步的时候看到了⼀样的数据，然后继续向下执⾏，最终导致结果出问题
-- 可以使⽤乐观锁来优化
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc3;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc3(v_user_id int,v_price decimal(10,2),OUT v_msg
varchar(64))
 a:BEGIN
 DECLARE v_available DECIMAL(10,2);
 /*1.查询余额，判断余额是否够*/
 select a.available into v_available from t_funds a where a.user_id
= v_user_id;
 if v_available<=v_price THEN
 SET v_msg='账户余额不⾜!';
 /*退出*/
 LEAVE a;
 END IF;
 /*模拟耗时5秒*/
 SELECT sleep(5);
 /*2.余额减去price*/
 SET v_available = v_available - v_price;
 /*3.更新余额*/
 START TRANSACTION;
 UPDATE t_funds SET available = v_available WHERE user_id =
v_user_id;
 /*插⼊订单明细*/
 INSERT INTO t_order (price) VALUES (v_price);
 /*提交事务*/
 COMMIT;
 SET v_msg='下单成功!';
 END $
/*结束符置为;*/
DELIMITER ;

• 掌握乐观锁解决并发修改数据出错的问题：⽤期望的值和⽬标值进⾏⽐较，如果相同，则更新⽬标值，否则什么也不做

-- 创建表
DROP TABLE IF EXISTS t_funds;
CREATE TABLE t_funds(
 user_id INT PRIMARY KEY COMMENT '⽤户id',
 available DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '账户余额',
 version INT DEFAULT 0 COMMENT '版本号，每次更新+1'
) COMMENT '⽤户账户表';
-- ROW_COUNT()可以获取更新或插⼊后获取受影响⾏数。将受影响⾏数放在
v_update_count中
-- 然后根据v_update_count是否等于1判断更新是否成功，如果成功则记录订单信息并提交事务，否则回滚事务
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc4;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc4(v_user_id int,v_price decimal(10,2),OUT v_msg 
varchar(64))
 a:BEGIN
 /*保存当前余额*/
 DECLARE v_available DECIMAL(10,2);
 /*保存版本号*/
 DECLARE v_version INT DEFAULT 0;
 /*保存影响的⾏数*/
 DECLARE v_update_count INT DEFAULT 0;
 /*1.查询余额，判断余额是否够*/
 select a.available,a.version into v_available,v_version from
t_funds a where a.user_id = v_user_id;
 if v_available<=v_price THEN
 SET v_msg='账户余额不⾜!';
 /*退出*/
 LEAVE a;
 END IF;
 /*模拟耗时5秒*/
 SELECT sleep(5);
 /*2.余额减去price*/
 SET v_available = v_available - v_price;
 /*3.更新余额*/
 START TRANSACTION;
 UPDATE t_funds SET available = v_available WHERE user_id =
v_user_id AND version = v_version;
 /*获取上⾯update影响⾏数*/
 select ROW_COUNT() INTO v_update_count;
 IF v_update_count=1 THEN
 /*插⼊订单明细*/
 INSERT INTO t_order (price) VALUES (v_price);
 SET v_msg='下单成功!';
 /*提交事务*/
 COMMIT;
 ELSE
 SET v_msg='下单失败,请重试!';
 /*回滚事务*/
 ROLLBACK;
 END IF;
 END $
/*结束符置为;*/
DELIMITER ;

• 总结
  • 异常分为mysql内部异常和外部异常
  • 内部异常由mysql内部触发，外部异常是sql的执⾏结果和期望结果不⼀致导致的错误
  • sql内部异常捕获⽅式
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;
  • ROW_COUNT()可以获取mysql中insert或者update影响的⾏数
  • 掌握使⽤乐观锁（添加版本号）来解决并发修改数据可能出错的问题
  • begin end前⾯可以加标签，LEAVE 标签可以退出对应的begin end，可以使⽤这个来实现return的效果