postgresql “选择更新”何时锁定和解锁？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15736640/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When does "select for update" lock and unlock?
提问by venus.w
Here is my Pseudo-code：
这是我的伪代码：
re = [select **result** from table where **condition**=key for update]
if[re satisfies]
{
delete from table where **condition** = key;
}
commit
I want to ask if the row with condition equals to "key" has already been deleted, Can the lock blocked by the "select for update" be unlocked automatically, which means if another process enters at this point and select for the same "key" it can not be blocked by this one ?
我想问一下条件等于“key”的行是否已经被删除了，被“select for update”阻塞的锁能不能自动解锁，也就是说如果此时有另一个进程进入，选择同一个“key” “这也挡不住？
回答by Craig Ringer
Locks are taken during (usually at or near the beginning of) a command's execution. Locks (except advisory locks) are released onlywhen a transaction commits or rolls back. There is no FOR UNLOCK, nor is there an UNLOCKcommand to reverse the effects of the table-level LOCKcommand. This is all explained in the concurrency control section of the PostgreSQL documentation.
锁定是在命令执行期间（通常在或接近开始时）获取的。只有在事务提交或回滚时才会释放锁（咨询锁除外）。没有，也没有可以逆转表级命令效果的命令。这在 PostgreSQL 文档的并发控制部分都有解释。FOR UNLOCKUNLOCKLOCK
You must commit or rollback your transaction to release locks.
您必须提交或回滚您的事务以释放锁。
Additionally, it doesn't really make sense to ask "has this row already been deleted by another concurrent transaction". It isn't really deleted until the transaction that deleted the row commits... and even then, it might've deleted and re-inserted the row or another concurrent transaction might've inserted the row again.
此外，询问“该行是否已被另一个并发事务删除”实际上没有意义。在删除行的事务提交之前，它并没有真正被删除......即使这样，它也可能已经删除并重新插入了该行，或者另一个并发事务可能再次插入了该行。
Are you building a task queue or message queue system by any chance, because if so, that problem is solved and you shouldn't be trying to reinvent that unusually complicated wheel. See PGQ, ActiveMQ, RabbitMQ, ZeroMQ, etc. (Future PostgreSQL versions may include FOR UPDATE SKIP LOCKEDas this is being tested, but hasn't been released at time of writing).
您是否正在构建任务队列或消息队列系统，因为如果是这样，问题就解决了，您不应该试图重新发明那个异常复杂的轮子。请参阅PGQ、ActiveMQ、RabbitMQ、ZeroMQ等（未来的 PostgreSQL 版本可能包括FOR UPDATE SKIP LOCKED正在测试中，但在撰写本文时尚未发布）。
I suggest that you post a new question with a more detailed description of the underlying problem you are trying to solve. You're assuming that the solution to your problem is "find out if the row has already been deleted" or "unlock the row". That probably isn't actually the solution. It's a bit like someone saying "where do I buy petrol" when their push-bike doesn't go so they assume it's out of fuel. Fuel isn't the problem, the problem is that push bikes don't take fuel and you have to pedal them.
我建议您发布一个新问题，更详细地描述您要解决的潜在问题。您假设问题的解决方案是“查明该行是否已被删除”或“解锁该行”。这可能不是真正的解决方案。这有点像有人说“我在哪里买汽油”，当他们的推车不走时，他们认为它没油了。燃油不是问题，问题在于推车不耗油，您必须踩踏板。
Explain the background. Explain what you're trying to achieve. Above all else, don't post pseudocode, post the actual code you are having problems with, preferably in a self-contained and runnable form.
解释一下背景。解释你想要达到的目标。最重要的是，不要发布伪代码，而是发布您遇到问题的实际代码，最好以独立且可运行的形式发布。
