IS_USED_LOCK
Description
This function checks whether a lock named str is being used by a session (i.e., whether the lock has been acquired).
Syntax
IS_USED_LOCK('str')
Description
Parameters
str: the name of the lock to be checked. The value of this parameter is a string.
Return value
-
If a session ID is returned, it indicates that the lock is being used. This session ID represents the client session that acquired the lock.
-
If
NULLis returned, it 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 who uses the lock.
SELECT IS_USED_LOCK('my_lock');
The return result is as follows:
+-------------------------+
| IS_USED_LOCK('my_lock') |
+-------------------------+
| 3221487701 |
+-------------------------+
1 row in set (0.001 sec)
The return value is 3221487701, indicating that the lock my_lock is being used by the session with session ID 3221487701. You can query the information about the session that occupies the lock from the oceanbase.GV$OB_PROCESSLIST view. Here is an example:
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 (0.001 sec)