如何在PostgreSQL中处理竞争条件？

Question

我在Postgresql上收到了一些错误，这些错误似乎与这个竞态条件有关。

我有一个用Twisted Python编写的进程/守护进程。最简单的描述方式是Web爬虫--它拉取一个页面，解析链接，并记录它所看到的内容。由于HTTP阻塞，Twisted运行多个“并发”进程，延迟到线程。

这是竞态条件。

当我遇到url缩短器时，会发生以下逻辑：

result= """SELECT * FROM shortened_link WHERE ( url_shortened = %(url)s ) LIMIT 1;"""
if result:
   pass
else:
   result= """INSERT INTO shortened_link ( url_shortened ..."

由于url_shortened上的唯一索引被违反，因此引发了一个令人惊讶的数字，或者说unique copg2.IntegrityError。

select/insert实际上一起运行。据我所知，这看起来像是2个缩短的链接排在另一个队列中。

Process A: Select, returns Null
Process B: Select, returns Null
Process A: Insert , success
Process B: Insert , integrity error

有没有人能推荐一些技巧/窍门来处理这个问题？我希望避免显式锁定，因为我知道这会带来一系列的问题。

Answer 1

只需一条命令即可完成所有操作：

result= """
INSERT INTO shortened_link ( url_shortened ...
SELECT %(url)s
where not exists (
    select 1
    from shortened_link
    WHERE url_shortened = %(url)s
);"""

只有在该链接不存在时才会插入。

Answer 2

确实没有一种解决方案可以避免需要能够处理唯一约束违反错误的可能性。如果您的框架不能做到这一点，那么我会将SQL包装在PL/pgSQL函数或过程that can中。

既然您可以处理错误，那么您最好不要测试唯一值的存在，而只是尝试插入，让EXCEPTION子句处理任何错误。

Answer 3

您要么需要某种类型的互斥锁，要么必须忍受由于竞争条件而产生的冗余。

如果您选择使用互斥锁-您不一定需要使用数据库级锁。您可以简单地锁定Twisted进程，以阻止其他线程处理类似的缩短url。

如果选择避免锁定，请删除url_shortened字段上的唯一约束。定期地，你可以将这些记录移动到一个“干净的”表中，该表包含每个缩短的url的唯一副本。