Postgresql 共享行独占锁 VS pg_advisory_lock

Question

我正在运行 Postgres 9.3 并有一个表标签，可通过 Python 的 psycopg2 模块访问。我有一个名为“标签”的表，它通过两种不同的方法更新/插入，称为“更新”和“插入”。我还有几个工作人员同时运行，每个工作人员都调用“更新”或“插入”。由于唯一性约束，我想在执行插入或更新之前直接锁定 'tags' 表，并在之后直接提交事务。

所以我的代码大致看起来像(用 psycopg2 的说法)

更新:

cur.execute(LOCK TABLE tags IN SHARE ROW EXCLUSIVE MODE)
cur.execute(UPDATE tags SET ...)
cur.execute(DELETE FROM tags ....)
cur.execute(INSERT INTO tags ...)
connection.commit()

插入:

cur.execute(LOCK TABLE tags IN SHARE ROW EXCLUSIVE MODE)
cur.execute(DELETE FROM tags ....)
cur.execute(INSERT INTO tags ...)
connection.commit()

我的模式看起来像

user_id varchar NOT NULL, 
tag varchar NOT NULL, 
time timestamptz, 
CONSTRAINT unique_tag_key PRIMARY KEY (user_id, tag)
CONSTRAINT seen_before_user FOREIGN_KEY (user_id)
    REFERENCES user_id_table (user_id) MATCH SIMPLE 
    ON UPDATE NO ACTION ON DELETE NO ACTION 

我遇到的问题是，当我运行并发工作程序时，我会在执行共享锁时遇到死锁。奇怪的是，如果我用类似

的调用替换 LOCK TABLE 调用

cur.execute("SELECT pg_advisory_lock(tag_hash)")

其中 tag_hash 是标签表名称“标签”上的散列，我没有收到此类错误。

为什么我在使用 SHARE ROW EXCLUSIVE 而不是 pg_advisory 锁时遇到错误？如果我可以保证标签表永远不会在这两种方法之外被修改，那么在这里使用 pg_advisory 锁有什么缺点吗？

Answer 1

我认为你的做法是错误的。

1. 你不应该在 Postgres 中锁定整个表，除非你正在它上面运行 DDL。

2. 您已经对相关字段进行了唯一约束

最好的办法是在 try/except 中运行事务。如果有异常 For The constraint violation - Handle it properly，或者 deadlock detected exception，只需重试即可。

一般来说，postgres 非常擅长处理锁而不需要手动锁控制，除非你正在做一些非常疯狂的事情。