Deadlocks detected? What to do next?

Body

Deadlock refers to a specific condition in a database, when two processes are waiting for the other process to release a resource. For example, when one client application holds a lock on a table and attempts to obtain the lock on a second table that is held by another client application, this may lead to a deadlock if the other application attempts to obtain the lock that is held by the first application.

To circumvent the deadlock problem, it is recommended that you need to sort the information to be accessed in a certain order before grabbing locks.

When the DB encounters a deadlock, it automatically resolves it by rolling back one of the statements involved in the deadlock by releasing one set of data. The transaction that is rolled back by the DB will throw the error. The  other transaction that goes on to complete successfully.

To understand the root cause of the deadlock, it is important  to identify the transaction that successfully completed as well. The application provides an OOB method to gather the deadlock trace for Oracle which showcase details of various transactions involved in the deadlock.

To capture the Deadlock trace from the Oracle database -

• Set yfs.yfs.app.identifyconnection=Y in customer_overrides.properties file
• Add JVM parameter -DDLOCK_LOG_DIR to All IBM Sterling JVMs. Eg: -DDLOCK_LOG_DIR=/directory/where/deadlock/logs/should/go/.
• If schema owner and DB login user are different ,add -DOWNER=<Schema Owner>

This will generate the deadlock trace in the files specified that will provide information about the transactions involved in the deadlock. In addition to the deadlock trace, the following will  provide an overall picture of the situation-

• SQLDEBUG logs from the duration that the deadlock was thrown
• Deadlock logs from the DB
• AWR reports