Change Details

See PHI2009. Until MySQL 5.7, `GET_LOCK(...)` releases other locks held on the same connection as a side effect: > Before MySQL 5.7, only a single simultaneous lock can be acquired and GET_LOCK() releases any existing lock. > https://dev.mysql.com/doc/refman/5.7/en/locking-functions.html#function_get-lock That is: ``` mysql> GET_LOCK('A', 0); msyql> GET_LOCK('B', 0); # Releases lock "A"! ``` I think this has escaped notice through a combination of factors: - The behavior is wildly surprising. - Most development/testing occurs against newer versions of MySQL that don't have this behavior. - We currently only stack locks in one specific workflow (when accepting writes against non-leader repository nodes). - This is only observable in a multi-master writable cluster with a fairly high write rate. Most test environments don't have this behavior, and `secure` doesn't have a high enough write rate to hit it. In the short term, the fix is: - Never issue `GET_LOCK()` on a connection already holding a lock. In the longer term, perhaps: - Require MySQL 5.7 or newer, or condition this logic on old versions of MySQL, since it has a small performance cost and the old `GET_LOCK()` behavior is wholly ridiculous.

See PHI2009. Until MySQL 5.7, `GET_LOCK(...)` releases other locks held on the same connection as a side effect: > Before MySQL 5.7, only a single simultaneous lock can be acquired and GET_LOCK() releases any existing lock. > https://dev.mysql.com/doc/refman/5.7/en/locking-functions.html#function_get-lock That is: ``` mysql> GET_LOCK('A', 0); msyql> GET_LOCK('B', 0); # Releases lock "A"! ``` I think this has escaped notice through a combination of factors: - The behavior is wildly surprising. - Most development/testing occurs against newer versions of MySQL that don't have this behavior. - We currently only stack locks in one specific workflow (when accepting writes against non-leader repository nodes). - This is only observable in a multi-master writable cluster with a fairly high write rate. - (See Below) The code accidentally attempts to prevent it. - (See Below) The reproduction case is even more complicated and subtle than I initially believed, and requires an external connection be improperly returned to the connection pool after a failure to acquire a write lock. Most test environments don't have this behavior, and `secure` doesn't have a high enough write rate to hit it. In the short term, the fix is: - Never issue `GET_LOCK()` on a connection already holding a lock. In the longer term, perhaps: - Require MySQL 5.7 or newer, or condition this logic on old versions of MySQL, since it has a small performance cost and the old `GET_LOCK()` behavior is wholly ridiculous.

See PHI2009. Until MySQL 5.7, `GET_LOCK(...)` releases other locks held on the same connection as a side effect: > Before MySQL 5.7, only a single simultaneous lock can be acquired and GET_LOCK() releases any existing lock. > https://dev.mysql.com/doc/refman/5.7/en/locking-functions.html#function_get-lock That is: ``` mysql> GET_LOCK('A', 0); msyql> GET_LOCK('B', 0); # Releases lock "A"! ``` I think this has escaped notice through a combination of factors: - The behavior is wildly surprising. - Most development/testing occurs against newer versions of MySQL that don't have this behavior. - We currently only stack locks in one specific workflow (when accepting writes against non-leader repository nodes). - This is only observable in a multi-master writable cluster with a fairly high write rate. - (See Below) The code accidentally attempts to prevent it. - (See Below) The reproduction case is even more complicated and subtle than I initially believed, and requires an external connection be improperly returned to the connection pool after a failure to acquire a write lock. Most test environments don't have this behavior, and `secure` doesn't have a high enough write rate to hit it. In the short term, the fix is: - Never issue `GET_LOCK()` on a connection already holding a lock. In the longer term, perhaps: - Require MySQL 5.7 or newer, or condition this logic on old versions of MySQL, since it has a small performance cost and the old `GET_LOCK()` behavior is wholly ridiculous.