Query deadlocks

Query deadlock detection parameters

In OceanBase Database, the parameter related to deadlock detection is _lcl_op_interval, which is a hidden parameter at the cluster level. This parameter controls the interval at which nodes push messages in the Lock Chain Length (LCL) deadlock detection algorithm.

In OceanBase Database V4.x, the default value of the hidden parameter _lcl_op_interval is 30 ms, which means that deadlock detection is enabled by default. A smaller value of this parameter results in lower deadlock detection latency but higher resource consumption, while a larger value results in higher latency but lower resource usage.

When _lcl_op_interval is set to 0 ms, the LCL deadlock detection feature is disabled. Generally, it is recommended to disable this feature. When disabled, deadlocks are handled through the transaction timeout rollback mechanism (the default transaction timeout is controlled by the ob_trx_timeout variable).

To check if the deadlock detection feature is enabled, you can use the following methods:

Use views

The GV$OB_PARAMETERS view records all parameters (including hidden parameters in the _xx_xx format) and their attributes. You can query this view to check the value of the hidden parameter _lcl_op_interval.

• Sys tenant

  In the sys tenant, you can use the following statement to query:

  obclient> SELECT * FROM oceanbase.GV$OB_PARAMETERS WHERE NAME='_lcl_op_interval';
  

• User tenant

  In a MySQL-compatible user tenant, you can use the following statement to query:

  obclient> SELECT * FROM oceanbase.GV$OB_PARAMETERS WHERE NAME='_lcl_op_interval';
  

  In an Oracle-compatible user tenant, you can use the following statement to query:

  obclient> SELECT * FROM SYS.GV$OB_PARAMETERS WHERE NAME='_lcl_op_interval';
  

Use the SHOW statement

You can use the following statement to view the value of the hidden parameter _lcl_op_interval only after it has been modified from its default value:

obclient> SHOW PARAMETERS LIKE '%_lcl_op_interval%';

Query deadlock events

The CDB_OB_DEADLOCK_EVENT_HISTORY view (system tenant) or DBA_OB_DEADLOCK_EVENT_HISTORY view (user tenant) records all deadlock events that have occurred, the transactions involved in these events, and which transaction was ultimately killed in each deadlock event.

• Sys tenant

  In the sys tenant, you can use the following statement to query:

  obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_DEADLOCK_EVENT_HISTORY WHERE TENANT_ID=tenant_id;
  

• User tenant

  In a MySQL-compatible user tenant, you can use the following statement to query:

  obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_DEADLOCK_EVENT_HISTORY;
  

  In an Oracle-compatible user tenant, you can use the following statement to query:

  obclient [SYS]> SELECT * FROM SYS.DBA_OB_DEADLOCK_EVENT_HISTORY;