MySQL MyISAM如何在不锁定表的情况下执行读取？

My question is a follow up to this answer. I want to find out how to perform a select statement without locking a table with MyISAM engine.

我的问题是对这个答案的跟进。我想了解如何在不使用MyISAM引擎锁定表的情况下执行select语句。

The answer states the following if you have InnoDB but not MyISAM. What is the equivalent for MyISAM engine?

如果你有InnoDB而不是MyISAM,答案陈述如下。 MyISAM引擎的等价物是什么?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;

2 个解决方案

#1

This is the default behaviour with MyISAM tables. If one actually wants to lock a MyISAM table, one must manually acquire a table-level lock. Transaction isolation level, START TRANSACTION, COMMIT, ROLLBACK have no effect on MyISAM tables behaviour since MyISAM does not support transactions.

这是MyISAM表的默认行为。如果实际上想要锁定MyISAM表,则必须手动获取表级锁。事务隔离级别,START TRANSACTION,COMMIT,ROLLBACK对MyISAM表行为没有影响,因为MyISAM不支持事务。

More about internal locking mechanisms

更多关于内部锁定机制

A READ lock is implicitely acquired before, and released after execution of a SELECT statement. Notice that several concurrent, simultaneous, SELECT statements could be running at the same time, because several sessions may hold a READ lock on the same table.

在执行SELECT语句之前隐式获取READ锁定并释放它。请注意,几个并发的,同时的SELECT语句可能同时运行,因为多个会话可能在同一个表上保持READ锁定。

Conversely, a WRITE lock is implicitely acquired before executing an INSERT or UPDATE or DELETE statement. This means that no read (let alone a concurrent write) can take place as long as a write is in progress^*.

相反,在执行INSERT或UPDATE或DELETE语句之前,会隐式获取WRITE锁。这意味着只要写入正在进行*就不会发生读取(更不用说并发写入)。

The above applies to MyISAM, MEMORY, and MERGE tables only.

以上内容仅适用于MyISAM,MEMORY和MERGE表。

You might want to read more about this here:

您可能想在此处阅读更多相关信息:

Internal locking methods

内部锁定方法

Read vs Write locks

读取与写入锁定

^*However, these locks are not always required thanks to this clever trick:

*但是,由于这个聪明的技巧,并不总是需要这些锁:

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks.

MyISAM存储引擎支持并发插入,以减少给定表的读取器和写入器之间的争用:如果MyISAM表在数据文件的中间没有空闲块,则总是在数据文件的末尾插入行。在这种情况下,您可以*地为没有锁的MyISAM表混合并发INSERT和SELECT语句。

#2

MyISAM does indeed use a read lock during SELECT. An INSERT at the end of the table can get around that.

在SELECT期间,MyISAM确实使用了读锁定。表格末尾的INSERT可以解决这个问题。

But try doing an UPDATE, DELETE, or ALTER TABLE while a long-running SELECT is in progress. Or vice-versa, reading from a table while a change to that table is running. It's first-come, first-serve, and the later thread blocks until the first thread is done.

但是在长时间运行的SELECT正在进行时尝试执行UPDATE,DELETE或ALTER TABLE。反之亦然,在对该表的更改运行时从表中读取。它是先到先得的,后来的线程阻塞直到第一个线程完成。

MyISAM doesn't have any support for transactions, so it must work this way. If a SELECT were reading rows from a table, and a concurrent thread changes some of those rows, you would get a race condition. The SELECT may read some of the rows before the change, and some of the rows after the change, resulting in a completely mixed-up view of the data.

MyISAM对事务没有任何支持,因此它必须以这种方式工作。如果SELECT从表中读取行,并且并发线程更改了某些行,则会出现争用情况。 SELECT可能会在更改之前读取某些行,并在更改之后读取某些行,从而导致数据的完全混合视图。

Anything you do with SET TRANSACTION ISOLATION LEVEL has no effect with MyISAM.

使用SET TRANSACTION ISOLATION LEVEL执行的任何操作都不会对MyISAM产生任何影响。

For these reasons, it's recommended to use InnoDB instead.

出于这些原因,建议使用InnoDB。

#1