MySQL读取锁选择更新 [英] Mysql read lock SELECT FOR UPDATE

问题描述

编辑

我使用 node.js felixge-mysql 并具有连接池.

原始

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

1. 对话"，存储元数据:用户ID(2)，主题，时间戳等.
2. 邮件"，存储带有对话的FK邮件.id

现在我总是这样做:

1. SELECT一个对话"
2. 检查元数据是否允许请求的操作
3. 对会话"执行UPDATE(更改某些元数据，例如lastUpdatedTimestamp)
4. 可能INSERT将一条消息转换为消息".

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

对话UPDATE和可能的消息INSERT将在交易中发生.

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

第一季度

现在，从我选择对话行开始，如何读取它?但是当元数据导致用户错误"(例如，当前userId不是此"对话中的userId)时，仍然能够释放锁定.

第二季度

现在，我正在使用redis'locks'数据库，该数据库通过使用Lua锁定给定的ID并使用node.js事件来释放此锁定.这些Redis锁定有超时. (例如1000毫秒).有没有办法在mysql锁定上设置超时?

解决方案

目前尚不清楚您要完成什么.但据我所知，您所要问的是，没有任何本机的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检索它

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进行检查，然后再执行UPDATE，则有可能另一个会话在这两个单独的语句之间进行滑动……如果不增加明显的延迟，很难真正做到这一点.但是，如果我们要麻烦锁定行，则最好做对.

请注意，当我们要检查已持有很长时间的锁时，存储在locked_at列中的值会起作用.某个会话可能已获得一些锁定，但该会话已消失，并且这些锁定将永远不会被释放.可以安排一个单独的维护任务来查看表中是否有真正的locked_at值.

或者，您可以使用locked_at对锁进行更复杂的外观，并考虑真正的旧锁即将过期.

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

===

注意:

我以前从未在生产系统中使用过这种方法.我的团队通常关心的问题是胜利中的最后一个"方案，在该方案中，更新可能会覆盖另一个会话最近进行的更改.但是我们正在解决的问题似乎与您要解决的问题完全不同.

要解决获胜中的最后一个"问题，我们在表中添加了一个版本"列(简单整数).检索行时，我们检索版本列的当前值.当会话稍后要更新该行时，它通过将先前检索的版本值与表中的当前值进行比较，来验证是否对该行没有其他更新.如果版本号匹配，我们允许更新该行，并将版本号增加一. (我们都在单个UPDATE语句中完成所有操作，因此该操作是原子操作，以避免出现竞争情况，即两个同时进行的会话都不会进行更新.我们之所以使用此模式，是因为我们实际上并不希望通过一次会话，并永久保持锁定状态.我们只是在防止同时进行的更新彼此覆盖，这又一次听起来与您要完成的目标不同.

EDIT

I use node.js felixge-mysql and have a pool of mysql connections.

ORIGINAL

I have a mysql db in which there are 2 tables:

1. "conversations", stores metadata: user ids (2), subject, timestamp, etc.
2. "messages", stores messages that have an FK with a conversation.id

Now I always do:

1. SELECT a "conversation"
2. check if metadata allows requested action
3. perform an UPDATE on the "conversation" (change some metadata, e.g. lastUpdatedTimestamp)
4. possibly INSERT a message into "messages".

Next to messages a user could also block the conversation (from his side!)

The conversation UPDATE and possible message INSERT will happen inside a transaction.

One caveat: After I SELECT the conversation row and check the metadata at the application level it could be that the requested action is not allowed, leading to the UPDATE and possible INSERT never to be executed!

Q1

Now how to read lock the conversation row from the moment I select it? But still be able to release the lock when the metadata leads to a "user error" (e.g. current userId is not a userId in "this" conversation).

Q2

Right now I'm using a redis 'locks' db which locks a given id by using Lua and use node.js events to release this lock. These redis locks have a timeout. (e.g. 1000 millisecs). Is there a way to set a timeout on a mysql lock?

解决方案

It's not entirely clear what you are trying to accomplish. But as best I understand what you are asking, there isn't any native MySQL "lock" mechanism that is going to do what you need. (You want a session to be able to "lock" a row to prevent it from being "read" (or modified) by another session.

To accomplish what you are trying to do, that sounds like an application issue, not a database integrity issue.

The approach I would use to solve that would be to add two columns to the table:

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

For a session attempting to obtain the lock on row, I'd check whether or not the row was already locked by a different session, and if not, mark the row as locked by this session:

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

If the return from the update is "zero rows updated", you know you didn't obtain a lock. If the return is non-zero, you know you obtained a lock (on at least one row).

To check whether my session was already holding a lock on the row:

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

Once I knew I had an "lock" on the row, then I could retrieve it with a simple SELECT

SELECT ... WHERE unique_row_identifier = someval`

To release the lock, the session would set the locked_by and locked_at columns back to NULL.

A "read only" session could avoid reading a locked row by checking the values in the locked_by column:

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

The row would only be returned if it wasn't locked.

Note that I'd do the locking and checking in a single statement, to avoid a race condition with simultaneous situations. If I ran a SELECT to do a check, followed by an UPDATE, there's a potential for another session to slip in between those two separate statements... it would be hard to really cause that to happen, without adding a significant delay. But if we're going to bother with locking rows, we'd best do it right.

Note that the value stored in the locked_at column comes into play when we want to check for locks that have been held for a long time. Maybe a session took some locks, and that session has gone away, and those locks will never be released. A separate maintenance task could be scheduled to look through the table for really old locked_at values.

Alternatively, you could use the locked_at to do a more sophisticated look for a lock, and consider really old locks to be expired.

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

===

NOTE:

I've never used that approach in a production system before. The problem my team is typically concerned with is the "last one in wins" scenario, where an update will potentially overwrite a change that another session has recently made. But the problem we are solving seems to be quite different from what you are trying to accomplish.

To resolve the "last one in wins" problem, we add a single "version" column (simple integer) to the table. When we retrieve a row, we retrieve the current value of the version column. When the session later wants to update the row, it verifies that no other update has been made to the row, by comparing the previously retrieved version value with the current value in the table. If the version numbers match, we allow the row to be updated, and increment the version number by one. (We do this all within a single UPDATE statement, so the operation is atomic, to avoid a race condition where two simultaneous sessions don't both do updates. We use this pattern because we don't really want to have a row locked by a session, and having the lock held forever. We're just preventing simultaneous updates from overwriting one another, which again, is different from it sounds like you are trying to accomplish.

这篇关于MySQL读取锁选择更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！