A common problem that exists in distributed systems is prevent multiple instances of a service or script from executing at the same time. As part of a project I wanted to ensure that only a single instance of a script was running and if someone tried to start another instance it would just exit.

The script was already reading data from a MySQL table so I elected to use MySQLs named locks. I've moved the locking logic out into a library for general usage. An example usage script is available in the repo as well.

Running two instances of the example script:

python example.py --host $MYSQL_HOST --port 3306 --username $USER --lock_name test_lock
Enter your mysql password
Password: 
Successfully acquire lock: test_lock
We acquired the lock
Successfully release lock
Successfully released our lock

Second instance:

python example.py --host $MYSQL_HOST --port 3306 --username $USER --lock_name test_lock
Enter your mysql password
Password: 
Lock is currently in use
The lock is in use or an error occurred, exiting....

A look at the code

The __init__ for the client take all the parameters necessary to create the MySQL client. The timeout value defaults to -1 meaning an infinite lock will be acquire. If the lock is infinite it will only be released if the connection that created it terminates, either normally or abnormally.

    def __init__(self, host, port, user, passwd, lock_name, timeout=-1):

        self.username = user
        self.password = passwd
        self.mysql_host = host
        self.mysql_port = port
        self.lock_name = lock_name

        """
        We default the timeout to -1 for an infinite lock
        If an infinite lock is used the lock is only released if the connection terminates
        """
        self.lock_timeout = timeout

        self.mysql_connection = self._setupMySqlConnection()

The locking logic first checks if the lock is currently free. If the lock isn't free the method returns False. This could be changed to not check the lock status and just attempt to acquire it. This would essentially create a spinlock , as the query will continue to try to get the lock, until it can or times out. If the lock isn't in use, we attempt to acquire it. If we succeed we return True back to the user, or an error occurs or we can't acquire the lock we'll return False.

def aquireLock(self):

        cursor = self.mysql_connection.cursor()

        # First lets check if the the lock is free
        cursor.execute("SELECT IS_FREE_LOCK('{}')".format(self.lock_name))

        # IS_FREE_LOCK will return 0 if the lock is in use, 1 if the lock is free, or NULL for an error
        result = cursor.fetchone()
        if result[0] == 0:
            # lock is in use
            print "Lock is currently in use"
            return False
        elif result[0] == 1:
            # Lock is free attempt to acquire it
            cursor.execute("SELECT GET_LOCK('{}', {})".format(self.lock_name, self.lock_timeout))
            lock_result = cursor.fetchone()
            if lock_result[0] == 0:
                # Lock attempt failed
                print "Failed to acquire lock"
                return False
            elif lock_result[0] == 1:
                # Locked
                print "Successfully acquire lock: {}".format(self.lock_name)
                return True
            else:
                # Error occurred
                print "An error occurred will trying to acquire lock"
                return False
        else:
            # NULL return
            print "Unable to check lock status"
            return False

The release logic is essentially the same as the acquire lock logic, without the checking for the lock being in use.

I will likely move the library over to using the official python mysql connector rather than the MySQLdb library. I will also add a method to the client to spin when attempt to acquire the lock rather than giving up if the lock is in use.

Feel free to contribute or post issues.