我不理解在哪些情况下会实际使用它。根据此处的文档,它说:
但是我尝试了,它允许所有可能的更新。我甚至尝试更改键。也许我没有理解文档中所指的键(从这个答案中我认为它是外键,并且因为它没有像文档中说的那样被阻止,我尝试更新所有内容,一切都没有被阻止)。我通过两个psql终端模拟了两个并发事务来进行实验。 事务1:与FOR SHARE类似,但锁定更弱:SELECT FOR UPDATE被阻止,但不是SELECT FOR NO KEY UPDATE。共享键锁定阻止其他事务执行DELETE或更改关键字值的任何UPDATE,但不会阻止其他UPDATE,也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或SELECT FOR KEY SHARE。
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”)? 这有什么实际用途?
\d table_base
吗? - Laurenz Albe