Deadlock detection

OceanBase Database supports automatic distributed detection of deadlocks. OceanBase Database can automatically detect and solve deadlocks when the automatic distributed deadlock detection feature is enabled. You must determine whether to commit or roll back the transaction based on the SQL exception information.

Compared with the deadlock handling solution based on transaction timeout, automatic distributed deadlock detection has the following advantages:

OceanBase Database can detect and solve deadlocks promptly to reduce the impact of service deadlocks on system performance.

You can view the deadlocks that occurred in the last seven days and the relevant SQL statements executed to optimize the business logic and prevent deadlocks.

You can obtain deadlock information from OCP. For more information, see View deadlock analysis, Enable automatic detection of deadlocks, and Disable automatic detection of deadlocks.

You can also query the DBA_OB_DEADLOCK_EVENT_HISTORY or CDB_OB_DEADLOCK_EVENT_HISTORY view to obtain all deadlock events, transactions involved in the events, and the transaction that is killed in a deadlock event. Here is an example:

obclient> select * from oceanbase.DBA_OB_DEADLOCK_EVENT_HISTORY;

obclient> select * from oceanbase.CDB_OB_DEADLOCK_EVENT_HISTORY;