在PostgreSQL中锁定表

Question

我有一个名为'games'的表，其中包含一个名为'title'的列，该列是唯一的，数据库使用的是PostgreSQL 我有一个用户输入表单，允许他在'games'表中插入一个新的'game'。插入新游戏的函数会检查先前输入过具有相同'title'的'game'是否已存在，为此，我获取了具有相同游戏'title'的行数。

对于这个问题，我使用事务处理，在开始时插入函数使用BEGIN，获取行数，如果行数为0，则插入新行，完成后，它将COMMITS更改。

问题是，有可能用户同时提交具有相同title的2个游戏，因为我只获取行数以检查重复记录，并且每个事务都会与其他事务隔离 我考虑在获取行数时锁定表：

LOCK TABLE games IN ACCESS EXCLUSIVE MODE;
SELECT count(id) FROM games WHERE games.title = 'new_game_title' 

这将锁定表格以供阅读（这意味着其他事务必须等待，直到当前事务成功完成）。这将解决问题，这是我怀疑的。是否有更好的方法来避免重复具有相同标题的游戏？

- Akash

---

尝试更改事务的隔离级别。 - Tony

为什么不使用唯一约束来代替自己试图解决竞态条件呢？ - mu is too short

@muistooshort 我可以这样做，但是这会在用户端产生一个错误。 - Akash

那就自己捕获错误吧。您试图通过一堆不稳定的hack来避免一个简单的错误处理，而不是让数据库管理数据及其约束条件，这样会给自己带来麻烦。 - mu is too short

无论如何，您都必须捕获错误。除了约束违规之外，很多事情都可能导致INSERT失败：内存错误、连接问题、权限等等。也要捕获这种错误。 - Mike Sherrill 'Cat Recall'

Answer 1

在这种情况下，您不需要锁定表。

相反，您可以使用以下其中一种方法：

• 为必须真正独特的列定义UNIQUE索引。在这种情况下，第一个事务将成功，第二个事务将出错。
• 定义AFTER INSERT OR UPDATE OR DELETE触发器来检查您的条件。如果条件不成立，则应RAISE错误，这将终止冒犯的事务。

在所有这些情况下，您的客户端代码应准备好适当地处理可能返回的失败（例如操作失败）通过执行语句。

- mvp

---

+1，唯一约束是现实可行的方式。 （虽然我不太喜欢触发器解决方案）。 - user330315

@Akash：你不能计算也不能锁定尚未被其他进程提交的内容。正如其他人所提到的那样，唯一约束是唯一安全的解决方案。唯一约束是针对此问题而设计的，因此请使用它。 - Frank Heikens

我计划在使用FOR UPDATE的同时，使用UNIQUE索引来暂停其他事务，直到当前事务完成。 - Akash

@Akash：不要使用显式的锁定，而是使用唯一约束并捕获错误。这样，您的应用程序将更具可扩展性，并且在数据库服务器上使用的资源将更少。正如其他人指出的那样，您无论如何都必须实现错误处理。 - user330315

我计划在UNIQUE约束条件下使用FOR UPDATE。为什么？这会使你的应用程序变慢，而FOR UPDATE并没有任何作用。"很难捕捉到正确的错误"为什么？只需阅读（捕获）错误消息即可完成。非常简单易实现。 - Frank Heikens

Answer 2

使用最高事务隔离级别（Serializable），您可以实现类似于您实际问题的东西。但请注意，这可能会失败ERROR: could not serialize access due to concurrent update 我不完全同意约束方法。您应该有一个约束来保护数据完整性，但是依赖于约束会迫使您不仅要确定发生了哪个错误，而且还要确定哪个约束导致了错误。麻烦在于不仅要捕获错误，而且还要识别导致错误的原因并为失败提供可读的人类原因。根据您的应用程序使用的语言，这几乎是不可能的。例如：对于单独的约束，告诉用户“游戏标题[foo]已存在”，而不是“游戏必须有价格”。

有一个单语句替代您的两阶段方法的方法：

INSERT INTO games ( [column1], ... )
SELECT [value1], ...
WHERE NOT EXISTS ( SELECT x FROM games as g2 WHERE games.title = g2.title );

我希望明确一点...这不是替代唯一约束的选择（需要额外的索引数据）。你必须有一个来保护你的数据免受损坏。

- Philip Couling

---

我认为这是错误的；由于竞争条件，它将允许多次插入单个游戏标题，因为当内部SELECT找到零行时发生INSERT，因此不会进行锁定。 - Myles McDonnell

我理解拥有唯一约束将完全防止多次插入相同的标题，但提出的INSERT INTO..SELECT WHERE NOT EXISTS解决方案存在竞态条件;可能会插入重复的标题并且调用者不知道，所以我看不到它的价值在哪里？你说'它将可靠地无论如何都无法插入行，而不受约束的限制'，但这不是真的，因为两个并发插入相同的标题都可能成功。 - Myles McDonnell

可能是因为我写这篇文章时做了太多的 Oracle 编码。但是我注意到，在 8.3 和 8.4 之间的手册中，“可读提交隔离级别”的描述已经改变了。如果没有挖出过时的 postgres 版本，我无法确定建议是否总是错误的或只是过时的。我修改了我的答案以进行更正。 - Philip Couling

我仍然不确定它是否正确，但你可能比我更了解这个主题。我发现仅仅通过查看SQL而不是像你所指出的参考文档来推理这些事情非常困难，但我的理解是，因为内部选择实际上没有选择任何内容，所以无论隔离级别如何都不会采取锁定，因此在并发场景中这是不安全的。我99%确定在PG 9.6.3上是这种情况，因为我今天正在该版本上处理这个问题。 - Myles McDonnell

没有一种解决方案是永远不会出错并且只需等待的。我对Postgres有点失望。其他DBMS（Oracle和Mysql）将原子地执行上述SQL，强制其他查询等待。其他DBMS存在死锁的风险，所以我想这是选择你的毒药的情况。 - Philip Couling