MariaDB/MySQL: GET_LOCK, RELEASE_LOCK, etc

InnoDB and some 3rd parties Storage Engines support transactions. But there are many places where concurrency can cause conflicts:

  • MEMORY, Aria and MyISAM only 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: a VIEW can be a subset of a table, or a JOIN between 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() or RELEASE_LOCK(). In that case, you can use them with DO (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?

Advertisements

STK/Unit 1.0 Release Candidate 3 released

2013-04-05

STK/Unit 1.0 Release Candidate 3 is out!

STK/Unit 1.0 Release Candidate 3 should be a stable version. STK/Unit main author uses the software since 2010, but heavily recoded when he decided to make it public. All old tests he wrote still work.

Since the first public release, rc1, we only received come private feedback. That has been valuable, because helped us to identify bugs and flaws. We really wish to receive some public feedback before declaring STK/Unit production-ready.

This RC was not planned. However, we were able to expand the list of supported MySQL and MariaDB versions with a very low delta from the previous release. This should help us to get some user feedbacks, and we believe that we are not introducing any bugs.

Now, supported platforms are:

  • MariaDB 10.0, 5.5
  • MariaDB 5.1, 5.2, 5.3 (with some documented issues: a few DDL-specific assert functions could generate obscure exceptions)
  • MySQL 5.6, 5.5
  • MySQL 5.1 (with same problems MariaDB 5.1 has)

Here is a list of changes in 1.0 Release Candidate 3:

  • Fix bug #1162515 (test_stk_unit aborts on MySQL 5.5)
  • Modifying SQL files for installation on Oracle MySQL is no longer necessary
  • Commented BTs in test_stk_unit_assertions contained errors
  • Minor code cleaning

Documentation and Downloads for STK/Unit and others STK tools to come, are avaible from here:
http://stk.wikidot.com/

The public Mailing List can be found here:
https://launchpad.net/~stk-discuss

The STK team encourage you to try STK/Unit in your databases, report any bugs you may find, ask for help in the list when needed, and let us know any comments. Your feedback is valuable for us!

The STK Team

Quoting MySQL & MariaDB identifiers

Versione italiana

MySQL/MariaDB identifiers are names for databases, tables, columns, etc. They can be quoted with `backticks` (AKA backquotes), and in that case they can contain characters which are normally illegal for identifiers (even spaces or the backtick itself), or they can be reserved words. Both quoting and not quoting cause some problems.

If you don’t quote names you will need to avoid illegal chars and reserved words – which is a good practice, anyway. But when you upgrade MariaDB, the new version could add some reserved words.

If you quote names, you should be sure to do it everywhere. It is a good practice, but if a developer doesn’t use backticks (or forgets to do it), he may see strange errors.

If you use keywords as identifiers and don’t quote them, you will probably receive an error 1064 (SQLSTATE 42000):

mysql> SELECT * FROM insert;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert' at line 1

The bad identifier is usually the first word in the reported string (see example above), but can also be the last word before the beginning of the reported code part:

mysql> SELECT high_priority FROM t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'FROM t' at line 1

In theory, a query involving unquoted identifiers which are reserved words, could even do something different from what you think and report no errors.

Another caveat: even when you quote all identifiers, avoid using the same name for a column and for a local variable in a Stored Routine. While MariaDB understands clauses like: WHERE `id` = `id`, this can cause a lot of confusion.

SQL tools

Here are 2 Stored Routines that can probably be useful. For both the routines, I’ll include a Base Test for STK/Unit. The Stored Routines will go into a DB called lib, so the BTs will go into a Test Case called test_lib.

The first Function quotes an identifier for inclusion in a SQL string (which can be PREPAREd and EXECUTEd). It’s similar to the QUOTE() built-in function, that works for strings.

CREATE FUNCTION `lib`.`quote_name`(`id` TEXT)
    RETURNS TEXT
    DETERMINISTIC
    NO SQL
    LANGUAGE SQL
    COMMENT 'Return a quoted identifier (if NULL, id is empty)'
BEGIN
    IF `id` IS NULL THEN
        RETURN '``';
    ELSE
        RETURN CONCAT('`', REPLACE(`id`, '`', '``'), '`');
    END IF;
END;
 
CREATE PROCEDURE `test_lib`.`test_quote_name`()
    LANGUAGE SQL
    COMMENT 'Test quote_name()'
BEGIN
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`('x'), '`x`', 'Incorrect quoting');
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`('x`y'), '`x``y`', 'Incorrect escape');
 
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`(''), '``', 'Empty name expected');
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`(NULL), '``', 'For NULL value, empty name should be returned');
END;

If you need to verify a single name on the fly, the easiest way is probably:
SELECT 0 AS [name];
The following Procedure, however, is meant to be used inside Stored Programs. It can be used to know if a string is a valid name. It executes the above query (as a Prepared Statement, because it must be composed dynamically) and checks wether an error is generated.

CREATE PROCEDURE `lib`.`is_valid_name`(IN `id` TEXT, OUT `ret` BOOL)
    NO SQL
    LANGUAGE SQL
    COMMENT 'set `ret` to TRUE if id is valid name, else FALSE'
BEGIN
    -- error in query
    DECLARE EXIT HANDLER
        FOR 1064
    BEGIN
        SET `ret` = FALSE;
    END;
 
    SET @sql_query = CONCAT('DO (SELECT 0 AS ', `id`, ');');
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
 
    SET `ret` = TRUE;
END;
 
CREATE PROCEDURE `test_lib`.`test_is_valid_name`()
	LANGUAGE SQL
	COMMENT 'Test quote_name()'
BEGIN
    CALL `lib`.`is_valid_name`('valid_name', @r);
    CALL `stk_unit`.`assert_true`(@r, 'Specified name is valid');
 
    CALL`lib`.`is_valid_name`('SELECT', @r);
    CALL `stk_unit`.`assert_false`(@r, 'SELECT is a keyword');
    CALL `lib`.`is_valid_name`('a a', @r);
    CALL `stk_unit`.`assert_false`(@r, 'Valid names cant contain spaces');
    CALL `lib`.`is_valid_name`('', @r);
    CALL `stk_unit`.`assert_false`(@r, 'Empty name is not valid');
END;