Mysql 读锁 SELECT FOR UPDATE

EDIT

I use Node.js felixge-mysql https://github.com/felixge/node-mysql#pooling-connections并有一个poolmysql 连接数。

ORIGINAL

我有一个 mysql 数据库，其中有 2 个表：

1. “对话”，存储元数据：用户 ID (2)、主题、时间戳等。
2. “messages”，存储具有对话 ID 的 FK 的消息

现在我总是这样做：

1. SELECT一个对话”
2. 检查元数据是否允许请求的操作
3. 执行一个UPDATE关于“对话”（更改一些元数据，例如lastUpdatedTimestamp）
4. 可能INSERT一条消息变成“消息”。

除了消息之外，用户还可以block谈话（从他这边！）

对话UPDATE以及可能的消息INSERT将发生在交易内。

一个警告：在我之后SELECT对话行并检查应用程序级别的元数据，可能是不允许请求的操作，从而导致UPDATE和可能的INSERT永远不会被处决！

Q1

现在如何从选择对话行的那一刻起读取锁定对话行？但当元数据导致“用户错误”时，仍然能够释放锁定（例如当前 userId 不是“this”对话中的 userId）。

Q2

现在我正在使用 redis 'locks' db，它通过使用 Lua 锁定给定的 id，并使用 node.js 事件来释放此锁。这些redis锁有超时时间。 （例如 1000 毫秒）。有没有办法给mysql锁设置超时？

您正在寻找命名锁（小心，危险的东西，不要在生产服务器上尝试锁：D）。

看一眼：

• GET_LOCK(str, timeout) https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock
• RELEASE_LOCK(str) https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_release-lock
• IS_FREE_LOCK(str) https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_is-free-lock
• IS_USED_LOCK(str) https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_is-free-lock

A1：选择一个唯一的字符串来锁定和使用GET_LOCK在其上（比如说，GET_LOCK('conversation_' || [id]);如果它返回1锁是你的。做你想做的事，稍后再打电话RELEASE_LOCK（考虑所有可能的情况，包括错误）。

A2：第二个参数GET_LOCK超时时间为seconds。如果操作超时GET_LOCK将返回0.

来自官方文档

GET_LOCK(str,timeout)

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). If you have a lock obtained with GET_LOCK(), it is released when you execute RELEASE_LOCK(), execute a new GET_LOCK()¹, or your connection terminates (either normally or abnormally)². Locks obtained with GET_LOCK() do not interact with transactions. That is, committing a transaction does not release any such locks obtained during the transaction.

This function can be used to implement application locks or to simulate record locks. Names are locked on a server-wide basis³. If a name has been locked by one client, GET_LOCK() blocks any request by another client for a lock with the same name. This enables clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also enables a client that is not among the set of cooperating clients to lock a name, either inadvertently or deliberately, and thus prevent any of the cooperating clients from locking that name. One way to reduce the likelihood of this is to use lock names that are database-specific or application-specific. For example, use lock names of the form db_name.str or app_name.str.

粗体是我的：

1. 意味着每个连接只能持有一个锁（对于您的用户案例来说不是问题）
2. 意味着一旦你解锁，锁就会被释放close连接
3. 意味着两个不同的连接（即使来自同一个池）可能无法立即获取相同的外观。