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, 330🔥, 0💬
Popular Posts:
How To Break Query Output into Pages in MySQL? If you have a query that returns hundreds of rows, an...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...