Purpose
IS_USED_LOCK() checks whether the lock with the name given by str is in use, that is, has been obtained by a session.
Syntax
IS_USED_LOCK('str')
Description
Parameters
str: the name of the lock to be checked. The value is a string.
Return value
A session ID: indicates that the lock is in use. The returned value represents the session that holds the lock.
NULL: indicates that the lock is not in use.
Examples
Check whether the lock named my_lock is in use. If so, the session ID of the session that uses the lock will be returned.
obclient [(none)]> SELECT IS_USED_LOCK('my_lock');
The return result is as follows:
+-------------------------+
| IS_USED_LOCK('my_lock') |
+-------------------------+
| 3221487701 |
+-------------------------+
1 row in set
3221487701 is returned, indicating that the lock my_lock is held by session 3221487701. You can query the session information in the oceanbase.GV$OB_PROCESSLIST view. 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