Purpose
This function is used to check whether a lock named str is being used by a session (i.e., whether the lock has been acquired).
Syntax
IS_USED_LOCK('str')
Purpose
Parameters
str: The name of the lock to check, specified as a string.
Return value
Returns the session ID: Indicates that the lock is being used. This number represents the client session that acquired the lock.
Returns
NULL: Indicates that the lock is not being used by any session.
Examples
Check whether a lock named my_lock is being used by a 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 return result is 3221487701, indicating that the lock my_lock is being used by the session with ID 3221487701. You can query the oceanbase.GV$OB_PROCESSLIST view to obtain information about the session that is using the lock. Here is an 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
TOTAL_CPU_TIME: 0
1 row in set