How to find BOTH threads of a deadlock?

Question

We're having the classic spring/hibernate/mysql stack running in Tomcat 5.5. Once in a while we're getting a deadlock when the attempt times out to lock a table row. Some kind of deadlock exception is thrown.

The exception is clear and the stack trace indicate what went wrong. But it doesn't show the other thread which is holding the actual lock. Unless I know what that thread is doing it's all just a needle in a haystack.

QUESTION: Is there a way to find the other thread ?

Thanks !

Jan

Answer 1

Try using the following command in MySQL next time you see a deadlock. This should show you the last deadlock.

SHOW INNODB STATUS

Typically when you see a deadlock on your application server the logs show only the victim thread (the one which was rolled back). Since the other thread has completed no exception is thrown. You need to go back to your DB to recreate the transactions.

Once you have a capture from your DB for where the deadlock occured then you can investigate further.