使用UPDATE FOR和外键锁定postgres

My Postgres tables:

我的Postgres表:

performers

id
rank
group_id REFERENCES groups

group_id REFERENCES组

performances

id
pay
performer_id REFERENCES performers

performer_id参考表演者

groups

I often need to select all the performers from a group and update all their ranks. But this often leads to deadlocks since I'm doing an SELECT ... FOR UPDATE select and other threads are INSERTing at the same time.

我经常需要从一个组中选择所有表演者并更新他们的所有队伍。但这通常会导致死锁,因为我正在执行SELECT ... FOR UPDATE select和其他线程同时进行INSERTing。

Example of the error I see a lot (in Python SqlAlchemy):

我看到很多错误的例子(在Python SqlAlchemy中):

DBAPIError: (TransactionRollbackError) deadlock detected
DETAIL:  Process 83182 waits for ShareLock on transaction 14282922; blocked by process 83171.
Process 83171 waits for ShareLock on transaction 14282925; blocked by process 83182.
HINT:  See server log for query details.
 'SELECT performers.id AS performers_id, performers.rank AS performers_rank, performers.group_id AS performers_group_id \nFROM performers \nWHERE performers.group_id = %(group_id_1)s FOR UPDATE' {'group_id_1': 2}

I've found a few examples of this behavior around as well.

我也发现了一些这种行为的例子。

How can I fix this? Can I switch to a different transaction locking level? I'd rather not just abort and have to retry - I want the database to take care of this contention for me.

我怎样才能解决这个问题?我可以切换到不同的事务锁定级别吗?我宁愿不只是中止而且必须重试 - 我希望数据库能够为我解决这个问题。

There must be some way to fix this - what I want to do is quite simple.

必须有一些方法来解决这个问题 - 我想要做的很简单。

1 个解决方案

#1

You can avoid the deadlocks, if all concurrent write operations go about it in the same unique order. Add ORDER BY to your locking statement, and use the same sort order everywhere. Something like:

如果所有并发写入操作以相同的唯一顺序进行,则可以避免死锁。将ORDER BY添加到锁定语句,并在所有位置使用相同的排序顺序。就像是:

SELECT ... FROM performers WHERE ... ORDER BY performers.id FOR UPDATE

Where id would be the primary key (or any other stable, unambiguous combination of columns).

其中id将是主键(或任何其他稳定,明确的列组合)。

Also, if your operations involve multiple columns, do the locking in strictly the same sequence across tables.

此外,如果您的操作涉及多个列,请在表中严格按照相同的顺序进行锁定。

More details in this thread in pgsql-general list.
Or in the manual.

pgsql-general列表中此线程的更多细节。或者在手册中。

Keep triggers (and foreign key constraints) to the necessary minimum. Either way, first sort out the FOR UPDATE locks. That might just solve your problems. If not, consider a different transaction isolation level. Serializable should do the trick. But then you have to be prepared to retry transactions until they succeed.

将触发器(和外键约束)保持在必要的最小值。无论哪种方式,首先要排序FOR UPDATE锁。这可能只是解决你的问题。如果不是,请考虑不同的事务隔离级别。 Serializable应该可以解决问题。但是,你必须准备好重试交易,直到它们成功为止。

#1