Purpose
This function is used to check whether a lock named by the string str is being held by any session.
Syntax
IS_USED_LOCK('str')
Description
Parameters
str: The name of the lock to be checked, in string format.
Return value
Return session ID: Indicates that the lock is in use. This number represents the client session that acquired the lock.
Return
NULL: The lock is not being used by any session.
Examples
Check whether a lock named my_lock is being used by any session, and return the session ID of the user.
obclient [(none)]> SELECT IS_USED_LOCK('my_lock');
The return result is as follows:
+-------------------------+
| IS_USED_LOCK('my_lock') |
+-------------------------+
| 3221487701 |
+-------------------------+
1 row in set
The returned result 3221487701 indicates that the lock my_lock is being used by the session with ID 3221487701. You can query the oceanbase.GV$OB_PROCESSLIST view for information about the session holding this lock. Example:
obclient [(none)]> SELECT * FROM oceanbase.GV$OB_PROCESSLIST WHERE ID = 3221487701\G
The return result is as follows:
*************************** 1. row ***************************
SVR_IP: xxx.xxx.xxx.xx1
SVR_PORT: 2882
SQL_PORT: 2881
ID: 3221487701
USER: root
HOST: xxx.xxx.xxx.xx2:61470
DB: NULL
TENANT: mysql001
COMMAND: Sleep
TIME: 1014.214143
TOTAL_TIME: 1014.214143
STATE: SLEEP
INFO: NULL
PROXY_SESSID: NULL
MASTER_SESSID: NULL
USER_CLIENT_IP: xxx.xxx.xxx.xx2
USER_HOST: %
RETRY_CNT: 0
RETRY_INFO: 0
SQL_ID:
TRANS_ID: 0
THREAD_ID: 0
SSL_CIPHER: NULL
TRACE_ID: NULL
TRANS_STATE:
ACTION:
MODULE:
CLIENT_INFO:
LEVEL: 1
SAMPLE_PERCENTAGE: 10
RECORD_POLICY: SAMPLE_AND_SLOW_QUERY
LB_VID: NULL
LB_VIP: NULL
LB_VPORT: NULL
IN_BYTES: 448
OUT_BYTES: 0
USER_CLIENT_PORT: 61470
PROXY_USER:
SERVICE_NAME: NULL
TOTAL_CPU_TIME: 0
TOP_INFO: NULL
MEMORY_USAGE: NULL
1 row in set