0

I want to know if a certain transaction got rolled back. If we analyze

SHOW ENGINE INNODB STATUS\G;

we get the list rolled back transactions with transactionid , but how to get the actual query from this

eg I have

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 89504501
Purge done for trx's n:o < 88503595 undo n:o < 0 state: running but idle
History list length 2341
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 531230733218221, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 531230733221873, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 531230733213665, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

I get some transcation id = 531230733218221, but what is the INSERT/UPDATE query corresponding to this. Here is my mysql config:

Version: 5.7
Binary Log: ON
Slow Query Log: ON 
General Query Log: OFF
Performance Schema: ON
Profiling: OFF
Transaction Isolation Level: REPEATABLE-READ

Is there any table in performance_schema that we can explore. Since MySQL do not have any mechanism to store the last updated timestamp at row/column level, though it exists at the table level.

1
  • Since you have Performance Schema ON, the deadlock details could be recorded. I will look around. Mar 22 '19 at 12:45
-1

Look in the SHOW ENGINE INNODB STATUS output for

LATEST DETECTED DEADLOCK

and between this line and the

WE ROLL BACK TRANSACTION

you will find QUERY content that was involved for each contributor to the deadlock (there may be thousands of lines of detailed info).

1
  • Does anyone have a clue why this Answer was down voted? Mar 27 '19 at 2:47

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.