postgresql - 什么合适的锁定技术可以防止插入数据？ [PostgreSQL]

Question

我想确保一个 worker 正好有一个 manager

CREATE TABLE IF NOT EXISTS user_relationships (
  object_id  SERIAL PRIMARY KEY               NOT NULL UNIQUE,
  manager_id INT REFERENCES users (object_id) NOT NULL,
  worker_id  INT REFERENCES users (object_id) NOT NULL,
  CHECK (manager_id != worker_id),
  UNIQUE (manager_id, worker_id)
);

我有一系列使用 Read Committed 事务隔离级别的 SQL 语句，

BEGIN
SELECT * FROM users WHERE id=manager_id AND acc_type="manager" FOR UPDATE;
SELECT * FROM users WHERE id=worker_id AND acc_type="worker" FOR UPDATE;
SELECT * FROM relationships WHERE id=worker;
INSERT INTO relationships (m, w) VALUES (manager_id, worker_id)
COMMIT

• 我已经想出前两个FOR UPDATE来防止其他更改用户帐户类型 mid 的并发事务交易
• 我想不出第三个查询要用什么样的“技巧”。第三个查询应返回空列表以确保该 worker 尚未被任何经理拥有。
• 第三个查询 FOR UPDATE 不起作用，因为我期待一个空行。
• 由于第三个查询，我冒着并发事务向不同经理添加重复工作人员的风险。

我可以做些什么来强制一名 worker 服从一名经理？

Answer 1

可能您需要将 UNIQUE 添加到 worker_id 的约束中:

CREATE TABLE IF NOT EXISTS user_relationships (
  object_id  SERIAL PRIMARY KEY               NOT NULL UNIQUE,
  manager_id INT REFERENCES users (object_id) NOT NULL,
  worker_id  INT REFERENCES users (object_id) UNIQUE NOT NULL,
  CHECK (manager_id != worker_id)
);

但最好添加字段 manager_id INT REFERENCES users (object_id) NOT NULL到表 users并且不要使用 user_relationships .