MySQL has supported the GET_LOCK() function for a large part of its history. As the manual notes, GET_LOCK() can be used to implement application locks or to simulate record locks.
Changes in MySQL 5.7
In MySQL 5.7 we improved GET_LOCK() to be based on our internal meta-data locking system (MDL). This allowed us to lift the restriction that each subsequent call to GET_LOCK()would release all previous locks.
However, there is a chance that some applications depended on the previous behaviour of locks being released. For this subset of users, it is not always easy to inspect an application to see when this behaviour is required. This creates challenges in upgrading:
MySQL 5.6 Behavior
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> SELECT GET_LOCK(‘abc’, 10); +——————————–+ | GET_LOCK(‘abc’, 10) | +——————————–+ | 1 | +——————————–+ 1 row in set (0.04 sec) mysql> SELECT GET_LOCK(‘abcde’, 10); <— This releases the lock on ABC +———————————–+ | GET_LOCK(‘abcde’, 10) | +———————————–+ | 1 | +———————————–+ 1 row in set (0.00 sec) mysql> SELECT IS_FREE_LOCK(‘abc’); <— Confirmation ABC has been released. +——————————–+ | IS_FREE_LOCK(‘abc’) | +——————————–+ | 1 | +——————————–+ 1 row in set (0.01 sec) |
MySQL 5.7 Behavior
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> SELECT GET_LOCK(‘abc’, 10); +——————————–+ | GET_LOCK(‘abc’, 10) | +——————————–+ | 1 | +——————————–+ 1 row in set (0.01 sec) mysql> SELECT GET_LOCK(‘abcde’, 10); <— This no longer releases the lock on ABC +———————————–+ | GET_LOCK(‘abcde’, 10) | +———————————–+ | 1 | +———————————–+ 1 row in set (0.00 sec) mysql> SELECT IS_FREE_LOCK(‘abc’); +——————————–+ | IS_FREE_LOCK(‘abc’) | +——————————–+ | 0 | +——————————–+ 1 row in set (0.00 sec) |
An additional incompatibility between the previous behaviour and new, is that lock names are now restricted to 64 characters in length.
Creating an upgrade path
The easiest way to be able to upgrade, is to make MySQL 5.7 behave more like MySQL 5.6 (at least initially). For new functionality, or over time as code has been inspected, the MySQL 5.7 behavior can be slowly introduced.
Using version-specific syntax it is possible to modify application code to use an implementation of GET_LOCK() that does exactly this:
1 2 3 4 5 6 7 | mysql> SELECT /*!50700 get_lock_result FROM (SELECT RELEASE_ALL_LOCKS(), */ GET_LOCK(‘abc’, 10) /*!50700 as get_lock_result) a */; +——————————–+ | GET_LOCK(‘abc’, 10) | +——————————–+ | 1 | +——————————–+ 1 row in set (0.01 sec) |
(The /*!50700 means “only run this code in 5.7+”)
The second behavior change can be overcome by running the lock name through a hashing function. For example:
| Previous Usage | Backwards Compatible Usage |
| GET_LOCK(‘abc’, 10) | GET_LOCK(sha1(‘abc’), 10) |
| RELEASE_LOCK(‘abc’) | RELEASE_LOCK(sha1(‘abc’)) |
| IS_FREE_LOCK(‘abc’) | IS_FREE_LOCK(sha1(‘abc’)) |
| IS_USED_LOCK(‘abc’) | IS_USED_LOCK(sha1(‘abc’)) |
To combine both, the usage for GET_LOCK() would become:
1 2 3 4 5 6 7 | mysql> SELECT /*!50700 get_lock_result FROM (SELECT RELEASE_ALL_LOCKS(), */ GET_LOCK(SHA1(‘abc’), 10) /*!50700 as get_lock_result) a */; +—————————————–+ | GET_LOCK(SHA1(‘abc’), 10) | +—————————————–+ | 1 | +—————————————–+ 1 row in set (0.01 sec) |
Using Query Rewrite
MySQL 5.7 also supports a feature called Query rewrite (manual). With this feature it is possible to also make legacy application preserve the old get_lock behavior without needing to make any code changes. Here is a very simple example:
1 2 | INSERT INTO query_rewrite.rewrite_rules(pattern, replacement) VALUES (‘SELECT GET_LOCK(?, ?)’, “SELECT get_lock_result FROM (SELECT RELEASE_ALL_LOCKS(), GET_LOCK(?, ?) as get_lock_result) a”); CALL query_rewrite.flush_rewrite_rules(); |
With this query rewrite rule installed I can repeat the original test-case as in MySQL 5.6. I have enabled warnings to be printed to assist in readability of when rewrites are happening:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | mysql> SELECT GET_LOCK(‘abc’, 10); +————————–+ | get_lock_result | +————————–+ | 1 | +————————–+ 1 row in set, 1 warning (0.00 sec) Note (Code 1105): Query ‘SELECT GET_LOCK(‘abc‘, 10)’ rewritten to ‘SELECT get_lock_result FROM (SELECT RELEASE_ALL_LOCKS(), GET_LOCK(‘abc‘, 10) as get_lock_result) a’ by a query rewrite plugin mysql> SELECT GET_LOCK(‘abcde’, 10); +————————–+ | get_lock_result | +————————–+ | 1 | +————————–+ 1 row in set, 1 warning (0.00 sec) Note (Code 1105): Query ‘SELECT GET_LOCK(‘abcde‘, 10)’ rewritten to ‘SELECT get_lock_result FROM (SELECT RELEASE_ALL_LOCKS(), GET_LOCK(‘abcde‘, 10) as get_lock_result) a’ by a query rewrite plugin mysql> SELECT IS_FREE_LOCK(‘abc’); +——————————–+ | IS_FREE_LOCK(‘abc’) | +——————————–+ | 1 | +——————————–+ 1 row in set (0.00 sec) |
Conclusion
I expect that this behavior change will affect only a small percentage of applications, as in most cases the previous behavior was not something that was useful to rely upon. None the less, it was a feature that had not changed in perhaps 10+ years, so it is good to be able to have a safe upgrade path.
[Source:- Mysqlserverteam]
