技术开发►db

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；

完