CALL SP_ExecuteImportTestCase(1,1,1,'temptable20110421t1736561303378616068');
[Err] 1568 - Transaction isolation level can't be changed while a transaction is in progress
SP_ExecuteImportTestCase内容:BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET AUTOCOMMIT = 0; START TRANSACTION; /*alter temporary table*/
CALL SP_PrepareTemporaryTable (ContainerID, TemporaryTableName); /*import testsuites*/
CALL SP_ImportTestSuites (TestProjectID, ContainerID, TemporaryTableName); /*import testcases*/
CALL SP_ImportTestCases (TestProjectID, UserID, ContainerID, TemporaryTableName); COMMIT; SET AUTOCOMMIT = 1; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
END
mysql里事务嵌套会不会出问题
---TRANSACTION 0 1591237, ACTIVE 4 sec, OS thread id 3640
18 lock struct(s), heap size 2496, 482 row lock(s), undo log entries 236
MySQL thread id 11, query id 632 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 1591238, sees < 0 1591238这是SHOW INNODB STATUS里的信息,显示有锁,但是具体不知道是怎么造成的
用这个能找到是哪个查询出了问题吗
2,调用的第一条SP里面确实有DLL语句,后面两条SP没有,改为下面这样以后,问题依然是一样的,而且只有出错回滚的时候才会出现等待锁的情况,如果执行过程没什么问题的话,不会锁表BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET AUTOCOMMIT = 0; /*alter temporary table*/
CALL SP_PrepareTemporaryTable (ContainerID, TemporaryTableName); START TRANSACTION;
/*import testsuites*/
CALL SP_ImportTestSuites (TestProjectID, ContainerID, TemporaryTableName); /*import testcases*/
CALL SP_ImportTestCases (TestProjectID, UserID, ContainerID, TemporaryTableName); COMMIT; SET AUTOCOMMIT = 1; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
END
[SQL] delete from nodes_hierarchy where node_type_id<>1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
DECLARE exit HANDLER FOR sqlexception rollback;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;