sql - select for update 如何适用于具有多行/结果的查询？

Question

鉴于此交易:

select * from table_a where field_a = 'A' for update;

假设这会给出多行/结果，数据库会立即锁定所有结果吗？或者它会一次锁定一行。

如果后者为真，是否意味着并发运行此查询会导致死锁？

那么需要增加一个order by来保持订单的一致性来解决这个问题吗？

Answer 1

documentation解释发生的事情如下:

FOR UPDATE

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.

您问题的直接答案是 Postgres 无法“立即”锁定所有行；它必须先找到它们。请记住，这是行级锁定而不是表级锁定。

文档包含此注释:

SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.

我将其解释为 Postgres 执行 SELECT 查询并在找到行时将它们标记为已锁定。当 Postgres 识别该行时，锁定(对于给定的行)开始。它一直持续到交易结束。

基于此，我认为使用SELECT FOR UPDATE可能会出现死锁情况。