MySQL mutex sometimes allows acquiring of the same locknames

[alexjeen]

What steps will reproduce the problem?

If your MySQL threads are different from each other, occasionally they might receive the same locks. This is a race condition and it might be different on your version of MySQL of MariaDB.

What is the expected result?

If you call mutex acquire, with the same name, they should not get locked at the same time.

Read more info here: https://bugs.mysql.com/bug.php?id=95551

The important note to take away: * • Previously, GET_LOCK() permitted acquisition of only one named lock at a time, and a second GET_LOCK() call released any existing lock. Now GET_LOCK() permits acquisition of more than one simultaneous named lock and does not release existing locks. Applications that rely on the behavior of GET_LOCK() releasing any previous lock must be modified for the new behavior.*

And in the docs of GET_LOCK https://mariadb.com/kb/en/get_lock/ it says that you can use the same lock names multiple times.

What do you get instead?

I have two ECS containers running against the same MySQL database, and they acquire locks at the same time:

A locks mutex from 14:22:20 till 14:22:39 B locks mutex from 14:22:05 tm 14:22:25

As you can see, they overlap from seconds 20-39 and 5-25 (there is a 5 second overlap from seconds 20-25)

Proposed solution

In the Yii2 implementation we intend to get one lock by name, so we must first see if this lock is available with IS_FREE_LOCK and only then we lock it. This is how I've implemented it:

    /**
     * Acquires lock by given name.
     * @param string $name of the lock to be acquired.
     * @param int $timeout time (in seconds) to wait for lock to become released.
     * @return bool acquiring result.
     * @see http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_get-lock
     */
    protected function acquireLock($name, $timeout = 0)
    {
        // get the name from parent class
        $hashedName = $this->hashLockName($name);
        // retry acquiring the lock
        return $this->retryAcquire($timeout, function () use ($name, $hashedName, $timeout) {
            // check if the lock is free
            $isFree = $this->db->useMaster(function ($db) use ($name, $hashedName) {
                /** @var \yii\db\Connection $db */
                return (bool) $db->createCommand(
                    'SELECT IS_FREE_LOCK(:name)',
                    [':name' => $hashedName]
                )->queryScalar();
            });
            // if its free, stop the process
            if ($isFree) {
                // acquire the mutex
                return $this->db->useMaster(function ($db) use ($name, $hashedName, $timeout) {
                    /** @var \yii\db\Connection $db */
                    return (bool) $db->createCommand(
                        'SELECT GET_LOCK(:name, :timeout)',
                        [':name' => $hashedName, ':timeout' => $timeout]
                    )->queryScalar();
                });
            }
            return false;
        });
    }

Additional info

Q	A
Yii version	2.0.39
PHP version	7.2.16
Operating system	Alpine Linux

[samdark]

@alexjeen would you please make a pull request?

[rob006]

The important note to take away: * • Previously, GET_LOCK() permitted acquisition of only one named lock at a time, and a second GET_LOCK() call released any existing lock. Now GET_LOCK() permits acquisition of more than one simultaneous named lock and does not release existing locks. Applications that rely on the behavior of GET_LOCK() releasing any previous lock must be modified for the new behavior.*

And in the docs of GET_LOCK https://mariadb.com/kb/en/get_lock/ it says that you can use the same lock names multiple times.

This note is about acquiring multiple different (with different name) locks for the same connection. Previously you could have only one lock and calling GET_LOCK() would automatically release previous lock.

And https://bugs.mysql.com/bug.php?id=95551 seems to be related to multi-thread environment, which should be pretty rare in PHP apps and other locks may not work in this case either.

[cebe]

And https://bugs.mysql.com/bug.php?id=95551 seems to be related to multi-thread environment, which should be pretty rare in PHP apps and other locks may not work in this case either.

yii2-queue uses mutex to lock queue workers so there is a valid use case that needs to work. I have a system in production suffering from locking based issues currently, could be related to this issue.

[rob006]

yii2-queue uses mutex to lock queue workers so there is a valid use case that needs to work.

yii2-queue does not use threads.