PostgreSQL和锁定 [英] PostgreSQL and locking
问题描述
希望我能提供一些比我更聪明的DBA，以帮助我找到所需的解决方案.
Hopefully some smarter DBAs than I can help me find a good solution for what I need to do.
为便于讨论，假设我有一个名为"work"的表，其中包含一些列，其中之一是代表来自给定客户端的该行工作的所有权的列.场景是我将连接2个客户端，并轮询一个表以完成工作，当显示一行(或一定数量的行)时，选择该行的第一个客户端也将对其进行更新以暗示所有权，即更新将删除那些返回给其他任何客户选择内容的行.我的问题是，在这种情况下，我可以使用哪种锁定方式来防止2个客户端同时命中该表，并且两个客户端都通过select返回相同的行?
For the sake of discussion, lets assume I have a table called 'work' with some number of columns, one of which is a column that represents ownership of that row of work from a given client. The scenario is that I'll have 2 clients connected and polling a table for work to be done, when a row (or some number of rows) shows up, the first client that selects the rows will also update them to imply ownership, that update will remove those rows from being returned to any other client's selects. My question is, in this scenario, what sort of locking can I use to prevent 2 clients from hitting the table at the same time and both of them being returned the same rows via the select?
推荐答案
带有RETURNING子句的UPDATE语句是实现此目的的方法.
The UPDATE statement with RETURNING clause is the way to do this.
UPDATE table
SET ownership = owner
RETURNING ( column list );
参考:
这篇关于PostgreSQL和锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
