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

Is it possible to lock a table such that the holder can read and write, and other sessions can only read?

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

The documentation seems to suggestion that a read lock allows everyone to only read, and a write lock allows only the holder to read and write and other sessions have no access. Seems like having the holder able to read and write and other sessions only able to read would be a pretty frequently needed behavior -- perhaps the most frequently needed behavior.

文档似乎暗示读锁允许每个人只读,而写锁只允许持有者读写,而其他会话无法访问。似乎让持有者能够读写,而其他只能读取的会话将是一种非常常见的行为 - 也许是最常需要的行为。

Maybe the performance hit in implementing this scenario would be too high?

也许在实施这种情况时遇到的性能太高了?

4 个解决方案

#1

Take a look at LOCK IN SHARE MODE.

看看LOCK IN SHARE MODE。

This will let you set non blocking read locks.

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

But remember, this can lead to deadlocks! Make sure you are okay with processes having out of date information.

但请记住,这可能导致死锁!确保您对过程信息过时没问题。

#2

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:

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

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

持有锁的会话可以读取表(但不能写它)。

and for the WRITE lock:

并为WRITE锁定:

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.

这就是使用任意引擎表几乎无法实现的效果,但它可以通过事务引擎实现,即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:

让我们考虑一下,什么意味着单个会话在表上保持一个持久的写锁定,而其他表可以在事务方面从表中读取数据。这意味着我们有一个开放的长生活事务(让它成为W事务),它锁定一个表进行修改,而其他事务(在其他会话中)可以读取已修改但尚未提交的数据。就隔离级别而言,这意味着我们应该将默认隔离级别设置为READ-UNCOMMITTED,这样我们就不必更改每个新会话的隔离级别:

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:

但是我们的事务W应该使用更强的隔离级别,否则我们不能对我们的表应用任何锁定。 READ-COMMITTED不够强大,但REPEATABLE-READ正是我们想要的。这是为了启动W事务,我们应该为当前会话设置事务级别:

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:

LOCK FOR UPDATE似乎可以满足我们的需求:

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

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

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).

现在我们只需要锁定行。我们最简单的方法是锁定主键。 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, '');

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:

但问题是,您应该在W中提交修改,并且一旦提交事务,锁就会被释放,并且所有等待的插入或更新也会被应用,即使您使用以下提交:

 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.

这个故事的寓意是拥有两个MySQL帐户要容易得多:a)编写具有INSERT,UPDATE和DELETE GRANT权限的帐户,以及b)读取没有的帐户。

#3

There is SELECT ... FOR UPDATE, which will lock the rows for other callers that do SELECT ... FOR UPDATE, but will not lock it for anyone doing just SELECT. UPDATEs will wait for the lock, as well.

有SELECT ... FOR UPDATE,它将锁定其他调用者的行,这些调用者执行SELECT ... FOR UPDATE,但不会为只做SELECT的任何人锁定它。 UPDATEs也将等待锁定。

This is useful when you want to fetch a value and then push an update back without anyone changing the value and you not noticing. Be careful, adding too much of those will get you into a deadlock.

当您想要获取值然后推送更新而没有任何人更改值而您没有注意到时,这非常有用。要小心,添加过多的会让你陷入僵局。

#4

-1

You may find that the InnoDB engine does what you need by default: writes do not block reads. You need to be careful with the transaction isolation level so that writes are available when you want them.

您可能会发现InnoDB引擎默认执行您所需的操作:写入不会阻止读取。您需要小心事务隔离级别,以便在需要时可以使用写入。

#1