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