是否可以在 mysql 中发出选择查询而不采取任何读锁?

发布于 2024-08-16 17:46:59 字数 140 浏览 9 评论 0原文

看来mysql选择内容(而不是例如计数)查询总是至少在myisam表上获取表读锁,并在innodb表上获取行读锁。有没有办法在 mysql 中发出选择内容查询(如果需要,我可以更改表类型)而不让它抓住任何锁?我不介意返回的数据是否不一致,因为我将使用它作为搜索索引。

It seems that mysql select content (as opposed to e.g. count) queries always take at least a table read lock on myisam tables and a row read lock on innodb tables. Is there a way to issue a select content query in mysql (I could change table type if that's required) without having it to grab any locks? I don't mind if the data returned is inconsistent since I will use it for a search index.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(2

梦行七里 2024-08-23 17:46:59

使用InnoDB,您可以通过将事务隔离级别设置为:未提交的内容

在此隔离级别下:

SELECT 语句在
非锁定时尚,但有可能
行的早期版本可能是
用过的。因此,使用这种隔离
水平,这样的读取并不一致。
这也称为“脏读”。
否则,此隔离级别有效
就像读已提交。

您可以从 MySQL 选项文件更改默认事务隔离级别,或者可以为单个会话启用和禁用它:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM table_name;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

进一步阅读:MySQL 文档:设置事务

With InnoDB you achieve this by setting the transaction isolation level to: READ UNCOMMITTED.

In this isolation level:

SELECT statements are performed in a
nonlocking fashion, but a possible
earlier version of a row might be
used. Thus, using this isolation
level, such reads are not consistent.
This is also called a “dirty read.”
Otherwise, this isolation level works
like READ COMMITTED.

You can either change the default transaction isolation level from the MySQL option file, or else it can be enabled and disabled for a single session:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM table_name;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Further Reading: MySQL Documentation: Set Transaction

神魇的王 2024-08-23 17:46:59

在没有LOCK TABLES的情况下,myisam应该相当于读未提交模式,但它实际上不支持任何事务类型...

innodb默认运行在“一致读”模式(处于“可重复读”隔离级别),这文档建议不会锁定:

如果事务隔离级别为
可重复读取(默认级别),
同一内的所有一致读取
事务读取快照
由第一个这样的读入建立
该交易

...

一致读取是默认模式
InnoDB 处理 SELECT
READ COMMITTED 中的语句和
可重复读隔离级别。一个
一致性读不设置任何锁
在它访问的表上,以及
因此其他会议可以自由
同时修改这些表
正在执行一致读取
表。

...

InnoDB 使用一致性读取
选择 INSERT INTO ... 等子句
选择、更新...(选择)和
创建表...选择不
指定 FOR UPDATE 或 LOCK IN SHARE
模式 如果
innodb_locks_unsafe_for_binlog 选项
被设置并且隔离级别
交易未设置为
可串行化。因此,没有设置锁
从所选表中读取的行。

http://dev.mysql.com/doc/ refman/5.0/en/innodb-consistent-read.html

in the absence of LOCK TABLES, myisam should be equivalent to read uncommitted mode, but it doesn't actually support any transaction types...

innodb runs in "consistent read" mode (at "repeatable read" isolation level) by default, which the docs suggest won't lock:

If the transaction isolation level is
REPEATABLE READ (the default level),
all consistent reads within the same
transaction read the snapshot
established by the first such read in
that transaction

...

Consistent read is the default mode in
which InnoDB processes SELECT
statements in READ COMMITTED and
REPEATABLE READ isolation levels. A
consistent read does not set any locks
on the tables it accesses, and
therefore other sessions are free to
modify those tables at the same time a
consistent read is being performed on
the table.

...

InnoDB uses a consistent read for
select in clauses like INSERT INTO ...
SELECT, UPDATE ... (SELECT), and
CREATE TABLE ... SELECT that do not
specify FOR UPDATE or LOCK IN SHARE
MODE if the
innodb_locks_unsafe_for_binlog option
is set and the isolation level of the
transaction is not set to
SERIALIZABLE. Thus, no locks are set
on rows read from the selected table.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文