MySQL "LOCK TABLES" 超时？

Question

mysql LOCK TABLES语句的超时时间是多少？

我无法在任何地方找到答案。

我尝试在my.cnf中设置变量innodb_lock_wait_timeout，但似乎它与另一种（行级）锁定有关，而不是表锁定。

简单来说，它对LOCK TABLES没有影响。

我想为死锁情况设置一些较低的超时值，因为如果某些操作将锁定表并且出现问题，它将挂起整个网站！

例如，在您的网站上完成购买就很愚蠢。

- luky

---

似乎 LOCK TABLES 没有任何超时时间，查询将一直挂起，直到锁被释放。 - maalls

是的，我也害怕这个。这很愚蠢...至于逻辑，表锁定正好符合我的需求，但我担心死锁，所以我将不得不放弃这个解决方案。相反，我将使用带有唯一列（发票号码）的INSERT IGNORE，在LOCK中生成它，如果失败（例如当两个并发事务在同一时刻发生时），我将尝试再次生成发票号码。（它应该可以工作，但唯一的问题是，如果第一个事务回滚，发票号码将会有漏洞。） - luky

无论是提交还是回滚，在锁定表的情况下，第二个并发事务都必须等待第一个事务完成，因此不会发生这种情况。不管怎样，感谢您的回复。祝好。 - luky

Answer 1

我的解决方案是创建一个专门的锁定表，并只锁定该表中的一行。这样做的好处是只锁定那些明确想要被锁定的进程。即使更新进程在某个时刻接触到表，应用程序的其他部分仍然可以继续访问表。

设置

CREATE TABLE `mutex` (
    EMPTY ENUM('') NOT NULL,
    PRIMARY KEY (EMPTY)
);

使用方法

set innodb_lock_wait_timeout = 1;
start transaction;
insert into `mutex` values();

[... do the real work here ... or somewhere else ... even a different machine ...]

delete from `mutex`;
commit;

- Alex R

Answer 2

正确的答案是 lock_wait_timeout 系统变量。

根据文档：

此变量指定获取元数据锁的尝试超时时间（以秒为单位）。允许的值范围从1到31536000（1年）。默认值为31536000。

此超时适用于使用元数据锁的所有语句。这包括对表、视图、存储过程和存储函数进行的 DML 和 DDL 操作，以及 LOCK TABLES、FLUSH TABLES WITH READ LOCK 和 HANDLER 语句。

- Rishabh

Answer 3

为什么要使用LOCK TABLES？

如果您正在使用可能需要用到LOCK TABLES的MyISAM，则应转换为InnoDB。

如果您正在使用InnoDB，则绝不能使用LOCK TABLES。相反，依赖于innodb_lock_wait_timeout（默认值过高为50秒），并应检查错误。

InnoDB死锁会被捕获并立即导致错误。某些非死锁情况可能会等待innodb_lock_wait_timeout。

编辑

由于事务看起来如下：

BEGIN;
SELECT ...;
compute some stuff
UPDATE ... (using that stuff);
COMMIT;

你需要在 SELECT 语句的末尾添加 FOR UPDATE。

- Rick James

---

我曾经使用LOCK TABLES和innoDB事务，但这不是原因，原因是我需要让其他（理论上的）并发事务（或选择一个特定表）等待，直到第一个事务完成。除了使用LOCK TABLES之外，没有其他方法可以阻止对某个表的访问。想象一下，我有一个Invoices表，并且需要在插入此表中的行之前生成自定义唯一ID。因此，我需要在该表上进行选择，例如where year = 2005，它将返回一些ID，然后我将增加它并插入具有唯一发票ID的新行，但是，在理论上，另一个事务可能会在我查询和插入之间插入数据，从而导致重复的ID。因此，我必须使用LOCK TABLES来确保我的操作是原子的。 - luky

可能会有两个并发线程同时执行选择（year = 2005）以构建“唯一”的发票ID或编号。因此，如果表被锁定，则没有其他并发线程可以使用该表。这就是为什么要使用锁定表的原因。但最终，我不使用锁定表，而是使用插入操作，如果插入失败（因为发票号是唯一列，并且因为理论上的并发线程），我将重新生成发票号。但正如我所说，这种方法的缺点是，如果第一个线程中的事务回滚，编号将出现漏洞。 - luky

仍然不需要使用 LOCK TABLES。一个线程插入一行并调用 last_insert_id()，该函数是特定于线程的，以获取唯一的 AUTO_INCREMENT id。其他线程也是如此；它们将获得不同的id。或者...如果您没有使用 AUTO_INCREMENT，请提供详细信息，以便我可以解释为什么它也不是问题。在任何构成“事务”的SQL语句组合周围，您都需要使用 BEGIN ... COMMIT。 - Rick James

请展示用于执行此操作的SQL语句集。我们将建议如何添加“BEGIN”和“COMMIT”以使其“安全”，并避免表锁定。 - Rick James

嗨Rick，抱歉回复晚了。我刚试了一下。很奇怪，因为我以前试过，但似乎没有成功，但现在我看到它可能有效，就像你说的那样。这意味着它做到了它应该做的事情。谢谢。 - luky

Answer 4

我认为您需要的是table_lock_timout变量，它在MySQL 5.0.10中引入，但随后在5.5中被删除。不幸的是，发布说明没有指定替代方案，我猜想一般的态度是转而使用InnoDB事务，就像@Rick James在他的答案中所述。

我认为删除该变量是无益的。其他人可能认为这是 XY问题的一个例子，我们试图通过更改锁定表的超时期来修复死锁的症状，而实际上我们应该通过转而使用事务来解决根本原因。 我认为仍然有一些情况，表锁比使用事务更适合应用程序，并且可能更容易理解，即使它们的性能更差。 使用LOCK TABLES的好处在于，您可以在继续之前声明查询所依赖的表。在事务中，锁定是在最后一刻抓取的，如果无法获取并超时，则需要检查此失败并回滚，然后再次尝试所有操作。最简单的方法是在锁定表查询上设置1秒超时（最小值），并重试获取锁定直到成功，然后在解锁表之前继续执行查询。这种逻辑不会有死锁的风险。

我认为开发人员的态度可以通过以下摘录从文档中概括出来:

...避免使用LOCK TABLES语句，因为它不提供任何额外的保护措施，反而会减少并发性。

- Programster

Answer 5

我认为你想说的是默认超时时间; 根据MySQL文档，它是50秒

innodb_lock_wait_timeout 默认值为50 秒，在InnoDB事务放弃等待行锁之前等待的秒数。默认值为50秒。

- Rahul

---

我认为这是针对行锁而不是表锁的。 - maalls

不好意思，我的意思是如何设置它。就像我说的，我尝试更改你所说的值，但似乎没有效果。这可能与另一个锁定（例如SELECT ... FOR UPDATE）有关，但那并不能满足我的需求。我需要让并发事务等待，直到另一个事务完成。但还是谢谢你。 - luky