如何故意锁定MySQL行，以便SELECT都将返回错误? [英] How to deliberately lock a MySQL row such that even SELECT will return an error?

问题描述

我正在尝试使用MySQL行锁定来基本上在一行上模拟MuteEx.可以说我的表有2列，一个id和一个文本字段，以及三个条目(1，a)(2，b)和(3，c). SELECT * FROM表；将返回这些结果. 我可以按常规方式锁定特定行.

I'm trying to use MySQL row locking to basically emulate a MuteEx on a row. Lets say that my table has 2 columns, an id and a text field, and three entries (1,a) (2,b) and (3,c). SELECT * FROM table; would return these results. I can lock a specific row the normal way.

START TRANSACTION;
BEGIN;
SELECT * FROM table WHERE id = '2' FOR UPDATE;

但是，如果从第二个连接中我要从表中进行SELECT *.它将返回所有3个结果.行级锁定是否有一种方法可以从根本上防止任何SELECT看到/使用被锁定的行?基本上，我试图阻止任何人使用当前正在使用/操作的行，或者甚至在查看行时也无法查看该行的数据(因为正在使用/操作该行)，因为它在SELECT时不准确

However, if from a second connection I were to SELECT * from table. It would return all 3 results. Is there a way for row level locking to basically prevent any SELECT from seeing/using a row that is locked? Basically I'm trying to prevent anyone from using the row that is currently being used/manipulated, or even viewing the row as its data (since it's being used/manipulated) can't be trusted to be accurate at the time of a SELECT.

推荐答案

您需要LOCK IN SHARE MODE.将它与SELECT一起使用可确保没有其他人使用FOR UPDATE锁定任何行.

You need a LOCK IN SHARE MODE. Using it with SELECT guarantees no one else is locking any rows with FOR UPDATE.

例如

客户端A进行SELECT * FROM table WHERE type=2 FOR UPDATE

客户B做SELECT * FROM table LOCK IN SHARE MODE并挂在这里

客户端A写入/插入/更新某些内容，然后执行COMMIT

Client A writes/INSERTs/UPDATEs something then does a COMMIT

客户端B现在取消冻结并恢复处理

Client B now un-freezes and resumes processing

这篇关于如何故意锁定MySQL行，以便SELECT都将返回错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！