Collections:
GET_LOCK() - Requesting User Defined Lock
How to request a user defined lock using the GET_LOCK() function?
✍: FYIcenter.com
GET_LOCK(lock, timeout) is a MySQL built-in function that
tries to obtain a user defined lock within a timeout period.
It returns 1 if successful, 0 if failed.
For example:
SELECT GET_LOCK('MyLock', 60), IS_FREE_LOCK('MyLock'), IS_USED_LOCK('MyLock'); -- +------------------------+------------------------+------------------------+ -- | GET_LOCK('MyLock', 60) | IS_FREE_LOCK('MyLock') | IS_USED_LOCK('MyLock') | -- +------------------------+------------------------+------------------------+ -- | 1 | 0 | 307 | -- +------------------------+------------------------+------------------------+
GET_LOCK(lock, timeout) function behavior can be described as:
The following examples shows you the multiple-instance behavior.
SELECT GET_LOCK('Cloned', 5), IS_USED_LOCK('Cloned'); -- +-----------------------+------------------------+ -- | GET_LOCK('Cloned', 5) | IS_USED_LOCK('Cloned') | -- +-----------------------+------------------------+ -- | 1 | 307 | -- +-----------------------+------------------------+ SELECT GET_LOCK('Cloned', 5), IS_USED_LOCK('Cloned'); -- +-----------------------+------------------------+ -- | GET_LOCK('Cloned', 5) | IS_USED_LOCK('Cloned') | -- +-----------------------+------------------------+ -- | 1 | 307 | -- +-----------------------+------------------------+ SELECT RELEASE_LOCK('Cloned'); -- +------------------------+ -- | RELEASE_LOCK('Cloned') | -- +------------------------+ -- | 1 | -- +------------------------+ SELECT RELEASE_LOCK('Cloned'); -- +------------------------+ -- | RELEASE_LOCK('Cloned') | -- +------------------------+ -- | 1 | -- +------------------------+ SELECT RELEASE_LOCK('Cloned'); -- +------------------------+ -- | RELEASE_LOCK('Cloned') | -- +------------------------+ -- | NULL | -- +------------------------+
The following examples shows you the waiting behavior.
-- in session 1: SELECT CONNECTION_ID(); -- +-----------------+ -- | CONNECTION_ID() | -- +-----------------+ -- | 307 | -- +-----------------+ SELECT GET_LOCK('Shared', 5), IS_USED_LOCK('Shared'); -- +-----------------------+------------------------+ -- | GET_LOCK('Shared', 5) | IS_USED_LOCK('Shared') | -- +-----------------------+------------------------+ -- | 1 | 307 | -- +-----------------------+------------------------+ -- in session 2: SELECT CONNECTION_ID(); -- +-----------------+ -- | CONNECTION_ID() | -- +-----------------+ -- | 340 | -- +-----------------+ SELECT IS_USED_LOCK('Shared'); -- +------------------------+ -- | IS_USED_LOCK('Shared') | -- +------------------------+ -- | 307 | -- +------------------------+ SELECT SYSDATE(), GET_LOCK('Shared', 5), SYSDATE(); -- +---------------------+-----------------------+---------------------+ -- | SYSDATE() | GET_LOCK('Shared', 5) | SYSDATE() | -- +---------------------+-----------------------+---------------------+ -- | 2023-12-19 18:28:04 | 0 | 2023-12-19 18:28:09 | -- +---------------------+-----------------------+---------------------+
Reference information of the GET_LOCK() function:
GET_LOCK(lock, timeout): boolean Tries to obtain a user defined lock within a timeout period. Arguments, return value and availability: lock: Required. The lock name to be requested. timeout: Required. The timeout period to wait for the lock to be released. boolean: Return value. 1 if request is successful, 0 otherwise. Available since MySQL 4.
Related MySQL functions:
⇒ GREATEST() - Finding the Greatest/Maximum Value
⇐ EXTRACTVALUE() - Extracting Text Content from XML
2023-12-20, 655🔥, 0💬
Popular Posts:
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
How To Download Oracle Database 10g XE in Oracle? If you want to download a copy of Oracle Database ...
How To Verify a User name with SQLCMD Tool in SQL Server? The quickest way to verify a user name in ...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...
How To Convert Numeric Expression Data Types using the CONVERT() Function in SQL Server Transact-SQL...