在高并发写表上防止死锁错误

Question

我有一个表，每分钟大约有1000多个插入。上面有一个触发器来更新另一个表上的列。

CREATE or replace FUNCTION clothing_price_update() RETURNS trigger AS $clothing_price_update$
BEGIN
INSERT INTO
clothes(clothing_id, last_price, sale_date)
VALUES(NEW.clothing_id, new.price, new."timestamp")
ON CONFLICT (clothing_id) DO UPDATE set last_price = NEW.price, sale_date = NEW."timestamp";
RETURN NEW;
END;
$clothing_price_update$ LANGUAGE plpgsql;

CREATE TRIGGER clothing_price_update_trigger BEFORE INSERT OR UPDATE ON sales
FOR EACH ROW EXECUTE PROCEDURE clothing_price_update();

然而，我随机得到死锁错误。这似乎很简单，没有其他触发因素在起作用。我错过什么了吗?

sales有不断插入的数据，但它不依赖于其他表，并且一旦添加数据就不会发生更新。

Answer 1

从一个角度来说，死锁的典型根本原因是并发事务中写入(锁定)行的顺序不一致。

想象两个完全并发的事务:

T1:

INSERT INTO sales(clothing_id, price, timestamp) VALUES
(1, 11, '2000-1-1')
, (2, 22, '2000-2-1');

T2:

INSERT INTO sales(clothing_id, price, timestamp) VALUES
(2, 23, '2000-2-1')
, (1, 12, '2000-1-1');

T1 locks the row with `clothing_id = 1` in `sales` and `clothes`.
T2 locks the row with `clothing_id = 2` in `sales` and `clothes`.
T1 waits for T2 to release locks for `clothing_id = 2`.
T2 waits for T1 to release locks for `clothing_id = 1`.
💣 Deadlock.

通常情况下，死锁仍然是极不可能的，因为时间窗口是如此狭窄，但随着更大的集合/更多的并发事务/更长的事务/更昂贵的写/添加周期的触发器(!)等，它变得更有可能。

在这种情况下，触发器本身并不是原因(除非它引入了乱序写!)，它只会增加死锁实际发生的可能性。

解决方法是按一致的排序顺序插入行在同一事务中。最重要的是在同一个命令内。然后下一个事务将排队等待，直到第一个事务完成(COMMIT或ROLLBACK)并释放其锁。手动:

防止死锁的最好方法通常是确保使用数据库的所有应用程序都获得锁顺序一致的多个对象

:

• 如何在PostgreSQL中模拟死锁?

长时间运行的事务通常会增加问题。看到:

• PostgreSQL表锁定

一边，则使用:

ON CONFLICT (clothing_id) DO UPDATE set last_price = NEW.price ... 

您可能需要在这里使用EXCLUDED而不是NEW:

ON CONFLICT (clothing_id) DO UPDATE set last_price = EXCLUDED.price ... 

微妙的区别:这样，可能的触发ON INSERT的效果被保留，而粘贴NEW再次覆盖它。相关:

• 如何在一个语句中使用单个值UPSERT多行?