postgresql - 在 postgres 中 FOR KEY SHARE 的实际用途是什么？

Question

我无法理解我将在哪里实际使用它。
根据文档 here它说

Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, and neither does it prevent SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.

但我试过了，它允许所有更新。我什至尝试更改 key 。可能是我没理解文档中所指的 key (我认为它是外键 from this answer 并且因为它没有像文档中所说的那样被阻止，所以我尝试更新所有内容并且一切正常而没有被阻止)。我通过让两个 psql 终端模拟两个并发事务来对此进行试验。

交易 1

db1=> begin;
BEGIN
db1=> SELECT * from table_base FOR key share;
 base_id | foreign_id | nonkey1 
---------+------------+---------
 112 |          2 | plaexpl
  21 |          2 | harish
 111 |          2 | harish
(3 rows)

db1=> select * from table_foreign ;
foreign_id |  value  
------------+---------
      2 | val2
     12 | val1new
     44 | newval3
(3 rows)

db1=> \d table_base 
                  Table "public.table_base"
 Column   |         Type          | Collation | Nullable | Default 
 ------------+-----------------------+-----------+----------+---------
 base_id    | integer               |           |          | 
 foreign_id | integer               |           |          | 
 nonkey1    | character varying(50) |           |          | 
 Foreign-key constraints:
  "table_base_foreign_id_fkey" FOREIGN KEY (foreign_id) REFERENCES 
 table_foreign(foreign_id) ON UPDATE CASCADE

交易2

db1=> begin;
BEGIN
db1=> UPDATE table_base set base_id = 221 where base_id=21;
UPDATE 1
db1=> UPDATE table_base set foreign_id = 12 where nonkey1='harish';
UPDATE 2
db1=> UPDATE table_base set nonkey1='newharish' where nonkey1='harish';
UPDATE 2
db1=> end;
COMMIT
db1=> SELECT * from table_base;
 base_id | foreign_id |  nonkey1  
---------+------------+-----------
     112 |          2 | plaexpl
     111 |         12 | newharish
     221 |         12 | newharish
(3 rows)

db1=> begin;
BEGIN
db1=> UPDATE table_foreign set foreign_id = 33 where value = 'val1new';
UPDATE 1
db1=> UPDATE table_foreign set value ='newvalfor33' where foreign_id = 33;
UPDATE 1
db1=> end;
COMMIT
db1=> SELECT * from table_foreign ;
 foreign_id |    value    
------------+-------------
          2 | val2
         44 | newval3
         33 | newvalfor33
(3 rows)

有关上述示例中表格的更多信息

如果它允许所有更新，那么'FOR KEY SHARE'和普通'SELECT'之间有什么区别(除了它阻止SELECT FOR UPDATE)。
这有什么实际用途？

Answer 1

FOR KEY SHARE在修改具有外键约束的表时最有用:被引用的行(在远程表上)将(自动)接收这样的锁，以便不能同时修改被引用的键。

您看不到任何效果，因为您的表不包含 PRIMARY KEY或 UNIQUE约束。