MySQL：如何锁定表并启动事务？

Question

TL;DR - MySQL不允许您同时锁定表格和使用事务。 有没有什么办法可以解决这个问题？

我有一个MySQL表，用于缓存来自（慢速）外部系统的一些数据。 这些数据用于显示Web页面（用PHP编写）。 每隔一段时间，当缓存的数据被认为太旧时，其中一个Web连接应该触发更新缓存的数据。

我必须处理三个问题：

- 当我更新它时，其他客户端将尝试读取缓存数据 - 多个客户端可能会决定缓存数据过时并尝试同时更新它 - 做工作的PHP实例可能随时意外终止，并且数据不应该损坏

我可以使用事务解决第一个和最后一个问题，因此客户端将能够在提交事务之前读取旧数据，当他们立即看到新数据时，任何问题都会导致事务回滚。

我可以通过锁定表格来解决第二个问题，以便只有一个进程有机会执行更新。 到其他任何进程获得锁定的时候，他们会意识到他们已经被击败了，不需要更新任何内容。

这意味着我需要同时锁定表格和启动事务。根据MySQL手册，这是不可能的（链接1）。启动事务会释放锁，并且锁定表格会提交任何活动事务。是否有绕过此问题的方法，或者完全实现我的目标的其他方式？

- Malvineous

Answer 1

这意味着我需要同时锁定表并开始一个事务。

以下是实现的方法:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

如需更多信息，请参阅mysql文档

。

- Tomas Bilka

---

这看起来实际上可以工作，但只适用于InnoDB表（如果这是一个问题的话）。 - Malvineous

Answer 2

如果是我，我会使用MySQL中的advisory locking function实现更新缓存的互斥锁，并使用事务进行读隔离。例如：

begin_transaction(); // although reading a single row doesnt really require this
$cached=runquery("SELECT * FROM cache WHERE key=$id");
end_transaction();

if (is_expired($cached)) {
   $cached=refresh_data($cached, $id);
}
...

function refresh_data($cached, $id)
{
 $lockname=some_deterministic_transform($id);
 if (1==runquery("SELECT GET_LOCK('$lockname',0)") {
    $cached=fetch_source_data($id);
    begin_transaction();
    write_data($cached, $id);
    end_transaction();
    runquery("SELECT RELEASE_LOCK('$lockname')");
 }
 return $cached; 
}

（顺便提一句：如果您在使用持久连接时尝试此操作，可能会发生不良情况）

- symcbean

Answer 3

我建议通过完全删除争用来解决问题。

给缓存数据添加一个时间戳列。

当您需要更新缓存数据时：

- 仅使用当前时间戳将新缓存数据添加到表中。 - 删除早于或等于 24 小时的缓存数据。

当您需要提供缓存数据时：

- 按时间戳（DESC）排序并返回最新的缓存数据。

在任何给定时间，您的客户端都将检索不被任何其他进程删除的记录。此外，您不需要关心客户端获取不同写入（即具有不同时间戳）的缓存数据。

- Gianluca Ghettini

Answer 4

第二个问题可以完全不涉及数据库而解决。为缓存更新过程设置一个锁文件，以便其他客户端知道已经有人在处理它。这可能无法捕获每一个角落案例，但如果两个客户端同时更新缓存，这真的很重要吗？毕竟，他们正在事务中进行更新，因此缓存仍将保持一致。

您甚至可以通过在表中存储最后一次缓存更新时间来实现锁定。当客户端想要更新缓存时，让它锁定该表，检查最后更新时间，然后更新字段。

也就是说，实现自己的锁定机制以防止多个客户端更新缓存。事务会处理其余部分。

- Emil Vikström

---

很遗憾，锁定文件在这里行不通，因为我们有多个 Web 服务器运行本地站点文件的副本。 因此，一个 Web 服务器看不到另一个创建的锁定文件。 我正在将最后更新时间存储在表中，但是在缓存刷新完成之前无法更新该时间 - 否则，如果更新时间然后进程被终止（根据第3点），数据将没有被刷新，但是最后更新时间将错误地显示它已经刷新了。 锁定整个更新期间的表将破坏第1点，在刷新期间阻止其他客户端读取旧数据。 - Malvineous