Am I able to use the mysql built-in function get_lock?

Question

There is a batch job outside. And there are multiple servers that can handle the job. I only want the job to be picked up and processed by one of the many servers during a specific time.

In order to achieve this purpose, when investigating the global lock mechanism, I discovered the get_lock function of mysql.

In addition, since it already depends on mysql, I think this function can be used.

However, what is suspicious is that when looking for a global lock mechanism, most of them use redis, etc., and it is difficult to find cases using get_lock.

So I doubt if mysql's get_lock has any shortcomings.

My current situation is that I am already using mysql, but not redis.

So, can I use mysql's get_lock for global locking? Or are there any shortcomings of mysql's get_lock?

Answer 1

This question has been voted closed, but some suggestions might be helpful.

As I understand it, you have several machines processing the same data set - you want to ensure exclusivity of the data items. You seem to be considering an advisory locking mechanism, where data consumers need to look for locks and then create them before using the data. Advisory locking should only be used as a last resort:

• Data clients need to explicitly opt-in to the locking mechanism - as systems become more complex, it becomes increasingly likely that someone has written some code that fails to use the locking mechanism like everything else. Then problems will occur
• Advisory locking causes state to be superseded - the locking state is saved in a different location than the operation that required the lock. You are now in the realm of CAP theorem

Please try reposting with details of data sinks and data sources and available resources instead of asking if it is a suitable solution to an undefined problem.