Query row locks

During business operations, users often encounter lock conflicts. For row locks, two key objects are involved: the holder of the row lock and the waiter for the row lock. These two objects play a crucial role in troubleshooting row locks. Essentially, row lock conflicts are caused by transactions, and both the holder and waiter are part of transactions. Therefore, when addressing row lock issues, it is also necessary to monitor active transactions.

Query information about transactions that hold or request locks

The GV$OB_LOCKS view records information about locks held or requested by transactions in the current user. This view primarily displays the following three types of locks:

• Table locks (TM): Locks on tables or partitions (Tablets). DML operations such as insert, update, and delete typically generate partition-level table locks, while DDL operations generate table-level table locks.
• Row locks (TR): Locks on rows during write operations.
• Transaction locks (TX): The most common type of lock. These are typically generated when a write operation is performed within a transaction.

In OceanBase Database, if a transaction holds a row lock, it must also hold its own transaction lock.

You can query information about transactions that hold or request locks using the following methods.

• Sys tenant

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

  obclient> SELECT t.TENANT_ID, t.TRANS_ID, t.TYPE, t.ID1, t.ID2, t.LMODE, t.REQUEST, ROUND(t.CTIME / 1000000) CTIME_s, t.BLOCK
    FROM oceanbase.gv$ob_locks t
    WHERE t.BLOCK = 1 ORDER BY t.TRANS_ID;
  

• User tenant

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

  obclient> SELECT t.TRANS_ID, t.TYPE, t.ID1, t.ID2, t.LMODE, t.REQUEST, ROUND(t.CTIME / 1000000) CTIME_s, t.BLOCK
    FROM oceanbase.gv$ob_locks t
    WHERE t.BLOCK = 1 ORDER BY t.TRANS_ID;
  

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

  obclient> SELECT t.TRANS_ID, t.TYPE, t.ID1, t.ID2, t.LMODE, t.REQUEST, ROUND(t.CTIME / 1000000) CTIME_s, t.BLOCK
    FROM SYS.gv$ob_locks t
    WHERE t.BLOCK = 1 ORDER BY t.TRANS_ID;
  

For example, the query result in the system tenant is as follows:

+-----------+----------+------+----------+--------------------------------+-------+---------+---------+-------+
| TENANT_ID | TRANS_ID | TYPE | ID1      | ID2                            | LMODE | REQUEST | CTIME_s | BLOCK |
+-----------+----------+------+----------+--------------------------------+-------+---------+---------+-------+
|      1002 | 23723243 | TR   |   200051 | 23723237-{"BIGINT UNSIGNED":1} | NONE  | X       |      20 |     1 |
|      1002 | 23723243 | TX   | 23723237 | NULL                           | NONE  | X       |      20 |     1 |
+-----------+----------+------+----------+--------------------------------+-------+---------+---------+-------+
2 rows in set

From the first row of the result, we can see that there is a row lock (TR) information. The transaction ID (field TRANS_ID) of the transaction requesting the lock is 23723243. The tablet_id (field ID1) of the locked object is 200051. The row information (field ID2) being locked is 23723237-{"BIGINT UNSIGNED":1}. The lock has been waiting for 20 seconds.

The second row of the result shows that there is a transaction lock (TX) currently. The transaction ID (TRANS_ID) of the requesting transaction is 23723243, and the transaction ID (ID1) of the transaction that holds the lock is 23723237.

Query the session ID corresponding to the transaction based on the transaction ID

The GV$OB_TRANSACTION_PARTICIPANTS view records the participant information of active transactions on all OBServer nodes.

After you obtain the trans_id of a transaction that holds a lock or requests a lock, you can query the GV$OB_TRANSACTION_PARTICIPANTS view to obtain the session_id of the transaction.

• Sys tenant

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

  obclient> SELECT ROUND(now() - t.CTX_CREATE_TIME) RUNNING_TIME_s, t.TENANT_ID, t.SVR_IP, t.SVR_PORT, t.SESSION_ID, t.TX_TYPE, t.TX_ID, t.STATE, t.ACTION, t.CTX_CREATE_TIME, t.LAST_REQUEST_TIME
    FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS t
    WHERE t.TX_ID IN (holdlock_trans_id, requestlock_trans_id);
  

• User tenant

  In a MySQL-compatible user tenant, you can execute the following statement:

  obclient>SELECT ROUND(now() - t.CTX_CREATE_TIME) RUNNING_TIME_s, t.TENANT_ID, t.SVR_IP, t.SVR_PORT, t.SESSION_ID, t.TX_TYPE, t.TX_ID, t.STATE, t.ACTION, t.CTX_CREATE_TIME, t.LAST_REQUEST_TIME
    FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS t
    WHERE t.TX_ID IN (holdlock_trans_id, requestlock_trans_id);
  

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

  obclient>SELECT ROUND(now() - t.CTX_CREATE_TIME) RUNNING_TIME_s, t.TENANT_ID, t.SVR_IP, t.SVR_PORT, t.SESSION_ID, t.TX_TYPE, t.TX_ID, t.STATE, t.ACTION, t.CTX_CREATE_TIME, t.LAST_REQUEST_TIME
    FROM SYS.GV$OB_TRANSACTION_PARTICIPANTS t
    WHERE t.TX_ID IN (holdlock_trans_id, requestlock_trans_id);
  

In the statements:

• running_time_s: the total execution time of the transaction.
• holdlock_trans_id: the ID of the transaction that holds a lock.
• requestlock_trans_id: the ID of the transaction that requests a lock.

After you obtain the session_id of the transaction that holds a lock, you can use kill session_id to roll back the corresponding transaction.