Menu

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

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|

+---------------------+

1row inset(0.04sec)

mysql>SELECT GET_LOCK('abcde',10);<--Thisreleases the lock on ABC

+-----------------------+

|GET_LOCK('abcde',10)|

+-----------------------+

|1|

+-----------------------+

1row inset(0.00sec)

mysql>SELECT IS_FREE_LOCK('abc');<--Confirmation ABC has been released.

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|

+---------------------+

1row inset(0.01sec)

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

+---------------------------+

1row inset(0.01sec)

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:

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.

5 thoughts on “Making GET_LOCK behavior more predictable cross version with query rewrite”

Note that the query rewrite is a weak to useless tool for this purpose, as it requires enumerating a rule for every possible query form that might use get_lock().

Sure, it might help work around a specific case, but it wont “fix” any dynamic code generation that includes locks.

To me its a big pity that the query rewrite functionality is not more generic. If it allowed one to match *fragments*, then one could write a rewrite rule that allowed one to conver /any/ use of GET_LOCK() into the appropriate munged query. I can think of multiple uses for matching query fragments, and only few for matching exact queries.

I think a generic “rerwrite any use of X function as Y function” would be much more powerful and useful than the current query rewrite logic, and would also would cover the GET_LOCK() regression in 5.7.

One of the goals of query rewrite is to be very low overhead. It piggybacks on our statement digesting functionality internally, and only requires a simple hash table lookup to see if a statement requires rewrite. In the event that it does; the query is rewritten, and parsed again, so if there is a measurable cost, it should only be on those statements that require rewrite.