由于回滚造成的死锁

Question

现在碰到一个头疼的问题，有一个事务对A,B,C三表进行了更新，插入操作，如果执行的时候没有错误那么一切正常；
但是如果过程中有错误，那么在回滚了以后，则对这几表只能SELECT，如果是UPDATE或者DELETE的话就一直会处于等待锁的状态，最后报超时，不知道解决这种问题该从何处着手。

Answer 1

一直处于等待状态，不代表就是死锁。SHOW INNODB STATUS;看看是否有死锁。事务的回滚是对事务的反向操作，INSERT的反向为DELETE，同样需要对表进行X锁。如果这个回滚操作没有完成，其他事务再请求加X锁，就需要等待。还是看看你的事务里的SQL是否已经回滚完成了。

Answer 2

从数据上来看已经回滚完成了，数据都是未执行前的状态，SHOW INNODB STATUS的信息是这样的，能解释下吗：
------------
TRANSACTIONS
------------
Trx id counter 0 1593853
Purge done for trx's n:o < 0 1593846 undo n:o < 0 0
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3968
MySQL thread id 191, query id 3892 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 1593852, not started, OS thread id 3332
MySQL thread id 4, query id 3889 localhost 127.0.0.1 root
---TRANSACTION 0 0, not started, OS thread id 3268
MySQL thread id 2, query id 3655 localhost 127.0.0.1 root
---TRANSACTION 0 1593847, ACTIVE 18 sec, OS thread id 3384
15 lock struct(s), heap size 2496, 482 row lock(s), undo log entries 236
MySQL thread id 181, query id 3662 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 1593848, sees < 0 1593848

Answer 3

我刚才又测试了一下，SP里面只有一个INSERT语句，用START transaction;COMMIT嵌套住以后，故意加一个错误的语句在里面，数据回滚了，但是进程还在，表依然是被锁了，怎么会这样的；付我的测试语句：START TRANSACTION;INSERT INTO `nodes_hierarchy`(
`name`,
`parent_id`,
`node_type_id`,
`node_order`
)SELECT
`Name`,
NULL,
2,
`ID`
FROM
temptable20110421t1736561303378616068
WHERE
Type = 'S'
ORDER BY
`ID`;a;COMMIT

Answer 4

参照下面这样的事务写法
create PROCEDURE aa(SqlCMD1 varchar(8000),SqlCMD2 varchar(8000),SqlCMD3 varchar(8000))
begin 
    declare exit handler for sqlexception rollback;
    start TRANSACTION;
    
    PREPARE stmt_name FROM SqlCMD1;
    EXECUTE stmt_name;
    DEALLOCATE stmt_name;
    PREPARE stmt_name FROM SqlCMD2;
    EXECUTE stmt_name;
    DEALLOCATE stmt_name;
    PREPARE stmt_name FROM SqlCMD3;
    EXECUTE stmt_name;
    DEALLOCATE stmt_name;
    COMMIT;
end