当前位置：文江博客话题详情

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

发布于 2024-12-11 19:54:22 字数 245 浏览 0 评论 0原文

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

文档似乎建议读锁允许每个人只读，写锁只允许持有者读写，其他会话无权访问。似乎让持有者能够读取和写入而其他会话只能读取将是一种非常频繁需要的行为——也许是最频繁需要的行为。

也许实施这种方案对性能的影响会太大？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吹梦到西洲 2024-12-18 19:54:22

看看锁定共享模式。

这将允许您设置非阻塞读锁。

但请记住，这可能会导致僵局！确保您可以接受包含过时信息的流程。

回复收藏 0 原文

一刻暧昧 2024-12-18 19:54:22

现有答案中有很多正确的话，但似乎没有人给出明确的答案。我会尝试。

正如您在锁定表的文档中已经看到的那样，它不能用于此目的，因为对于 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;

锁定共享模式不是我们想要的：

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

锁定更新似乎可以满足我们的需要:

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 中提交修改，并且一旦您提交事务，锁就会被释放，并且所有等待的插入或更新也会被应用，即使您提交了它with：

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

这个故事的寓意是，拥有两个 MySQL 帐户要容易得多：a) 具有 INSERT、UPDATE 和 DELETE 的写入帐户授予权限，以及 b) 读取尚未授予权限的帐户。

There are many correct words in existing answers, but no one seems to have given a clear answer. I will try.

As you have already seen in documentation on LOCK TABLES, it can not be used for the purpose, since for the READ lock:

The session that holds the lock can read the table (but not write it).

and for the WRITE lock:

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

That is the effect can hardly be achievable with an arbitrary engine table, but it can be achived with a transactional engine, that is InnoDB.

Let's think about what means that a single session keeps a constant write lock on a table and other tables can read data from the table in terms of transactions. That means that we have an open long living transaction (let it be W transaction) which locks a table for modifications and other transactions (in other sessions) can read data that is already modified, but not yet committed. In terms of isolation levels, that means that we should set up the default isolation level to READ-UNCOMMITTED, so that we would not have to change the isolation level for each new session:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

But our transaction W, should use a stronger isolation level, otherwise we can not apply any locking to our table. READ-COMMITTED is not strong enough, but REPEATABLE-READ is exactly what we want. That is befor starting a W transaction we should set the transaction level for the current session:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Now, how to lock the whole table. Let's create a table:

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

LOCK IN SHARE MODE is not what we want:

If any of these rows [that are read] were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

LOCK FOR UPDATE seems to do what we need:

SELECT ... FOR UPDATE locks the rows and any associated index entries.

Now all we need is to lock the rows. The simplest thing we can to is to lock the primary key. COUNT(*) does a full index scan for InnoDB (since InnoDB does not know that exact row count).

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, '');

Now you can open other sessions and try to read the data from the table and try to add or modify the existing data from those sessions.

The problem is though, that you should commit the modifications in W, and as soon as you commit the transaction, the lock is released and all waiting inserts or updates are applied as well, even if you commit it with:

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

The moral of the story is that it is much easier to have two MySQL accounts: a) writing account which has INSERT, UPDATE and DELETE GRANT permissions, and b) reading account which has not.

回复收藏 0 原文