mysql - 来自 nodejs mysql 和 mysql2 驱动程序的 Advisory Lock 返回 null

Question

尝试获取建议锁时: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock

我收到的是 null 响应，而不是 MySQL 文档指定的 0 或 1，正如我发出通过MySQL客户端查询。

import test from 'ava'

import mysql from 'mysql1'

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  port: 3306,
  database: 'test',
  password: '',
})

const foo = () => new Promise((resolve) => {
  resolve(1)
})

const multiQuery = () => new Promise((resolve, reject) => {
  const sql = `
    SELECT GET_LOCK('test2', -1) AS test3;
  `
  pool.query(sql, (err, res) => (err == null ? resolve(res) : reject(err)))
})

test(async (t) => {
  const response = await multiQuery()
  console.log(response)
  t.is(await foo(), 1)
})

回应:

这是预期的吗？

Answer 1

MySql 和 MariaDB 在 GET_LOCK 实现上是有区别的。

来自MySql GET_LOCK() Documentation :

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. A negative timeout value means infinite timeout. The lock is exclusive. While held by one session, other sessions cannot obtain a lock of the same name.

来自MariaDB GET_LOCK() Documentation :

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill).

如果发生错误，两种实现都返回 NULL，但是，MariaDB 不支持负超时。因此，当将负整数作为超时参数传递给 GET_LOCK() 函数时，MariaDB 将返回 NULL。

给出我的查询:

SELECT GET_LOCK('test2', -1) AS test3

MariaDB 将以 NULL 响应，而 MySQL 将以 1 响应