Bảng liên kết không thể chứa thành viên trùng

Một dự án có thể có nhiều tài khoản, và một tài khoản có thể tham gia nhiều dự án. Tư cách thành viên, hay membership, là quan hệ giữa hai bên. Quan hệ đó cần định danh riêng, ngay cả khi định danh chỉ là cặp hai đầu mút.

Bài này dành cho lập trình viên đã biết SQL cơ bản và đang bổ sung quyền truy cập dự án. Chúng ta sẽ dựng bảng liên kết theo tenant, giữ nguyên vai trò hiện có khi yêu cầu thêm bị lặp, rồi tìm hiểu vì sao một truy vấn có thể không trả hàng nào trong cuộc đua đồng thời dù thành viên đã tồn tại. Hãy chạy ví dụ trong cơ sở dữ liệu PostgreSQL dùng thử. Chúng mô tả hành vi lưu trữ; chúng không xác thực người nhận lời mời hay cho phép người gọi tự cấp quyền.

Đưa tenant vào cả hai tham chiếu

Giả sử mã dự án và tài khoản chỉ có ý nghĩa trong một tenant, tức một tổ chức hoặc phạm vi khách hàng. Dự án 10 của tenant 1 khác dự án 10 của tenant 2. Một membership phải trỏ tới dự án và tài khoản thuộc cùng tenant. Chỉ thêm cột tenant vào membership nhưng vẫn tham chiếu các mã còn lại riêng lẻ sẽ không diễn tả được quy tắc ấy.

CREATE TABLE tenants (id bigint PRIMARY KEY);
CREATE TABLE projects (
  tenant_id bigint NOT NULL REFERENCES tenants(id),
  id bigint NOT NULL,
  title text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);
CREATE TABLE accounts (
  tenant_id bigint NOT NULL REFERENCES tenants(id),
  id bigint NOT NULL,
  display_name text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);
CREATE TABLE memberships (
  tenant_id bigint NOT NULL,
  project_id bigint NOT NULL,
  account_id bigint NOT NULL,
  role text NOT NULL CHECK (role IN ('viewer', 'editor')),
  joined_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (tenant_id, project_id, account_id),
  FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects(tenant_id, id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, account_id)
    REFERENCES accounts(tenant_id, id) ON DELETE CASCADE
);

Khóa chính của membership là (tenant_id, project_id, account_id). Đổi role không làm cho một hàng thứ hai của cùng quan hệ trở nên hợp lệ. Tài khoản vẫn có thể tham gia dự án khác, và dự án vẫn có thể nhận thêm tài khoản. Khóa ngăn cặp trùng trong một tenant, chứ không ngăn hai hướng hợp lệ của quan hệ nhiều-nhiều.

Mỗi khóa ngoại ghép đều dùng lại cột tenant_id duy nhất của membership. Nhờ vậy tenant tham gia cả hai phép kiểm tra đầu mút. Cả ba cột định danh đều không được null, nên tham chiếu thiếu một phần không thể đi qua theo hành vi thông thường của khóa ngoại có giá trị null. Tài liệu ràng buộc của PostgreSQL mô tả cách khóa chính và khóa ngoại dùng chung cột trong bảng liên kết.

Tạo dữ liệu cho hai tenant, cố ý lặp một số mã cục bộ:

INSERT INTO tenants VALUES (1), (2);
INSERT INTO projects VALUES
  (1, 10, 'Camera'), (1, 11, 'Sound'), (2, 10, 'Lighting');
INSERT INTO accounts VALUES
  (1, 73, 'Alex'), (1, 74, 'Sam'), (2, 73, 'Jo'), (2, 75, 'Lee');
INSERT INTO memberships (tenant_id, project_id, account_id, role)
VALUES (1, 10, 73, 'editor'), (1, 10, 74, 'viewer'),
       (2, 10, 73, 'viewer');
SELECT tenant_id, project_id, account_id, role
FROM memberships ORDER BY tenant_id, project_id, account_id;

Kết quả có ba membership. Tài khoản 73 của tenant 1 là editor trong dự án 10 của tenant đó; tài khoản 73 của tenant 2 là viewer trong dự án 10 riêng. Đây là những định danh khác nhau, không phải hai vai trò mâu thuẫn của một membership.

Bây giờ xét (1, 10, 75). Tài khoản 75 tồn tại, nhưng chỉ thuộc tenant 2. Thêm membership này thất bại với lỗi khóa ngoại 23503. Tương tự, (2, 11, 73) thất bại vì dự án 11 chỉ có trong tenant 1. Nếu chỉ kiểm tra từng mã số có tồn tại ở đâu đó trong cơ sở dữ liệu, cả hai tổ hợp sai đều có thể được chấp nhận.

Mở ảnh kích thước đầy đủ ↗

Mô hình này chủ động chọn tài khoản thuộc về từng tenant. Nếu sản phẩm có tài khoản toàn cục được tham gia nhiều tổ chức, hãy mô hình hóa định danh ấy riêng và xác định quan hệ tenant-tài khoản nào mà membership tham chiếu. Đừng gắn cột tenant vào định danh toàn cục chỉ để sao chép schema này. Dù chọn mô hình nào, toàn vẹn tham chiếu vẫn không quyết định người gọi đã xác thực nào được đọc hoặc sửa hàng.

Yêu cầu thêm không nên âm thầm thay vai trò

Lặp lại thao tác thêm tài khoản 73 với vai trò viewer:

INSERT INTO memberships (tenant_id, project_id, account_id, role)
VALUES (1, 10, 73, 'viewer')
ON CONFLICT ON CONSTRAINT memberships_pkey DO NOTHING
RETURNING role;

SELECT role FROM memberships
WHERE tenant_id = 1 AND project_id = 10 AND account_id = 73;

Lệnh chèn không trả hàng nào. Truy vấn sau đó trả editor. DO NOTHING giữ nguyên vai trò và thời điểm tham gia đã lưu; nó không hạ quyền vì yêu cầu lặp lại gửi vai trò thấp hơn. Việc nêu tên ràng buộc khóa chính xác định xung đột mà ví dụ xử lý. Dùng ON CONFLICT (tenant_id, project_id, account_id) để suy ra ràng buộc từ các cột cũng được hỗ trợ và có thể thuận tiện hơn khi thay index.

Tài liệu INSERT nêu rằng RETURNING chứa các hàng thực sự được chèn hoặc cập nhật. Hàng bị bỏ qua không trở thành một kết quả xác nhận quan hệ hiện có. Ứng dụng phải quyết định “đã là editor” có đáp ứng “thêm làm viewer” hay không, nên báo đã là thành viên, hay là xung đột với một thao tác có yêu cầu chặt hơn. Một lệnh không làm thay đổi dữ liệu không tự chọn được phản hồi đó.

DO UPDATE SET role = EXCLUDED.role mang ý nghĩa khác: thay vai trò hiện tại bằng vai trò đầu vào. Phép đối chiếu trong bộ thử xác nhận nó đổi editor này thành viewer. Khi muốn thay thế, hãy dùng thao tác đổi vai trò được kiểm tra quyền riêng. Tránh thêm UPDATE chỉ để ép cơ sở dữ liệu trả một hàng; UPDATE có thể kích hoạt hành vi khác mà yêu cầu thêm trùng không nên yêu cầu.

Nhánh xử lý xung đột không miễn mọi bước xác thực. Vai trò đầu vào owner vẫn vi phạm CHECK của schema, kể cả khi cặp đã tồn tại. Khóa null và tham chiếu đến đầu mút không có vẫn là lỗi. Xử lý đúng ý nghĩa của chúng thay vì biến mọi lần ghi thất bại thành “đã là thành viên”.

Đổi vai trò trên quan hệ hiện có

Một chuyển đổi tường minh có thể yêu cầu vai trò mà người gọi đang kỳ vọng:

BEGIN;
UPDATE memberships SET role = 'editor'
WHERE tenant_id = 1 AND project_id = 10 AND account_id = 74
  AND role = 'viewer'
RETURNING role;
SELECT account_id, role FROM memberships
WHERE tenant_id = 1 AND project_id = 10 ORDER BY account_id;
ROLLBACK;

Trong transaction này, tài khoản 74 đổi từ viewer sang editor, còn tài khoản 73 vẫn là editor. ROLLBACK cuối cùng đưa dữ liệu mẫu trở lại viewer. Điều kiện lọc ngăn lệnh đổi một hàng có vai trò hiện tại đã khác. Kết quả không có hàng cần được diễn giải: membership có thể không còn, đã đổi, hoặc nằm ngoài phạm vi được truyền vào. Nó không chứng minh người gọi có quyền.

Điều kiện vai trò kỳ vọng cũng không kiểm tra đầy đủ lịch sử. Vai trò có thể đã đổi sang giá trị khác rồi quay lại giữa hai lần quan sát. Nếu thao tác phải từ chối mọi chỉnh sửa xen giữa, hãy bổ sung version hoặc một hợp đồng đồng thời tường minh khác. Bảng đơn giản này lưu một vai trò hiện tại và thời điểm tham gia; nó không giữ lịch sử vai trò hay thực thi quy tắc chủ sở hữu cuối cùng. Thêm owner vào danh sách chuỗi được phép sẽ không bảo đảm “mỗi dự án luôn còn một owner”.

Chủ động chọn xóa quan hệ hay đầu mút

Bỏ một người khỏi dự án nên nhắm tới membership và giữ lại cả hai đầu mút. Với các quy tắc cascade đã chọn, xóa dự án có tác động rộng hơn:

BEGIN;
DELETE FROM memberships
WHERE tenant_id = 1 AND project_id = 10 AND account_id = 73;
SELECT count(*) FROM projects WHERE tenant_id = 1 AND id = 10;
SELECT count(*) FROM accounts WHERE tenant_id = 1 AND id = 73;
DELETE FROM projects WHERE tenant_id = 1 AND id = 10;
SELECT tenant_id, project_id, account_id FROM memberships
ORDER BY tenant_id, project_id, account_id;
ROLLBACK;

Sau lệnh xóa đầu tiên, cả hai phép đếm đầu mút đều bằng một. Lệnh xóa dự án tiếp theo loại bỏ membership còn lại của nó, chỉ để lại quan hệ (2, 10, 73) của tenant 2 trong transaction này. ROLLBACK cuối cùng khôi phục dữ liệu ban đầu. Tương tự, xóa tài khoản 73 của tenant 1 sẽ xóa các membership của tài khoản đó trên những dự án nó tham gia, nhưng không xóa hàng dự án hay tài khoản cùng mã cục bộ trong tenant 2.

Chiều cascade rất quan trọng: xóa cha sẽ xóa các quan hệ đang tham chiếu; xóa quan hệ không xóa cha. Chọn chính sách xóa khác nếu cần giữ lịch sử thành viên. Có thể ghép bảng membership hiện tại với các bản ghi lịch sử riêng, thay vì buộc một khóa chính đại diện cho mọi giai đoạn truy cập trong quá khứ.

Lặp một lệnh xóa có phạm vi rõ có thể không tác động hàng nào. Tài liệu DELETE coi đó là lệnh thành công nhưng không xóa gì, không phải lỗi cơ sở dữ liệu. API mô tả thao tác là đã hoàn tất hay quan hệ không tồn tại lại là một hợp đồng cần chọn rõ.

Phép chèn thấy xung đột nhưng snapshot chưa thấy hàng

Lệnh chèn và truy vấn đọc trước đó là hai câu SQL riêng. Ta dễ muốn gộp chúng thành một biểu thức “thêm hoặc lấy lại”. Truy vấn tiếp theo là thí nghiệm để bộc lộ giới hạn, không hứa luôn trả về một membership:

WITH inserted AS (
  INSERT INTO memberships (tenant_id, project_id, account_id, role)
  VALUES (1, 11, 74, 'viewer')
  ON CONFLICT ON CONSTRAINT memberships_pkey DO NOTHING
  RETURNING role
)
SELECT 'inserted' AS source, role FROM inserted
UNION ALL
SELECT 'existing', role FROM memberships
WHERE tenant_id = 1 AND project_id = 11 AND account_id = 74;

Khi không có đối thủ, lần chạy đầu trả inserted | viewer; lần sau trả existing | viewer. Chuỗi thử thông thường ấy bỏ sót một cuộc đua. Bắt đầu khi (1, 11, 74) chưa tồn tại. Kết nối A mở transaction và thêm nó với vai trò editor nhưng chưa commit. Kết nối B bắt đầu đúng truy vấn này rồi chờ lần ghi xung đột. Bây giờ cho A commit.

B bỏ qua chèn và giữ editor. Kết quả inserted rỗng. SELECT còn lại cũng không trả gì: snapshot, tức ảnh chụp dữ liệu mà câu lệnh được đọc, đã được lấy trước khi A commit. Phép kiểm tra xung đột phải tôn trọng lần chèn đã hoàn tất của A, dù hàng đó nằm ngoài snapshot dùng cho truy vấn đọc. Một membership tồn tại, nhưng câu lệnh này trả không hàng nào.

Kết quả này được mô tả trong quy tắc Read Committed của PostgreSQL. Tài liệu WITH có sửa dữ liệu cũng giải thích snapshot dùng chung và vì sao RETURNING truyền thay đổi giữa câu lệnh con sửa dữ liệu với phần sử dụng kết quả. Gộp các thao tác không làm mới góc nhìn của SELECT sau khi chờ.

Mở ảnh kích thước đầy đủ ↗

Trong bộ thử PostgreSQL 17.11 cục bộ, một SELECT mới sau câu lệnh của B trả editor. Ở lần chạy riêng cho A rollback, B thêm viewer và trả inserted | viewer. Bộ thử quan sát B thực sự đang chờ trước khi giải phóng A, rồi kiểm tra vai trò cuối cùng trong cả hai trường hợp. Nó không suy ra cuộc đua chỉ từ việc gửi hai yêu cầu sát nhau.

Ở Read Committed, câu lệnh tiếp theo có thể lấy snapshot mới hơn, nên cách dùng hai câu lệnh phía trước dễ diễn giải hơn. Truy vấn đọc ấy vẫn không bảo đảm vĩnh viễn: transaction khác có thể xóa membership trước khi nó chạy hoặc thay đổi hàng sau đó. Ứng dụng cần trạng thái ổn định hơn phải xác định khóa, cách thử lại hay giao thức transaction khác bao quanh thao tác. Các ví dụ không hứa cùng hành vi khôi phục ở Repeatable Read hoặc Serializable.

Không đưa trạng thái lời mời vào định danh membership

Lời mời có thể tồn tại trước tài khoản, hết hạn hoặc bị thu hồi. Membership hoạt động xác định một tài khoản và dự án đã biết. Đây là hai loại bản ghi có trạng thái khác nhau. Chấp nhận lời mời cần phối hợp việc tiêu thụ lời mời với tạo hoặc xác nhận membership, đồng thời kiểm tra định danh người nhận và vai trò được phép cấp.

Bài này chưa triển khai transaction chấp nhận lời mời đó. Khóa của cặp ngăn membership trùng; nó không chứng minh lời mời hợp lệ hay chỉ được tiêu thụ một lần. Một địa chỉ email không tự trở thành định danh tài khoản đã xác thực, và lần thử lại giữ nguyên hàng không giải quyết vòng đời lời mời.

Để luyện tập, chạy lệnh chèn trùng với từng vai trò được phép rồi kiểm tra vai trò và thời điểm tham gia đã lưu. Thử hai tổ hợp tenant sai, chỉ xóa quan hệ, rồi kiểm tra cascade của từng đầu mút trong transaction được rollback. Cuối cùng tái hiện cả hai kết quả của truy vấn đang chờ. Kết quả hữu ích là bất biến lưu trữ được ghi rõ và thao tác có giới hạn minh bạch, không chỉ là một câu SQL chạy thành công.