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

Question

我想确保一个工作完全一个 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)
);

i使用读取的交易隔离水平，

有一系列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

• 我已经找出了更新的前两个以防止其他更改用户帐户类型中间的并发交易交易
• 我无法弄清楚用于第三查询的哪种"技巧"。第三查询应返回空列表，以确保工人尚未由任何经理拥有。
• 第三查询用于更新不起作用，因为我期望有一个空行。
• 由于第三次查询，我承担并发交易的风险

我该怎么做才能为一名经理执行一名工人？

Answer 1

您可能需要添加唯一的约束到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。