| 故事背景
话说有一回, X 市 X 公司的产品经理 Douni 兴致冲冲的跑来和 Sum(Sum,X 市 X 公司资历 8 年程序猿, 技能: 深思, 熟虑, 心细, 深究, 技术过敏. 口头禅: 嗯, 容我想想. 坚信: 只要赚钱的业务, 我都可以让一枚程序猿完成, 如果不行, 那就再加一枚.)说:"最近公司的 B2C 业务不景气, 需要开发代理功能, 我们产品部正在开产品研讨会, 要不要一起来参加.",Sum 摸了一把下巴, 嘴角露出一丝诡异的笑容, 目光沿着 45 度角向产品经理投射过去, 说:"容我, 想想.". 话音未落, 已被 Douni 抬离卡座, 电光火石间仿佛听到 Doubi 那久久未能散去的余音:"这事没你不行!!!"
| 需求分析
Sum 在产品研讨会上讨论 (激烈争吵) 之后, 最终确定了需求.
需求定义是在 X 公司的一个 B2C 电商平台增加一个代理功能, 让指定的老用户升级为代理, 从而推动该电商的流水, 代理也可以从中获取到可观的报酬.
这个需求对于资深, 心细的 Sum 来说, 并未有挑战. 但是 Sum 虽然自称是猿, 但毕竟来说, 还是一个小团队的头儿. 所以, 在经过整理需求之后, 发现了一个比较容易犯错, 且若交给队员去实现的话, 有延期风险的技术难点. 所以, Sum 决心自己先过一遍核心思路, 再把该功能交给队友去做.
| 开干
该功能点对于 Sum 来说, 并不难. 主要是在该平台高并发下, 代理利润分成的结算. 这设计到了数据的一致性和可靠性. 在有限的开发条件下, Sum 的第一念头是写一个存储过程, 在存储过程当中, 使用事务对数据进行修改. 于是, Sum 在分配完其余工作之后, 开始着手编写这个存储过程.
Sum 打开 phpstudy, 开启 mysql(项目用的是 PHP,mysql 的环境), 然后打开 SQLyog, 进入本地的环境.
很熟练的, 新建了一个数据库 demo.
接着建立用户表 names, 注意, 该表的存储引擎是 InnoDB(InnoDB 具有事务回滚, 提交, 原子性等功能)
- CREATE TABLE `names` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(10) DEFAULT NULL,
- PRIMARY KEY (`id`),
- UNIQUE KEY `name` (`name`)
- ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
再建立一张资金表 testa:
- CREATE TABLE `testa` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(40) DEFAULT NULL,
- `money` decimal(10,2) DEFAULT NULL,
- PRIMARY KEY (`id`),
- UNIQUE KEY `name` (`name`)
- ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
万事已具备, 只欠编写代码了. Sum 坐在电脑前, 深思了一会. 突然间, 双手放在键盘上...
经过了大概十几分钟, 显示器出现了一连串代码:
- DELIMITER $$
- USE `demo`$$
- DROP PROCEDURE IF EXISTS `test`$$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN id INT(11) UNSIGNED, IN m DECIMAL(10,2) UNSIGNED)
- test:BEGIN
- DECLARE t_error INT DEFAULT 0;
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
SELECT `name` INTO @t_name FROM `names` WHERE `id`= id;-- 查出用户的信息
SET AUTOCOMMIT = 0;-- 关闭自动提交 SQL 语句
START TRANSACTION;-- 开启事务, 当然开启事务还有 BEGIN
SELECT `money` INTO @money FROM `testa` WHERE `name`=@t_name FOR UPDATE;-- 查询当前余额, 这一步为什么查询出来而不是直接锁? 大家思考下.
UPDATE `testa` SET `money`=@money+m WHERE `name`=@t_name;-- 更新资金库
IF t_error = 1 THEN-- 失败则回滚
- ROLLBACK;
- ELSE
COMMIT;-- 成功则提交
END IF;
SELECT t_error;-- 返回该事务的处理代码 0|1
- END$$
- DELIMITER ;
Sum 摸了一把下巴, 满意的喝了一口咖啡, Ctrl+F9, 这个存储过程就创建好了.
接下来就是调试阶段, 任何程序猿对外都会说, 我的代码是没有 BUG 的, 即使心中对自己有一万个为什么. 对于自认为骨子里有架构师天分的 Sum 来说, 在未对外吹牛逼的时候, 自测是非常重要的环节. 于是他兴奋的打开了一个查询窗口, 潇潇洒洒的写上了一句:
call .test.(1,33.00);
这段代码他心中已经运行了好几遍, 也检查了好几遍, 自认为并未有传说中程序杀手 --BUG-- 的存在, 充满自信的按下了 Ctrl+F9.
万万没想到, 运行结果竟然是!!!!!
非常刺激!!!Sum 手一抖, 咖啡差些洒在办公桌上!! 昂无里窝波儿!! 简直不敢相信!!Sum 把咖啡往桌上一放, 目光立即盯着该段存储过程的代码, 一行行往下扫去!!!
"没问题啊没问题啊, 问题出在哪里啊!!!" 脑中蹦出无数个没问题, Sum 此时都快疯了. 心想,"幸好, 不把这个工作丢给队员, 这操作简直太正确了!!!"
在思考无果后, Sum 各种百度谷歌.....(省去一小时寂静无语的时光)
终于在网上找到了一个解决方案
-- 如果出现执行异常则结束后继的执行, 并执行 begin-end 中的处理
- DECLARE EXIT HANDLER FOR SQLEXCEPTION
- BEGIN
-- 回滚事务
ROLLBACK;
-- 获取错误信息
GET DIAGNOSTICS CONDITION 1 @p1=RETURNED_SQLSTATE,@p2= MESSAGE_TEXT;
-- 借 "模拟" 抛出异常
- RESIGNAL SET schema_name = 'mtt_dev',
- table_name = 'tb_test',
- message_text = @p2,
- mysql_errno = @p1;
- END;
当 Sum 把代码小心翼翼的合并到存储过程中后, 按下 Ctrl+F9, 竟然报错了
错误代码: 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DIAGNOSTICS CONDITION 1 @p1=RETURNED_SQLSTATE,@p2= MESSAGE_TEXT;-- 借"' at line 10
原来, 这个解决方案是在 mysql5.6 版本或以上版本才能使用, 而 Sum 本地的环境是 5.1 版本的 mysql, 所以提示了报错. MySQL 5.6 提供了 get diagnostic 语句来获取错误缓冲区的内容
这一方案在电光火石间就被抛弃了. Sum 又陷入了新一轮的惆怅当中, 可是有着架构师天赋的 Sum 岂是说放弃就放弃的? 于是, 他开始了新一轮的调试.
Sum 每一行每一行的进行调试, 也不忘在 X 技术群里问老前辈们.
终于有发现!!!(已经过了 3 个小时)
Sum 发现, 在第一句 "SELECT `name` INTO @t_name FROM `names` WHERE `id`= id LIMIT 1" 加上 LIMIT 1, 竟然就过了!!!
如法炮制, Sum 把剩余的有关查询和更新的语句都加上 LIMIT 1, 然后按下 Ctrl+F9, 再次拿起咖啡, 点击运行 call .test.(1,33.00);
完美运行!!!
Sum 这可高兴坏了, 一看时间, 已经是 17:00 了, 于是放下咖啡, 快速的编写技术方案, 做好 demo, 交付给队员!
| 最终代码
- DELIMITER $$
- USE `demo`$$
- DROP PROCEDURE IF EXISTS `test`$$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN id INT(11) UNSIGNED, IN m DECIMAL(10,2) UNSIGNED)
- test:BEGIN
- DECLARE t_error INT DEFAULT 0;
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
SELECT `name` INTO @t_name FROM `names` WHERE `id`= id LIMIT 1;-- 查出用户的信息
SET AUTOCOMMIT = 0;-- 关闭自动提交 SQL 语句
START TRANSACTION;-- 开启事务, 当然开启事务还有 BEGIN
SELECT `money` INTO @money FROM `testa` WHERE `name`=@t_name LIMIT 1 FOR UPDATE;-- 查询当前余额, 这一步为什么查询出来而不是直接锁? 大家思考下.
UPDATE `testa` SET `money`=@money+m WHERE `name`=@t_name LIMIT 1;-- 更新资金库
IF t_error = 1 THEN-- 失败则回滚
- ROLLBACK;
- ELSE
COMMIT;-- 成功则提交
END IF;
SELECT t_error,@t_name;-- 返回该事务的处理代码 0|1
- END$$
- DELIMITER ;
| 总结
1. 当在事务中 select [字段] into @用户变量 的时候, 请确保结果集是一条, 如果是多条, 请使用游标!
2. 使用 for update 的时候, 一定要命中索引字段, 不然会锁表, 违背了高并发时候, 处理事务的初衷
3. 小的表, 没有索引, 尽量的就不用 for update, 因为 mysql 的执行计划
4.for update 叫做悲观锁, 命中索引的时候, 叫做行锁, 不命中的时候, 叫做表锁, 如果结果集为空, 则无锁!
来源: https://www.cnblogs.com/programmerVIP/p/9567514.html