InnoDB and some 3rd parties Storage Engines support transactions. But there are many places where concurrency can cause conflicts:
MEMORY,AriaandMyISAMonly support table-level locks, which prevent all write statements (and maybe even reads) on a whole table. When concurrency is too high, this is a problem.- You may want to lock a
VIEW, which is very different from locking a table: aVIEWcan be a subset of a table, or aJOINbetween table subsets. - DDL statements are not affected by locks. This is even true on tables which support transactions.
An alternative is using some SQL functions which acquire, check and release global “locks”. The reason why I quoted “locks” is that they don’t really lock anything. All sessions are expected to acquire and release locks with a proper name. So, applications and routines must be written by someone who knows the locking logic used for that database.
In other words: there is no way to really lock some rows, or a table, or a view using this set of functions; but if applications and Stored Routines acquire a logical lock having the same name before accessing the same table, no conflict is possible.
Just some notes, before discussing the set of functions that handle logical locks:
- Lock names are case-insensitive. So
'A'is equal to'a'. - Sometimes you don’t want to check the result of
GET_LOCK()orRELEASE_LOCK(). In that case, you can use them withDO(see examples below).
GET_LOCK(name, timeout)
Acquires a lock with the given name. If the lock is already taken by someone else, waits until the other session releases the lock. The wait will not be eternal: with the timeout parameter, you specify how many seconds you want to wait, as a maximum.
When the current connection is closed (because you close it, because it timeouts, or because the administrator KILLs it), the lock will be free.
GET_LOCK() should return 1. If it timeouts, returns 0. If another error occurs, returns NULL.
Before MariaDB 10.0, and with MySQL 5.6, if you have perviously acquired a lock with another name, it will be released. This means that before MariaDB 10.0, and with MySQL 5.6, you could not acquire more than one lock. With MariaDB 10.0 you can. But there are some rare cases where you may need to mofify existing applications.
RELEASE_LOCK(name)
Releases a lock with the given name. This function should be called when you don’t need a lock anymore but you are not about to close the connection.
Despite the fact that you can’t currently hold more than one lock, you must provide a lock name. If you want to acquire another lock, you don’t need to call RELEASE_LOCK().
RELEASE_LOCK() should return 1. If it returns 0, that lock has been acquired by someone else, and can’t be release by you. If the specified lock does not currently exist, returns NULL. In both cases, your code is likely to be buggy.
IS_FREE_LOCK(name)
Returns 1 is specified lock is free and can be acquired, 0 if it’s in use, NULL if an error occurs. Normally you don’t need to call this function because, as stated above, GET_LOCK() waits until specified lock is free.
IS_USED_LOCK(name)
This function is useful if you need to know which thread is holding a certain lock. It returns the connection id of that thread, or NULL if the specified lock is free.
If you need to know your connection id, in SQL you can use CONNECTION_ID(). In other languages, check your API documentation to find the API function which efficiently does this.
Example
Let’s try two example. The following examples are tested on MariaDB 5.5, but should work with the same versions of Percona Server and Oracle MySQL.
Ex. 1: Using GET_LOCK and RELEASE_LOCK
This example shows how you can simulate a lock on 1 row using its id. The following stored function copies a row from table a to table b, and then increments a value in the original row. But if we don’t handle concurrency, this could happen:
- thread 1 copies a row into b
- thread 2 copies the same row into b, generating a duplicate
- thread 1 increments the row in a
- thread 2 doesn’t copy the new value of that row, and increments it again.
To avoid this conflict, we’ll use GET_LOCK(). At the end of the Routine, we’ll use RELEASE_LOCK().
CREATE PROCEDURE `my_db`.`copy_row`(IN `row_id` SMALLINT UNSIGNED)
MODIFIES SQL DATA
BEGIN
-- name of the lock we'll acquire: it's a row-level lock
DECLARE `lock_name` TEXT DEFAULT CONCAT('row_id_', `row_id`);
-- try to acquire lock, or return an error
IF (GET_LOCK(`lock_name`, 10) 1) THEN
SIGNAL SQLSTATE VALUE '45000' SET
MESSAGE_TEXT = '[my_db.copy_row] Cant acquire lock';
END IF;
-- copy the row
INSERT INTO `b` (qty)
SELECT `qty`
FROM `a`
WHERE `id` = `row_id`;
-- increment
UPDATE `a`
SET `qty` = `qty` + 1
WHERE `id` = `row_id`;
-- free lock
DO RELEASE_LOCK(`lock_name`);
END;Some notes about what we do here.
First, we create a lock_name dynamically, because we want to simulate a row-level lock. If two threads try to copy the same row (same id), they will try to acquire the same lock, so one of them will be queued. But if the id’s are different, the name of the locks will be different, and the threads can run this procedure at the same time.
GET_LOCK() is invoked within an IF condition, because we want to check the result. If it’s not 1, the lock is not acquired, so we throw an error.
At the end, we call RELEASE_LOCK() to avoid blocking the row when it’s no more necessary. We do it with a DO statement, because we are not checking the result of RELEASE_LOCK().
Ex. 2: Using IS_USED_LOCK
In the following example, we want to do something. Well, we won’t do anything, in fact we’ll just sleep for 5 seconds… but hey, when one sleeps, someone must be awake, in case the enimy arrives!
CREATE PROCEDURE `my_db`.`do_something`()
MODIFIES SQL DATA
`do_some`:
BEGIN
-- result of IS_USED_LOCK()
DECLARE `locker` TINYINT UNSIGNED DEFAULT IS_USED_LOCK('my_lock');
IF `locker` IS NOT NULL THEN
SELECT CONCAT('Sorry, my_lock is used by thread ', `locker`);
LEAVE `do_some`;
END IF;
DO GET_LOCK('my_lock', 1);
SELECT 'I\'m doing something for 5 seconds...';
DO SLEEP(5);
DO RELEASE_LOCK('my_lock');
END;This time, we store the result of IS_USED_LOCK(), so we know the id of the thread which taken the lock. If it’s NOT NULL, we report it to the user and exit the Procedure. Otherwise, we go ahead and invoke GET_LOCK().
It’s easy, isn’t it?
FALSE IS NOT NULL 