MySQL表锁定：持有者读写，其他会话只能读取？

Question

是否可以锁定一个表，使持有者可以读写，而其他会话仅能读取？

根据文档的描述，读锁定允许每个人只读取，写锁定只允许持有者读写，而其他会话则无法访问。似乎让持有者能够读写，而其他会话仅能读取，这是一种非常经常需要的行为——可能是最常需要的行为。

也许实现这种方案的性能损失会太高？

- John Bachir

---

约翰，你有没有找到你问题的答案？ - Jaka Jančar

嗯，我记不起来使用案例是什么了。我觉得我没有找到解决方案。 - John Bachir

不行，这会导致数据损坏。但是请看一下行锁定。 - Zaffy

Answer 1

现有答案中有很多正确的词语，但似乎没有人给出清晰的答案。我来试试。

正如您在LOCK TABLES文档中已经看到的，它不能用于此目的，因为对于READ锁：

会话持有锁可以读取表格（但无法写入）。

而对于WRITE锁：

只有持有锁的会话才能访问表格。在锁定被释放之前，没有其他会话可以访问它。

这个效果几乎不可能在任意引擎表格上实现，但它可以通过事务引擎InnoDB实现。

让我们考虑一下什么意味着单个会话在表上保持恒定的写锁，而其他表可以按事务方式从该表读取数据。这意味着我们有一个开放的长期事务（假设为W事务），它锁定了修改表并且其他事务（在其他会话中）可以读取已经修改但尚未提交的数据。就隔离级别而言，这意味着我们应该将默认隔离级别设置为READ-UNCOMMITTED，这样我们就不必为每个新会话更改隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

但是我们的事务 W，应该使用更强的隔离级别，否则我们无法对表应用任何锁定。 READ-COMMITTED 不够强，但是 REPEATABLE-READ 正是我们想要的。也就是在开始一个 W 事务之前，我们应该为当前会话设置事务级别：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

现在，如何锁定整个表格。让我们创建一个表格：

CREATE TABLE t (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  val VARCHAR(45) NOT NULL,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

LOCK IN SHARE MODE 不是我们想要的：

如果这些行中的任何一行被另一个尚未提交的事务更改，则您的查询将等待该事务结束，然后使用最新值。

LOCK FOR UPDATE 似乎符合我们的需求：

SELECT ... FOR UPDATE 锁定行和任何相关联的索引条目。

现在我们所需要的就是锁定行。我们可以做的最简单的事情就是锁定主键。 COUNT(*) 对于 InnoDB 来说会进行完整的索引扫描（因为 InnoDB 不知道确切的行数）。

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT COUNT(*) FROM t FOR UPDATE;
INSERT INTO t VALUES (NULL, '');

现在您可以打开其他会话，尝试从表中读取数据，并尝试从这些会话中添加或修改现有数据。

问题是，您应该提交对W的修改。一旦提交事务，锁定就会释放，并且所有等待插入或更新的操作也会被应用，即使您使用以下方式提交它：

 COMMIT AND CHAIN; SELECT COUNT(*) FROM ti FOR UPDATE;

故事的寓意是，拥有两个MySQL账户会更容易：a）写入账户具有INSERT、UPDATE和DELETE GRANT权限，b）阅读账户则没有这些权限。

- newtover

Answer 2

请查看LOCK IN SHARE MODE。

这将让您设置非阻塞读锁。

但是请记住，这可能会导致死锁！确保您可以接受进程具有过时信息。

- Byron Whitlock

---

我能用这个锁定整个表吗？ - John Bachir

当然，只需执行 begin transaction; select * from table lock in share mode 即可。但最好还是直接使用 lock table。http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html - Byron Whitlock

只要您首先调用begin transaction，其他进程的插入将不允许使用lock in share mode。要锁定表以使任何人都无法写入但其他人可以读取，请调用LOCK TABLES <table> as read_lock READ。文档对此非常清楚。 - Byron Whitlock

因此，总结一下，使用LOCK IN SHARE MODE进行行级锁定，使用LOCK TABLES ... READ锁定整个表。这两个命令允许其他进程读取但不写入。 - Byron Whitlock

不，你是错误的。“表锁仅保护其他会话的不当读取或写入。持有锁的会话（即使是读锁）可以执行诸如DROP TABLE之类的表级操作。”防止持有者写入其锁定的表将是相当无用的。 - Byron Whitlock

Answer 3

有SELECT ... FOR UPDATE，它将锁定行以防止其他调用者执行SELECT ... FOR UPDATE，但不会对执行SELECT的任何人进行锁定。 UPDATE将等待锁定。

当您想获取一个值并在没有任何人更改该值且您未注意到的情况下推送更新时，这非常有用。请注意，添加太多此类操作将使您陷入死锁。

- Emil Ivanov

Answer 4

你可能会发现InnoDB引擎默认情况下可以满足你的需求：写操作不会阻塞读操作。但是你需要注意事务隔离级别，以便在需要时可用写操作。

- Joshua Martell

---

您可以在这里阅读更多信息： http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html 和 http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html - Joshua Martell

我对此很熟悉，但据我所知，这仅与行锁定有关。如果文档中的含义是它同样适用于两种情况，那么这非常微妙... - John Bachir