MyISAM：如何在运行 DELETE 的情况下进行选择而无需锁定等待？

发布于 2024-11-01 20:37:39 字数 359 浏览 1 评论 0原文

我有一个包含3000万条记录的表，使用MyISAM引擎。我每 15 分钟就会重复执行 DELETE 语句来清除表中的旧记录。

使用：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM mytable WHERE id=123;

当“DELETE FROM mytable...”正在运行时（需要 30 - 60 秒）。

SELECT 语句会在不加锁的情况下返回脏记录吗？或者是否会遵守表锁并等待 30 秒然后返回结果？有没有办法让 MyISAM 表在 DELETE 有表锁时返回 SELECT 查询？

谢谢。

原文

I have a table with 30 million records, using MyISAM engine. I have recurrent DELETE statements every 15 minutes to clean the table from old records.

Using:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM mytable WHERE id=123;

while a 'DELETE FROM mytable...' is running (which takes from 30 - 60 seconds).

Will the SELECT statement return dirty records without locking? Or will the table lock be respected and wait 30 seconds then return the results? Is there a way to get a MyISAM table to return SELECT queries while DELETE has a table lock?

Thanks.

分享到QQ

分享到微博

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

发布评论

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

放血 2024-11-08 20:37:39

DELETE 语句支持
以下修饰符：
如果指定 LOW_PRIORITY，则
服务器延迟执行 DELETE
直到没有其他客户端正在阅读
从表中。这仅影响
仅使用的存储引擎
表级锁定（例如MyISAM，
内存和合并）。

你可以使用

DELETE LOW_PRIORITY FROM mytable ......

The DELETE statement supports the
following modifiers:
If you specify LOW_PRIORITY, the
server delays execution of the DELETE
until no other clients are reading
from the table. This affects only
storage engines that use only
table-level locking (such as MyISAM,
MEMORY, and MERGE).

u can use