GET_LOCK

Description

This function is used to attempt to acquire a lock with a specific name (str). Once a session successfully acquires a lock with a specific name, any other session attempting to acquire the same lock will be blocked (i.e., forced to wait) until the original session that held the lock releases it.

Locks acquired using the GET_LOCK() function can be explicitly released by executing RELEASE_LOCK(). Additionally, when a session terminates, whether normally or abnormally, any locks held by that session are implicitly released.

tip

Locks obtained using GET_LOCK() are not released upon transaction commit or rollback.

Syntax

GET_LOCK('str', timeout)

Description

Parameters

• str: The name of the lock, which is a string.

• timeout: The maximum time to wait for the lock to be released, in seconds. Valid values are as follows:

  • It can be 0 or a positive number.
  • It can also be a negative number, indicating an infinite wait, i.e., the session will wait indefinitely until the lock is acquired.

Return Value

• 1: Indicates that the lock was successfully acquired. At this point, the session that called GET_LOCK holds the lock, and any other attempts to acquire the same lock will fail unless the lock is released.
• 0: Indicates that the attempt to acquire the lock failed due to a timeout. This typically means that the lock was acquired by another session and was not released within the timeout period.
• NULL: Indicates an error occurred while attempting to acquire the lock, which could be due to insufficient memory or the thread attempting to acquire the lock being forcibly terminated.

Examples

1. In session 1, execute the following command to attempt to acquire a lock named my_lock, waiting up to 10 seconds.

   SELECT GET_LOCK('my_lock', 10);

   The result is as follows:

   +-------------------------+
   | GET_LOCK('my_lock', 10) |
   +-------------------------+
   |                       1 |
   +-------------------------+
   1 row in set (0.001 sec)

   The result is 1, indicating that the lock my_lock was successfully acquired.

2. In session 2, execute the following command to attempt to acquire a lock named my_lock, waiting up to 10 seconds.

   SELECT GET_LOCK('my_lock', 10);

   The result is as follows:

   +-------------------------+
   | GET_LOCK('my_lock', 10) |
   +-------------------------+
   |                       0 |
   +-------------------------+
   1 row in set (0.001 sec)

   The result is 0, indicating that the attempt to acquire the lock my_lock failed due to a timeout.

References

RELEASE_LOCK