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, 344🔥, 0💬
Popular Posts:
How To Insert New Line Characters into Strings in SQL Server Transact-SQL? If you want to break a st...
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...
What are DDL (Data Definition Language) statements for tables in SQL Server? DDL (Data Definition La...
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...