postgresql 使用外键约束更新行时锁定

3bygqnnd  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(182)

我试着执行相同的UPDATE查询两次,如下所示。
第一次,事务没有锁,但我可以在第二次查询后看到行锁。
架构:

test=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 i      | integer |           | not null | 
 j      | integer |           |          | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (i)
Referenced by:
    TABLE "t2" CONSTRAINT "t2_j_fkey" FOREIGN KEY (j) REFERENCES t1(i)

test=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 i      | integer |           | not null | 
 j      | integer |           |          | 
 k      | integer |           |          | 
Indexes:
    "t2_pkey" PRIMARY KEY, btree (i)
Foreign-key constraints:
    "t2_j_fkey" FOREIGN KEY (j) REFERENCES t1(i)

字符串
现有数据:

test=# SELECT * FROM t1 ORDER BY i;
 i | j 
---+---
 1 | 1
 2 | 2
(2 rows)

test=# SELECT * FROM t2 ORDER BY i;
 i | j | k 
---+---+---
 3 | 1 |  
 4 | 2 |  
(2 rows)


UPDATE查询和行锁状态:

test=# BEGIN;
BEGIN
test=# UPDATE t2 SET k = 123 WHERE i = 3;
UPDATE 1
test=# SELECT * FROM t1 AS t, pgrowlocks('t1') AS p WHERE p.locked_row = t.ctid;
 i | j | locked_row | locker | multi | xids | modes | pids 
---+---+------------+--------+-------+------+-------+------
(0 rows)

test=# UPDATE t2 SET k = 123 WHERE i = 3;
UPDATE 1
test=# SELECT * FROM t1 AS t, pgrowlocks('t1') AS p WHERE p.locked_row = t.ctid;
 i | j | locked_row | locker | multi |   xids   |       modes       | pids 
---+---+------------+--------+-------+----------+-------------------+------
 1 | 1 | (0,1)      | 107239 | f     | {107239} | {"For Key Share"} | {76}
(1 row)

test=#


为什么postgres只在第二次尝试获取行锁?
顺便说一下,更新列t2.j的查询会立即在t1行上创建新锁(ForKeyShare)。这种行为是有意义的,因为t2.j有外键约束引用t1.i。但上面的查询似乎没有。
有谁能解释一下这个锁吗?
PostgreSQL版本:9.6.3

ztyzrc3y

ztyzrc3y1#

好我知道了
https://engineering.nordeus.com/postgres-locking-revealed/
这是存在于Postgres中的优化。如果锁定管理器可以从第一个查询中发现外键没有改变(在更新查询中没有提到或设置为相同的值),它将不会锁定父表。但是在第二个查询中,它将按照文档中描述的那样运行(它将在ROW SHARE锁定模式下锁定父表,在FOR SHARE模式下锁定引用行)
看起来MySQL在外键锁方面更明智,因为同样的UPDATE查询不会在MySQL上产生这样的锁。

相关问题