MySQL Forums :: InnoDB :: Innodb has Transaction LEAK?


Advanced Search

Innodb has Transaction LEAK?
Posted by: Chang Xu ()
Date: January 16, 2017 11:55PM

It seems impossible, but I can't understand this problem anymore.
My application runs on Mysql 5.7.14 (Windows 7). When It executes some tasks concurrently, one task meets a deadlock, and the application rollbacks it explicitly.
Then the terrible thing happens, one row inserted by this task(transaction) is still alive in the database!!

Following is the query-log:

2017-01-17T05:24:13.976667Z 578 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.976667Z 571 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.976667Z 574 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.976667Z 569 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.976667Z 576 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.976667Z 582 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.976667Z 579 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.976667Z 567 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.977667Z 572 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.977667Z 580 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.977667Z 581 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2017-01-17T05:24:13.978667Z 575 Query START TRANSACTION
2017-01-17T05:24:13.978667Z 578 Query START TRANSACTION
2017-01-17T05:24:13.978667Z 571 Query START TRANSACTION
2017-01-17T05:24:13.978667Z 574 Query START TRANSACTION
2017-01-17T05:24:13.978667Z 569 Query START TRANSACTION
2017-01-17T05:24:13.978667Z 576 Query START TRANSACTION
2017-01-17T05:24:13.978667Z 582 Query START TRANSACTION
2017-01-17T05:24:13.978667Z 579 Query START TRANSACTION
2017-01-17T05:24:13.978667Z 567 Query START TRANSACTION
2017-01-17T05:24:13.979667Z 572 Query START TRANSACTION
2017-01-17T05:24:13.979667Z 580 Query START TRANSACTION
2017-01-17T05:24:13.979667Z 581 Query START TRANSACTION
2017-01-17T05:24:13.979667Z 575 Query select `withDraw`.`price`, `withDraw`.`state`, `withDraw`.`id` from `withDraw` where ((`withDraw`.`state`='3') AND (`withDraw`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.980667Z 578 Query select `order2`.`price`, `order2`.`state`, `order2`.`id` from `order2` where ((`order2`.`state`=91) AND (`order2`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.980667Z 571 Query select `pay`.`state`, `pay`.`origin`, `pay`.`price`, `pay`.`_createAt_`, `pay`.`id` from `pay` where ((`pay`.`state`=3) AND (`pay`.`_updateAt_`<1484630653974) AND (`pay`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.980667Z 574 Query select `order2`.`deadline`, `order2`.`state`, `order2`.`callback`, `order2`.`id` from `order2` where ((`order2`.`deadline`<1484630653974) AND (`order2`.`state` in (1,5)) AND (`order2`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.981667Z 569 Query select `pay`.`state`, `pay`.`refundLine`, `pay`.`id` from `pay` where ((`pay`.`state`=10) AND (`pay`.`refundLine`<1484630653974) AND (`pay`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.981667Z 582 Query select `user`.`state`, `user`.`name`, `user`.`idStatus`, `user`.`idCard`, `user`.`idImages`, `user`.`id` from `user` where ((`user`.`idStatus` in (10,30)) AND (`user`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.982667Z 576 Query select `refund`.`state`, `refund`.`price`, `refund`.`payId`, `refund`.`_createAt_`, `refund`.`id` from `refund` where ((`refund`.`state`=899) AND (`refund`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.982667Z 579 Query select `user`.`state`, `user`.`name`, `user`.`idStatus`, `user`.`idCard`, `user`.`idImages`, `user`.`id` from `user` where ((`user`.`idStatus`=80) AND (`user`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.982667Z 567 Query select `lock2`.`ckwExpiredTime`, `lock2`.`id`, `lockType`.`id` as 'type.id' from `lock2` left join `lockType` `lockType` on `lock2`.`typeId` = `lockType`.`id` where ((`lock2`.`ckwExpiredTime`<=1484630653974) AND (`lock2`.`_deleteAt_` is null)) AND (`lockType`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:13.982667Z 572 Query select `key2`.`type`, `key2`.`state`, `key2`.`comeFromId`, `key2`.`ownerId`, `key2`.`id` from `key2` where ((`key2`.`state` in (11,12,2,5)) AND (`key2`.`expiredTime`<=1484630653974) AND (`key2`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.983667Z 569 Query select `orderPay`.`id`, `order2`.`state` as 'order.state', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`payId`=1) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:13.984667Z 580 Query select `message`.`props`, `message`.`smsTemplateCode`, `message`.`id`, `user`.`mobile` as 'user.mobile', `user`.`id` as 'user.id' from `message` left join `user` `user` on `message`.`userId` = `user`.`id` where ((`message`.`weight`=90) AND (`message`.`state` in (0,3)) AND (`message`.`smsTemplateCode` is not null) AND (`message`.`_createAt_`>=1484627053975) AND (`message`.`_deleteAt_` is null)) AND (`user`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:13.984667Z 581 Query select `config`.`data`, `config`.`id` from `config` where ((`config`.`name`='cos') AND (`config`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.984667Z 571 Query COMMIT
2017-01-17T05:24:13.985667Z 575 Query COMMIT
2017-01-17T05:24:13.985667Z 582 Query COMMIT
2017-01-17T05:24:13.985667Z 576 Query COMMIT
2017-01-17T05:24:13.986667Z 578 Query select `orderPay`.`orderId`, `order2`.`price` as 'order.price', `order2`.`state` as 'order.state', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`orderId` not in (4)) AND (`orderPay`.`payId` is not null) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) and exists (select `orderPay_1`.`id` from `orderPay` `orderPay_1` where ((`orderPay_1`.`orderId` in (4)) AND (`orderPay_1`.`payId` = `orderPay`.payId) AND (`orderPay_1`.`_deleteAt_` is null))) limit 0, 128
2017-01-17T05:24:13.986667Z 579 Query COMMIT
2017-01-17T05:24:13.986667Z 574 Query COMMIT
2017-01-17T05:24:13.986667Z 571 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.986667Z 579 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.987667Z 569 Query update `pay` set `state`=999,`_updateAt_`=1484630653987 where `id`=1
2017-01-17T05:24:13.987667Z 580 Query COMMIT
2017-01-17T05:24:13.987667Z 572 Query COMMIT
2017-01-17T05:24:13.987667Z 575 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.988667Z 582 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.988667Z 576 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.988667Z 567 Query COMMIT
2017-01-17T05:24:13.988667Z 581 Query COMMIT
2017-01-17T05:24:13.989667Z 580 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.989667Z 574 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.989667Z 569 Query select `orderPay`.`id`, `order2`.`price` as 'order.price', `order2`.`state` as 'order.state', `order2`.`callback` as 'order.callback', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`payId`=1) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:13.989667Z 572 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.990667Z 567 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.990667Z 581 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:13.990667Z 578 Query select `orderPay`.`id`, `pay`.`state` as 'pay.state', `pay`.`origin` as 'pay.origin', `pay`.`price` as 'pay.price', `pay`.`id` as 'pay.id' from `orderPay` left join `pay` `pay` on `orderPay`.`payId` = `pay`.`id` where ((`orderPay`.`orderId`=4) AND (`orderPay`.`_deleteAt_` is null)) AND ((`pay`.`state` in (10,99,101)) AND (`pay`.`_deleteAt_` is null)) limit 0, 128
2017-01-17T05:24:13.991667Z 569 Query select `orderRefund`.`id`, `refund`.`state` as 'refund.state', `refund`.`price` as 'refund.price', `refund`.`id` as 'refund.id' from `orderRefund` left join `refund` `refund` on `orderRefund`.`refundId` = `refund`.`id` where ((`orderRefund`.`orderId`=1) AND (`orderRefund`.`_deleteAt_` is null)) AND (`refund`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:13.993668Z 578 Query insert into `refund`(`payId`,`state`,`price`,`_createAt_`,`_updateAt_`) values(7,0,2000,1484630653992,1484630653992)
2017-01-17T05:24:13.993668Z 569 Query select `orderPay`.`orderId`, `order2`.`price` as 'order.price', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`orderId` not in (1)) AND (`orderPay`.`payId` is not null) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) and exists (select `orderPay_1`.`id` from `orderPay` `orderPay_1` where ((`orderPay_1`.`orderId` in (1)) AND (`orderPay_1`.`payId` = `orderPay`.payId) AND (`orderPay_1`.`_deleteAt_` is null))) limit 0, 128
2017-01-17T05:24:13.994668Z 578 Query insert into `refund`(`payId`,`state`,`price`,`_createAt_`,`_updateAt_`) values(5,0,1000,1484630653993,1484630653993)
2017-01-17T05:24:13.995668Z 578 Query insert into `orderRefund`(`orderId`,`refundId`,`_createAt_`,`_updateAt_`) values(4,3,1484630653994,1484630653994)
2017-01-17T05:24:13.996668Z 569 Query select `pay`.`state` as 'pay.state', `pay`.`price` as 'pay.price', `pay`.`id` as 'pay.id' from `orderPay` left join `pay` `pay` on `orderPay`.`payId` = `pay`.`id` where ((`orderPay`.`orderId`=1) AND (`orderPay`.`_deleteAt_` is null)) AND ((`pay`.`state` in (3,10,999,101,99,201)) AND (`pay`.`_deleteAt_` is null)) limit 0, 1000
2017-01-17T05:24:13.999668Z 569 Query select `order2`.`price`, `order2`.`rest`, `order2`.`allowCancel`, `order2`.`deadline`, `order2`.`txnUuid`, `order2`.`state`, `order2`.`autoPay`, `order2`.`mustPay`, `order2`.`extractableAfter`, `order2`.`availableAfter`, `order2`.`autoRefundAt`, `order2`.`title`, `order2`.`desc`, `order2`.`callback`, `order2`.`image`, `order2`.`type`, `order2`.`groupBy`, `order2`.`closedBy`, `order2`.`payerId`, `order2`.`receiverId`, `order2`.`_createAt_`, `order2`.`_updateAt_`, `order2`.`_deleteAt_`, `order2`.`id`, `user`.`mobile` as 'payer.mobile', `user`.`password` as 'payer.password', `user`.`tradePassword` as 'payer.tradePassword', `user`.`state` as 'payer.state', `user`.`disableReason` as 'payer.disableReason', `user`.`dangerousCoefficient` as 'payer.dangerousCoefficient', `user`.`nickname` as 'payer.nickname', `user`.`gender` as 'payer.gender', `user`.`birth` as 'payer.birth', `user`.`head` as 'payer.head', `user`.`name` as 'payer.name', `user`.`txnUuid` as 'payer.txnUuid', `user`.`idStatus` as 'payer.idStatus', `user`.`idCard` as 'payer.idCard', `user`.`idImages` as 'payer.idImages', `user`.`isVirtual` as 'payer.isVirtual', `user`.`agentId` as 'payer.agentId', `user`.`_createAt_` as 'payer._createAt_', `user`.`_updateAt_` as 'payer._updateAt_', `user`.`_deleteAt_` as 'payer._deleteAt_', `user`.`id` as 'payer.id', `user_1`.`mobile` as 'receiver.mobile', `user_1`.`password` as 'receiver.password', `user_1`.`tradePassword` as 'receiver.tradePassword', `user_1`.`state` as 'receiver.state', `user_1`.`disableReason` as 'receiver.disableReason', `user_1`.`dangerousCoefficient` as 'receiver.dangerousCoefficient', `user_1`.`nickname` as 'receiver.nickname', `user_1`.`gender` as 'receiver.gender', `user_1`.`birth` as 'receiver.birth', `user_1`.`head` as 'receiver.head', `user_1`.`name` as 'receiver.name', `user_1`.`txnUuid` as 'receiver.txnUuid', `user_1`.`idStatus` as 'receiver.idStatus', `user_1`.`idCard` as 'receiver.idCard', `user_1`.`idImages` as 'receiver.idImages', `user_1`.`isVirtual` as 'receiver.isVirtual', `user_1`.`agentId` as 'receiver.agentId', `user_1`.`_createAt_` as 'receiver._createAt_', `user_1`.`_updateAt_` as 'receiver._updateAt_', `user_1`.`_deleteAt_` as 'receiver._deleteAt_', `user_1`.`id` as 'receiver.id' from `order2` left join `user` `user` on `order2`.`payerId` = `user`.`id` left join `user` `user_1` on `order2`.`receiverId` = `user_1`.`id` where ((`order2`.`_deleteAt_` is null) AND (`order2`.`id`=1)) AND (`user`.`_deleteAt_` is null) AND (`user_1`.`_deleteAt_` is null) limit 0, 1
2017-01-17T05:24:14.000668Z 569 Query update `order2` set `state`=999,`_updateAt_`=1484630654000 where `id`=1
2017-01-17T05:24:14.001668Z 569 Query insert into `orderAction`(`orderId`,`prevState`,`nextState`,`operatorId`,`_createAt_`,`_updateAt_`) values(1,10,999,2,1484630654000,1484630654000)
2017-01-17T05:24:14.003668Z 569 Query select `orderPay`.`id`, `order2`.`state` as 'order.state', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`payId`=2) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:14.003668Z 569 Query update `pay` set `state`=999,`_updateAt_`=1484630654003 where `id`=2
2017-01-17T05:24:14.004668Z 569 Query select `orderPay`.`id`, `order2`.`price` as 'order.price', `order2`.`state` as 'order.state', `order2`.`callback` as 'order.callback', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`payId`=2) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:14.004668Z 569 Query select `orderRefund`.`id`, `refund`.`state` as 'refund.state', `refund`.`price` as 'refund.price', `refund`.`id` as 'refund.id' from `orderRefund` left join `refund` `refund` on `orderRefund`.`refundId` = `refund`.`id` where ((`orderRefund`.`orderId`=2) AND (`orderRefund`.`_deleteAt_` is null)) AND (`refund`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:14.005668Z 569 Query select `orderPay`.`orderId`, `order2`.`price` as 'order.price', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`orderId` not in (2)) AND (`orderPay`.`payId` is not null) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) and exists (select `orderPay_1`.`id` from `orderPay` `orderPay_1` where ((`orderPay_1`.`orderId` in (2)) AND (`orderPay_1`.`payId` = `orderPay`.payId) AND (`orderPay_1`.`_deleteAt_` is null))) limit 0, 128
2017-01-17T05:24:14.005668Z 569 Query select `pay`.`state` as 'pay.state', `pay`.`price` as 'pay.price', `pay`.`id` as 'pay.id' from `orderPay` left join `pay` `pay` on `orderPay`.`payId` = `pay`.`id` where ((`orderPay`.`orderId`=2) AND (`orderPay`.`_deleteAt_` is null)) AND ((`pay`.`state` in (3,10,999,101,99,201)) AND (`pay`.`_deleteAt_` is null)) limit 0, 1000
2017-01-17T05:24:14.006668Z 569 Query select `order2`.`price`, `order2`.`rest`, `order2`.`allowCancel`, `order2`.`deadline`, `order2`.`txnUuid`, `order2`.`state`, `order2`.`autoPay`, `order2`.`mustPay`, `order2`.`extractableAfter`, `order2`.`availableAfter`, `order2`.`autoRefundAt`, `order2`.`title`, `order2`.`desc`, `order2`.`callback`, `order2`.`image`, `order2`.`type`, `order2`.`groupBy`, `order2`.`closedBy`, `order2`.`payerId`, `order2`.`receiverId`, `order2`.`_createAt_`, `order2`.`_updateAt_`, `order2`.`_deleteAt_`, `order2`.`id`, `user`.`mobile` as 'payer.mobile', `user`.`password` as 'payer.password', `user`.`tradePassword` as 'payer.tradePassword', `user`.`state` as 'payer.state', `user`.`disableReason` as 'payer.disableReason', `user`.`dangerousCoefficient` as 'payer.dangerousCoefficient', `user`.`nickname` as 'payer.nickname', `user`.`gender` as 'payer.gender', `user`.`birth` as 'payer.birth', `user`.`head` as 'payer.head', `user`.`name` as 'payer.name', `user`.`txnUuid` as 'payer.txnUuid', `user`.`idStatus` as 'payer.idStatus', `user`.`idCard` as 'payer.idCard', `user`.`idImages` as 'payer.idImages', `user`.`isVirtual` as 'payer.isVirtual', `user`.`agentId` as 'payer.agentId', `user`.`_createAt_` as 'payer._createAt_', `user`.`_updateAt_` as 'payer._updateAt_', `user`.`_deleteAt_` as 'payer._deleteAt_', `user`.`id` as 'payer.id', `user_1`.`mobile` as 'receiver.mobile', `user_1`.`password` as 'receiver.password', `user_1`.`tradePassword` as 'receiver.tradePassword', `user_1`.`state` as 'receiver.state', `user_1`.`disableReason` as 'receiver.disableReason', `user_1`.`dangerousCoefficient` as 'receiver.dangerousCoefficient', `user_1`.`nickname` as 'receiver.nickname', `user_1`.`gender` as 'receiver.gender', `user_1`.`birth` as 'receiver.birth', `user_1`.`head` as 'receiver.head', `user_1`.`name` as 'receiver.name', `user_1`.`txnUuid` as 'receiver.txnUuid', `user_1`.`idStatus` as 'receiver.idStatus', `user_1`.`idCard` as 'receiver.idCard', `user_1`.`idImages` as 'receiver.idImages', `user_1`.`isVirtual` as 'receiver.isVirtual', `user_1`.`agentId` as 'receiver.agentId', `user_1`.`_createAt_` as 'receiver._createAt_', `user_1`.`_updateAt_` as 'receiver._updateAt_', `user_1`.`_deleteAt_` as 'receiver._deleteAt_', `user_1`.`id` as 'receiver.id' from `order2` left join `user` `user` on `order2`.`payerId` = `user`.`id` left join `user` `user_1` on `order2`.`receiverId` = `user_1`.`id` where ((`order2`.`_deleteAt_` is null) AND (`order2`.`id`=2)) AND (`user`.`_deleteAt_` is null) AND (`user_1`.`_deleteAt_` is null) limit 0, 1
2017-01-17T05:24:14.007668Z 569 Query update `order2` set `state`=999,`_updateAt_`=1484630654007 where `id`=2
2017-01-17T05:24:14.007668Z 569 Query insert into `orderAction`(`orderId`,`prevState`,`nextState`,`operatorId`,`_createAt_`,`_updateAt_`) values(2,10,999,2,1484630654007,1484630654007)
2017-01-17T05:24:14.008668Z 569 Query select `orderPay`.`id`, `order2`.`state` as 'order.state', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`payId`=3) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:14.009668Z 569 Query update `pay` set `state`=999,`_updateAt_`=1484630654009 where `id`=3
2017-01-17T05:24:14.010668Z 569 Query select `orderPay`.`id`, `order2`.`price` as 'order.price', `order2`.`state` as 'order.state', `order2`.`callback` as 'order.callback', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`payId`=3) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:14.010668Z 569 Query select `orderRefund`.`id`, `refund`.`state` as 'refund.state', `refund`.`price` as 'refund.price', `refund`.`id` as 'refund.id' from `orderRefund` left join `refund` `refund` on `orderRefund`.`refundId` = `refund`.`id` where ((`orderRefund`.`orderId`=3) AND (`orderRefund`.`_deleteAt_` is null)) AND (`refund`.`_deleteAt_` is null) limit 0, 128
2017-01-17T05:24:14.011669Z 569 Query select `orderPay`.`orderId`, `order2`.`price` as 'order.price', `order2`.`id` as 'order.id' from `orderPay` left join `order2` `order2` on `orderPay`.`orderId` = `order2`.`id` where ((`orderPay`.`orderId` not in (3)) AND (`orderPay`.`payId` is not null) AND (`orderPay`.`_deleteAt_` is null)) AND (`order2`.`_deleteAt_` is null) and exists (select `orderPay_1`.`id` from `orderPay` `orderPay_1` where ((`orderPay_1`.`orderId` in (3)) AND (`orderPay_1`.`payId` = `orderPay`.payId) AND (`orderPay_1`.`_deleteAt_` is null))) limit 0, 128
2017-01-17T05:24:14.011669Z 569 Query select `pay`.`state` as 'pay.state', `pay`.`price` as 'pay.price', `pay`.`id` as 'pay.id' from `orderPay` left join `pay` `pay` on `orderPay`.`payId` = `pay`.`id` where ((`orderPay`.`orderId`=3) AND (`orderPay`.`_deleteAt_` is null)) AND ((`pay`.`state` in (3,10,999,101,99,201)) AND (`pay`.`_deleteAt_` is null)) limit 0, 1000
2017-01-17T05:24:14.012669Z 569 Query select `order2`.`price`, `order2`.`rest`, `order2`.`allowCancel`, `order2`.`deadline`, `order2`.`txnUuid`, `order2`.`state`, `order2`.`autoPay`, `order2`.`mustPay`, `order2`.`extractableAfter`, `order2`.`availableAfter`, `order2`.`autoRefundAt`, `order2`.`title`, `order2`.`desc`, `order2`.`callback`, `order2`.`image`, `order2`.`type`, `order2`.`groupBy`, `order2`.`closedBy`, `order2`.`payerId`, `order2`.`receiverId`, `order2`.`_createAt_`, `order2`.`_updateAt_`, `order2`.`_deleteAt_`, `order2`.`id`, `user`.`mobile` as 'payer.mobile', `user`.`password` as 'payer.password', `user`.`tradePassword` as 'payer.tradePassword', `user`.`state` as 'payer.state', `user`.`disableReason` as 'payer.disableReason', `user`.`dangerousCoefficient` as 'payer.dangerousCoefficient', `user`.`nickname` as 'payer.nickname', `user`.`gender` as 'payer.gender', `user`.`birth` as 'payer.birth', `user`.`head` as 'payer.head', `user`.`name` as 'payer.name', `user`.`txnUuid` as 'payer.txnUuid', `user`.`idStatus` as 'payer.idStatus', `user`.`idCard` as 'payer.idCard', `user`.`idImages` as 'payer.idImages', `user`.`isVirtual` as 'payer.isVirtual', `user`.`agentId` as 'payer.agentId', `user`.`_createAt_` as 'payer._createAt_', `user`.`_updateAt_` as 'payer._updateAt_', `user`.`_deleteAt_` as 'payer._deleteAt_', `user`.`id` as 'payer.id', `user_1`.`mobile` as 'receiver.mobile', `user_1`.`password` as 'receiver.password', `user_1`.`tradePassword` as 'receiver.tradePassword', `user_1`.`state` as 'receiver.state', `user_1`.`disableReason` as 'receiver.disableReason', `user_1`.`dangerousCoefficient` as 'receiver.dangerousCoefficient', `user_1`.`nickname` as 'receiver.nickname', `user_1`.`gender` as 'receiver.gender', `user_1`.`birth` as 'receiver.birth', `user_1`.`head` as 'receiver.head', `user_1`.`name` as 'receiver.name', `user_1`.`txnUuid` as 'receiver.txnUuid', `user_1`.`idStatus` as 'receiver.idStatus', `user_1`.`idCard` as 'receiver.idCard', `user_1`.`idImages` as 'receiver.idImages', `user_1`.`isVirtual` as 'receiver.isVirtual', `user_1`.`agentId` as 'receiver.agentId', `user_1`.`_createAt_` as 'receiver._createAt_', `user_1`.`_updateAt_` as 'receiver._updateAt_', `user_1`.`_deleteAt_` as 'receiver._deleteAt_', `user_1`.`id` as 'receiver.id' from `order2` left join `user` `user` on `order2`.`payerId` = `user`.`id` left join `user` `user_1` on `order2`.`receiverId` = `user_1`.`id` where ((`order2`.`_deleteAt_` is null) AND (`order2`.`id`=3)) AND (`user`.`_deleteAt_` is null) AND (`user_1`.`_deleteAt_` is null) limit 0, 1
2017-01-17T05:24:14.013669Z 569 Query update `order2` set `state`=999,`_updateAt_`=1484630654013 where `id`=3
2017-01-17T05:24:14.013669Z 569 Query insert into `orderAction`(`orderId`,`prevState`,`nextState`,`operatorId`,`_createAt_`,`_updateAt_`) values(3,10,999,2,1484630654013,1484630654013)
2017-01-17T05:24:14.014669Z 569 Query COMMIT
2017-01-17T05:24:14.042670Z 578 Query insert into `orderRefund`(`orderId`,`refundId`,`_createAt_`,`_updateAt_`) values(4,4,1484630653995,1484630653995)
2017-01-17T05:24:14.078672Z 569 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-01-17T05:24:14.107674Z 578 Query ROLLBACK

Session 578 is the wrong one. The row inserted in table `orderRefund` with refundId = 4 is still alive. In fact, the refund with id = 4 is also inserted by it and is rollbacked successfully.
So this refundId points to a NULL refund...

mysql> select * from refund;
+-------+---------+---------+-------+---------------+---------------+-----------
-+----+
| state | price | txnUuid | payId | _createAt_ | _updateAt_ | _deleteAt_
| id |
+-------+---------+---------+-------+---------------+---------------+-----------
-+----+
| 201 | 3000.00 | NULL | 6 | 1484630651492 | 1484630651625 | NULL
| 1 |
| 201 | 1000.00 | NULL | 4 | 1484630651493 | 1484630651625 | NULL
| 2 |
+-------+---------+---------+-------+---------------+---------------+-----------
-+----+
2 rows in set (0.00 sec)

mysql> select * from orderRefund
-> ;
+---------+----------+---------------+---------------+------------+----+
| orderId | refundId | _createAt_ | _updateAt_ | _deleteAt_ | id |
+---------+----------+---------------+---------------+------------+----+
| 5 | 1 | 1484630651496 | 1484630651496 | NULL | 1 |
| 5 | 2 | 1484630651497 | 1484630651497 | NULL | 2 |
| 4 | 4 | 1484630653995 | 1484630653995 | NULL | 4 |
+---------+----------+---------------+---------------+------------+----+
3 rows in set (0.00 sec)

Does anyone knows what's the problem behinds? Thanks.

Options: ReplyQuote


Subject Views Written By Posted
Innodb has Transaction LEAK? 232 Chang Xu 01/16/2017 11:55PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.