Making GET_LOCK behavior more predictable cross version with query rewrite

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

MySQL 5.7 Behavior

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:

(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 UsageBackwards 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:

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:

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:

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]