History of deadlocks in Oracle?

Question

Does oracle keeps any history about deadlocks?

e.g. Can i know which sqls were executed when the deadlock was detected, and oracle exception ORA-00060 (deadlock detected while waiting for resource) is thrown?

Thanks in advance.

Answer 1

There's no history built-in to the database, however, when a deadlock occurs, a couple of things happen. First, it gets logged to the alert.log. Second, a trace file is written. So, there's some history recorded there.

The tracefile will contain many useful bits of information, such as: - deadlock graph - SQL that encountered the deadlock - other statements involved in the deadlock

These pieces of information should help you track down the source of the deadlock.

If you have any specific cases or examples, identify the tracefile, and post the pieces I mentioned above, and I'm sure someone will be able to help you understand what's going on.

Hope that helps.