0

I could see long running transactions in my Innodb Status, I'm using RDS Aurora Mysql 5.6 Version.

---TRANSACTION 977075315, ACTIVE 70113 sec
2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1630
MySQL thread id 72467, OS thread handle 0x2ba042a41700, query id 8257176 10.105.86.37 administrator cleaned up
Trx read view will not see trx with id >= 977075316, sees < 977054983
---TRANSACTION 977073376, ACTIVE 70210 sec
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1724
MySQL thread id 72463, OS thread handle 0x2ba041e8a700, query id 8263196 10.105.86.37 administrator delayed send ok done
Trx read view will not see trx with id >= 977073377, sees < 977054983
---TRANSACTION 977064809, ACTIVE 70654 sec
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1360
MySQL thread id 72464, OS thread handle 0x2ba042a41700, query id 8263033 10.105.86.37 administrator delayed send ok done
Trx read view will not see trx with id >= 977064810, sees < 977054983
---TRANSACTION 977061227, ACTIVE 70848 sec
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1705
MySQL thread id 72695, OS thread handle 0x2ba041e49700, query id 8262830 10.105.86.103 administrator cleaned up
Trx read view will not see trx with id >= 977061228, sees < 977054983
---TRANSACTION 977054983, ACTIVE 71140 sec
4 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1599
MySQL thread id 72693, OS thread handle 0x2ba03f7c1700, query id 8262150 10.105.86.103 administrator cleaned up
Trx read view will not see trx with id >= 977054984, sees < 977054977

I'm not using any Explicit transactions.(like begin,commit..)

And I could see from performance_schema.events_statements_history_long that currently executing queries are using the same transactions that are active for long time.

Why is Mysql not closing these transactions and using same transactions for Other Queries ??

Here one of the above transaction even has some row locks, which leads to lock wait time out exception.

I don't want to kill those threads, I want to fix the rootcause of this problem

Kindly help me out with this Issue.

  • Are you using autocommit=0 and forgetting to issue COMMIT? – Rick James Nov 12 at 19:15
  • No Autocommit is enabled and we didn't make it 0 – user3865748 Nov 13 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.