mysql 读锁 写锁 更新锁_MySQL读取锁选择更新

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:

"conversations", stores metadata: user ids (2), subject, timestamp, etc.

"messages", stores messages that have an FK with a conversation.id

Now I always do:

SELECT a "conversation"

check if metadata allows requested action

perform an UPDATE on the "conversation" (change some metadata, e.g. lastUpdatedTimestamp)

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.