MySql的坑- 临时表操作对事务的影响

背景

做了一个存储过程,里面3个子过程。子过程里都用了临时表。
伪码类似这样。
可调试时,Sub3里出错中断后发现Sub1,Sub2的结果并未回滚。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING
BEGIN
ROLLBACK;
END
START TRANSACTION;
-- sub1
call sub1();
-- sub2
call sub2();
-- sub3
call sub3();
COMMIT;
END

原因排查

开始以为子过程不能继承事务,做了个简单的子过程更新证明可以回滚。
那么原因就是临时表了。可是找了很多地方都没有说临时表会强制提交事务的说明。
直到找到 这里
才找到真正的原因。
是对临时表做DDL操作时少了一个单词【temporary】【temporary】【temporary】
重要的事情说3遍。

下面2句,在没有名字叫t1的物理表时,对表的操作效果一样,但2在执行前会强制提交事务,而1不会。

  1. drop temporary table t1;
  2. drop table t1;

总结

大家用临时表时都会删除一下的,切记不要
drop table if exists tmptbl;
而是要
drop temporary table if exists tmptbl;

  1. 子过程可以回滚,START TRANSACTION 只在父过程声明即可
  2. 不用修改「SET autocommit=0;」
  3. 不可用DDL
  4. 对临时表的DDL不提交事务但是要明确写明是临时表操作 如Drop table if exists tmptbl;