我有一个分区表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;
据我所知,如果这些查询(INSERT 和 ATTACH)单独运行,那么表就会以正确的顺序锁定,这样一个查询就会阻塞另一个查询,但不会出现死锁。据推测,同一事务中的某些较早的语句与其中一个语句以其他顺序获取了锁,这会导致死锁。
您没有在创建和附加序列中显示 BEGIN,因此我假设其中每一个都在不同的自动提交事务中运行,因此 INSERT 事务是罪魁祸首。但我认为,如果不检查生成 INSERT 的代码以及它在事务早期可能执行的操作,就没有很好的方法来调查此问题。