我正在开发一个使用 PostgreSQL 数据库进行存储的 Web 应用程序。该应用程序中有许多彼此相关的逻辑实体hotel --> hotel_floor --> hotel_room
(整个问题中使用的酒店示例纯粹是假设的)。每个表都存储层次结构中所有父表的 ID 以及它自己的 ID,该 ID在其父表范围内是唯一的。DB 结构如下所示:
table hotel (
hotel_id int,
...
CONSTRAINT hotel_pk PRIMARY KEY (hotel_id)
)
table floor (
hotel_id int,
floor_id int,
...
CONSTRAINT floor_pk PRIMARY KEY (hotel_id, floor_id),
CONSTRAINT floor_hotel_fk FOREIGN KEY (hotel_id) REFERENCES hotel (hotel_id)
)
table room (
hotel_id int,
floor_id int,
room_id int,
...
CONSTRAINT room_pk PRIMARY KEY (hotel_id, floor_id, room_id),
CONSTRAINT room_hotel_fk FOREIGN KEY (hotel_id) REFERENCES hotel (hotel_id),
CONSTRAINT room_floor_fk FOREIGN KEY (hotel_id, floor_id) REFERENCES floor (hotel_id, floor_id),
)
存储在其中的记录的 ID 如下所示:
hotel
--------
hotel_id
--------
1
2
3
floor
-------------------
hotel_id | floor_id
-------------------
1 | 1
1 | 2
|
2 | 1
2 | 2
room
-----------------------------
hotel_id | floor_id | room_id
-----------------------------
1 | 1 | 1
1 | 1 | 2
1 | 1 | 3
1 | 2 | 1
1 | 2 | 2
1 | 2 | 3
1 | 2 | 4
显然,由于这样的数据库结构(我们团队继承的并且不容易重构),所有ID都必须手动生成。出于这个原因,我们在每个表上都有一个插入前触发器,这些触发器都归结为与此类似的内容:
CREATE OR REPLACE FUNCTION room_set_new_id() RETURNS trigger LANGUAGE plpgsql AS $
BEGIN
LOCK TABLE room IN SHARE ROW EXCLUSIVE MODE;
NEW.room_id = (
SELECT COALESCE(MAX(room_id), 0) + 1
FROM room
WHERE hotel_id = NEW.hotel_id AND floor_id = NEW.floor_id
);
RETURN NEW;
END
$;
CREATE TRIGGER room_set_new_id
BEFORE INSERT ON room
FOR EACH ROW EXECUTE PROCEDURE room_set_new_id();
问题就出在LOCK TABLE
声明上。直到最近还没有出现任何问题,但是当两个插入操作同时执行时,我们开始遇到重复键错误,因此我决定添加这些锁,希望能够解决该问题。不幸的是,这导致了一个新问题:同时插入现在会导致死锁。我对服务器代码进行了实验,发现问题完全在于 本身,INSERT
例如INSERT INTO room (hotel_id, floor_id) VALUES (1, 1)
同时发出两个或多个查询 - // 没有发生其他读/写操作hotel
floor
room
此时的表(实际上,对网络服务器的每个请求都来自登录的用户,并且用户表和酒店表之间存在用于身份验证目的的连接,但我不认为同时“检查是否当前用户与相关酒店相关”查询应该影响插入操作)。
我一直在尝试找出解决此问题的方法,但无济于事。当然,我可以通过捕获错误并重试查询执行来在 Web 服务器上添加死锁错误处理,但只有当确实没有办法在数据库级别解决此问题时(即,如果我的表锁定),我才愿意这样做策略是错误的,有一种不同的方法可以避免重复的 ID 生成,我想这样做。(请记住,我一直坚持这种数据库结构,并且需要花费大量时间和精力来重写所有数据库和 Web 服务器逻辑以使用正确的自动生成列。)
经过更多实验后,我发现了问题的根源 - 在触发器执行期间获取了锁。我不知道这里的原因是什么,但显然,这会导致数据库在这些同时事务期间执行的任何操作之间发生冲突。因此,执行
LOCK TABLE
beforeINSERT
可以完全解决问题。固定触发:
以及交易样本: