我有一个分区表user_stay
,其中有许多插入和更新。
db=> \d user_stay;
Partitioned table "public.user_stay"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------------------------------------
id | bigint | | not null | nextval('user_stay_id_seq'::regclass)
last_update_date | date | | not null | CURRENT_DATE
custom_place_id | bigint | | |
Foreign-key constraints:
"user_stay_custom_place_id_fkey" FOREIGN KEY (custom_place_id) REFERENCES user_custom_place(id) ON DELETE CASCADE
分区是由last_update_date
. 当我尝试附加未来日期的新分区表时,出现错误。
例如,今天是 8/30,我尝试创建 9/2 的表:
CREATE TABLE user_stay_partition_2023_09_02
(LIKE user_stay INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE user_stay_partition_2023_09_02 ADD CONSTRAINT "2023_09_02"
CHECK ( last_update_date >= DATE '2023_09_02' AND
last_update_date < DATE '2023_09_03' );
ALTER TABLE user_stay ATTACH PARTITION user_stay_partition_2023_09_02
FOR VALUES FROM ('2023_09_02') to ('2023_09_03');
错误是:
[7396]:ERROR: deadlock detected at character 13
[7396]:DETAIL: Process 7396 waits for RowExclusiveLock on relation 594728 of database 18604; blocked by process 31245.
Process 31245 waits for ShareRowExclusiveLock on relation 594672 of database 18604; blocked by process 7396.
Process 7396: INSERT INTO user_stay (last_update_date, ...) VALUES ('2023-08-30'::date, ...) ON CONFLICT DO NOTHING RETURNING user_stay.id
Process 31245: ALTER TABLE user_stay ATTACH PARTITION user_stay_partition_2023_09_02
FOR VALUES FROM ('2023_09_02') to ('2023_09_03');
更新1
我认为原因是以下两条SQL:
- 插入 user_stay
- 更改表 user_stay 附加分区
user_stay
两者都需要&的锁user_custom_place
。如果SQL 1得到了first的锁user_stay
,SQL 2也得到了first的锁user_custom_place
,那么就是死锁,两条SQL都不会完成。
更新2
通过以相同的顺序手动锁定表来修复死锁,这样死锁就消失了:
BEGIN;
LOCK TABLE user_custom_place IN SHARE ROW EXCLUSIVE MODE;
ALTER TABLE user_stay ATTACH PARTITION user_stay_partition_2023_09_02
FOR VALUES FROM ('2023_09_02') to ('2023_09_03');
COMMIT;