Mysql读取锁选择用于更新

Question

编辑

我使用node.js felixge-mysql，并有一个池的mysql连接。

原始

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

1. “会话”，存储元数据:用户ids (2)、主题、时间戳等。
2. " messages "，存储具有带有conversation.id的FK的消息。

现在我总是这样做：

1. SELECT a“对话”
2. 检查元数据是否允许请求的操作
3. 在“会话”上执行UPDATE (更改一些元数据，例如lastUpdatedTimestamp)
4. 可能会将消息INSERT为“消息”。

在消息旁边，用户也可以block对话(从他身边！)

会话UPDATE和可能的消息INSERT将在事务中发生。

请注意:在我SELECT会话行并在应用程序级别检查元数据之后，可能不允许所请求的操作，从而导致永远不执行UPDATE和可能的INSERT！

Q1

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

Q2

现在，我使用的是redis‘lock’db，它使用Lua锁定给定的id，并使用node.js事件释放这个锁。这些红锁有一个超时。(例如1000毫秒)。有办法在mysql锁上设置超时吗？

Answer 1

还不完全清楚你想要实现什么。但据我所知，您所要求的没有任何本地MySQL“锁”机制来满足您的需要。(您希望会话能够“锁定”行，以防止它被另一个会话“读取”(或修改)。

要完成您想要完成的任务，这听起来像是应用程序问题，而不是数据库完整性问题。

解决这个问题的方法是在表中添加两列：

locked_by   - uniquely identify the session holding the row lock
locked_at   - the date/time the row lock was placed 

对于试图获取行上锁的会话，我将检查该行是否已被不同的会话锁定，如果没有，则将该行标记为此会话已锁定：

UPDATE mytable 
   SET locked_by = 'me'
     , locked_at = NOW()
 WHERE unique_row_identifer = someval
   AND locked_by IS NULL;

如果更新返回的是“零行更新”，您就知道没有获得锁。如果返回为非零，您知道您获得了一个锁(至少在一行上)。

若要检查会话是否已在行上持有锁，请执行以下操作：

SELECT 1
  FROM mytable t
 WHERE t.unique_row_identifier = someval
   AND locked_by = 'me';

一旦我知道我在行上有一个“锁”，我就可以通过一个简单的选择来检索它。

SELECT ... WHERE unique_row_identifier = someval`

为了释放锁，会话将locked_by和locked_at列设置为NULL。

“只读”会话可以通过检查locked_by列中的值来避免读取锁定的行：

SELECT t.*
  FROM mytable t
 WHERE t.unique_row_identifier = someval
   AND t.locked_by IS NULL

只有在未锁定行的情况下才会返回该行。

请注意，我会在一条语句中执行锁定和签入操作，以避免同时发生情况的争用条件。如果我运行了一个SELECT来进行检查，然后是一个更新，那么另一个会话就有可能在这两个单独的语句之间插入.如果不增加一个重大的延迟，就很难真正做到这一点。但是，如果我们要麻烦锁行，我们最好把它做好。

注意，当我们想要检查已持有很长时间的锁时，存储在locked_at列中的值就会起作用。也许一个会话使用了一些锁，而这个会话已经消失了，而这些锁永远不会被释放。可以安排一个单独的维护任务，在表中查找非常旧的locked_at值。

或者，您可以使用locked_at对锁进行更复杂的查找，并将真正的旧锁视为过期。

 WHERE ( locked_at IS NULL OR locked_at < (NOW() + INTERVAL 24 HOUR) )

===

注意：

我以前从未在生产系统中使用过这种方法。我的团队通常关注的问题是“最后一个胜利”场景，在这种情况下，更新可能会覆盖另一个会话最近所做的更改。但我们正在解决的问题似乎与你想要完成的完全不同。

为了解决“wins中的最后一个”问题，我们在表中添加了一个"version“列(简单整数)。检索一行时，检索version列的当前值。当会话稍后要更新行时，它将先前检索的版本值与表中的当前值进行比较，从而验证没有对该行进行任何其他更新。如果版本号匹配，则允许更新行，并将版本号增加一个。(我们在一个UPDATE语句中完成所有这些操作，因此操作是原子的，以避免两个同时的会话不同时进行更新的争用条件。我们使用这种模式是因为我们不希望一个会话锁定一个行，并且永远保持锁。我们只是在防止同时进行的更新覆盖另一个，这与你想要实现的不同。