Upsert với ON CONFLICT: "chưa có thì thêm, có rồi thì sửa" mà không lỗi 23505 Trung cấp

Vấn đề

Trang sản phẩm ghi “user 7 đã xem sản phẩm 42” vào bảng product_views: mỗi cặp (user_id, product_id) một row, kèm view_count. Code ban đầu: SELECT; chưa có thì INSERT, có thì UPDATE bằng giá trị đọc + 1.

Người dùng mở hai tab: hai request cùng thấy “chưa có”, request thứ hai nhận 23505 unique_violation. Khi row đã có, cả hai đọc view_count = 1 rồi cùng ghi 2: mất một lượt đếm.

Câu hỏi: làm sao để một câu lệnh tự chọn INSERT hay UPDATE mà không lỗi và không mất cập nhật khi chạy đồng thời?

Chọn gì? (tóm tắt trước)

Tình huống	Chọn	Điều kiện
Chưa có thì thêm, có rồi bỏ qua	ON CONFLICT (cols) DO NOTHING	Có unique index trên cols
Thêm hoặc cộng dồn / ghi đè	ON CONFLICT (cols) DO UPDATE SET ...	Batch không trùng key
Chỉ ghi khi dữ liệu đổi	DO UPDATE ... WHERE t.col IS DISTINCT FROM EXCLUDED.col	Row trùng vẫn bị khoá
Cần id dù row mới hay cũ	CTE + SELECT có retry, hoặc DO UPDATE “no-op”	Xem mục RETURNING
Đồng bộ cả bảng từ nguồn, có xoá	MERGE	Không ghi đồng thời cùng key, hoặc retry 23505

Khái niệm cốt lõi

Upsert (“UPDATE hoặc INSERT”) trong PostgreSQL là INSERT ... ON CONFLICT.

Thành phần	Ý nghĩa
Arbiter index	Unique index hoặc constraint NOT DEFERRABLE dùng để phát hiện conflict
ON CONFLICT (user_id, product_id)	Suy luận arbiter: mọi unique index có đúng các cột này
ON CONFLICT ON CONSTRAINT name	Chỉ định theo tên; docs ưu tiên suy luận
ON CONFLICT (cols) WHERE pred	Suy luận partial unique index; pred phải thoả predicate của index
EXCLUDED	Row đề xuất chèn, đã qua trigger BEFORE INSERT
DO NOTHING / DO UPDATE	Bỏ qua row trùng / cập nhật row đang có; DO UPDATE bắt buộc có conflict target

Cách hoạt động

Vì sao SELECT rồi INSERT race, kể cả trong transaction Read Committed? SELECT không khoá “chỗ trống” nơi row sẽ xuất hiện; transaction khác vẫn chèn được key đó.

SELECT rồi INSERT: cả hai thấy 0 row, B chờ A rồi nhận 23505

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/6

Request A tìm row (user 7, sản phẩm 42): chưa có. SELECT không giữ lock nào.

1. A và B đều đọc 0 row; không ai giữ lock.
2. A INSERT: entry trong unique index thuộc transaction chưa commit.
3. B INSERT cùng key: chờ A (demo đo được wait_event = transactionid).
4. A commit, B nhận 23505, transaction B bị huỷ.

Mức cao hơn không sửa được: chạy thử, Repeatable Read vẫn nhận 23505; Serializable đổi thành 40001 (B đã đọc key trước khi chèn), retry được nhưng vẫn là một lần thất bại. Với ON CONFLICT DO UPDATE:

ON CONFLICT DO UPDATE: B chờ A commit rồi cập nhật row của A, không lỗi

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/5

A chạy upsert: chưa có row nên INSERT, view_count = 1, chưa commit.

Docs bảo đảm: ở Read Committed, mỗi row đề xuất hoặc được INSERT hoặc được UPDATE, trừ lỗi không liên quan, kể cả khi row trùng chưa hiển thị với snapshot.

SET view_count = pv.view_count + 1 đọc giá trị hiện tại của row sau khi khoá, nên không mất lượt đếm.

Chi tiết vận hành

Đang tải diagram…

• Chỉ Read Committed có bảo đảm trên. Ở Repeatable Read/Serializable, nếu row trùng thuộc transaction ngoài snapshot, cả DO UPDATE lẫn DO NOTHING ném 40001 (demo bước 10). Phải retry cả transaction.
• Sequence nhảy số: nextval (kể cả identity) chạy trước khi phát hiện conflict và không thu hồi. Demo: ba lần conflict, row kế tiếp nhận id = 5.
• DO UPDATE ... WHERE: điều kiện được đánh giá sau cùng; row không thoả vẫn bị khoá đến hết transaction.
• Batch trùng key: VALUES (7, 50), (7, 50) với DO UPDATE ném 21000 “cannot affect row a second time”; DO NOTHING chỉ chèn 1 row. Gộp bằng GROUP BY trước.
• Partial index: thiếu WHERE khớp predicate thì nhận 42P10 “there is no unique or exclusion constraint matching the ON CONFLICT specification”.
• Trong lúc CREATE INDEX CONCURRENTLY chạy trên unique index, ON CONFLICT có thể ném unique violation.

RETURNING khi conflict

Với DO NOTHING, row bị bỏ qua không có trong RETURNING (demo: rowCount=0). Cách lấy id không ghi thêm:

WITH ins AS (
  INSERT INTO product_views (user_id, product_id) VALUES (7, 42)
  ON CONFLICT (user_id, product_id) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM product_views
WHERE user_id = 7 AND product_id = 42 AND NOT EXISTS (SELECT 1 FROM ins);

Lỗ hổng: nếu row trùng do transaction khác commit trong lúc câu lệnh chạy, nhánh SELECT dùng cùng snapshot nên không thấy. Demo đo được 0 row; phải retry.

Cách còn lại là DO UPDATE SET user_id = EXCLUDED.user_id RETURNING id: luôn có id, nhưng mỗi lần gọi là một UPDATE thật. Demo đo ctid (0,2) → (0,4), xmin đổi, trigger AFTER UPDATE chạy 1 lần.

Ví dụ thực tế

Đồng bộ giỏ hàng: mỗi user một giỏ active

Bảng carts có partial unique index (user_id) WHERE status = 'active'. App mobile gửi toàn bộ giỏ mỗi lần sửa, có khi hai request cùng lúc. Upsert nêu đúng predicate và chỉ ghi khi nội dung đổi:

INSERT INTO carts (user_id, items) VALUES (7, '[{"sku":"A","qty":2}]')
ON CONFLICT (user_id) WHERE status = 'active'
DO UPDATE SET items = EXCLUDED.items
WHERE carts.items IS DISTINCT FROM EXCLUDED.items
RETURNING id;

Gửi lại cùng nội dung thì RETURNING rỗng (demo: rowCount=0): “không đổi”, không phải “không có giỏ”. Chặn bản cũ đến trễ ghi đè bản mới cần thêm cột version.

Demo dùng hai connection pg:

npx --prefix examples ts-node examples/src/upsert-on-conflict/demo.ts

Output trên PostgreSQL 17 (rút gọn; ctid, xmin, id đổi mỗi lần chạy)

--- 1. SELECT rồi INSERT (Read Committed)
B đang chờ: { wait_event_type: 'Lock', wait_event: 'transactionid' }
B INSERT lỗi: 23505 duplicate key value violates unique constraint "product_views_user_id_product_id_key"
--- 2. SELECT view_count rồi UPDATE = giá trị đọc + 1
A đọc 1, B đọc 1; sau hai lượt xem view_count = 2
--- 4. DO NOTHING + RETURNING
CTE + UNION ALL khi A commit giữa chừng: 0 row
--- 6. Identity bị tiêu tốn khi conflict
id=1 product=42, id=5 product=43
--- 9. MERGE khi có concurrent INSERT
B MERGE lỗi: 23505 duplicate key value ...
--- 10. ON CONFLICT ở Repeatable Read
B upsert lỗi: 40001 could not serialize access due to concurrent update

MERGE không thay được ON CONFLICT khi ghi đồng thời

MERGE có từ PostgreSQL 15. Bản 17 thêm RETURNING với merge_action() và WHEN NOT MATCHED BY SOURCE (xử lý row có ở bảng đích nhưng không có ở nguồn).

Docs Read Committed ghi rõ: MERGE có cả INSERT và UPDATE không bảo đảm một trong hai xảy ra. Nếu nhánh INSERT gặp row do transaction khác chèn đồng thời, MERGE ném unique violation, không quay lại WHEN MATCHED.

Demo bước 9: B nhận 23505, chạy lại thì merge_action = UPDATE. Dùng MERGE cho đồng bộ hàng loạt từ bảng staging; dùng ON CONFLICT cho ghi từ request song song.

Trade-offs

Ưu điểm

• Bảo đảm INSERT hoặc UPDATE ở Read Committed, không 23505 trên arbiter.

• Cộng dồn trên row hiện tại, tránh lost update; một round-trip.

< slot="cons">

• Cần unique index đúng cột và predicate.

• Sequence tiêu tốn mỗi lần conflict; key nóng sinh nhiều row version và tranh lock.

• RETURNING không trả row bị bỏ qua; không xử lý xoá như MERGE.

Nhược điểm

Checklist production

1. Mỗi upsert có unique index tương ứng; conflict target viết bằng tên cột, kèm WHERE nếu là partial index.
2. Cộng dồn bằng SET col = t.col + EXCLUDED.col, không đọc ở app rồi ghi lại.
3. Thêm WHERE t.col IS DISTINCT FROM EXCLUDED.col cho dữ liệu đồng bộ lặp lại.
4. Batch: gộp key trùng trước, sắp xếp theo key để giảm deadlock giữa các batch chồng nhau.
5. Mẫu CTE + UNION ALL phải retry khi trả 0 row.
6. Chạy upsert ở Read Committed; ở mức cao hơn phải retry 40001.
7. Không dựa vào sequence liên tục; trigger UPDATE phải chịu được việc chạy khi giá trị không đổi.

Sai lầm thường gặp

Lỗi phổ biến nhất là tin transaction chống được race.

Kiểm tra trước rồi mới chèn trong transaction

✕ Hay gặp

BEGIN;
SELECT id FROM product_views WHERE user_id = 7 AND product_id = 42;  -- 0 row
INSERT INTO product_views (user_id, product_id) VALUES (7, 42);       -- 23505 nếu bị chèn trước
COMMIT;

✓ Nên viết

INSERT INTO product_views AS pv (user_id, product_id) VALUES (7, 42)
ON CONFLICT (user_id, product_id)
DO UPDATE SET view_count = pv.view_count + 1, last_viewed_at = EXCLUDED.last_viewed_at;

Vì sao

Transaction không khoá key chưa tồn tại; chỉ unique index tuần tự hoá việc chèn cùng key. Sau 23505, transaction hỏng đến khi ROLLBACK (xem savepoint ở Transaction trong code).

Lỗi thứ hai là coi bản “no-op” là miễn phí:

DO UPDATE no-op chỉ để lấy id

SET col = EXCLUDED.col vẫn là UPDATE đầy đủ: khoá row, tạo row version mới, ghi WAL, chạy trigger. Trên key bị gọi liên tục, chi phí tích lại thành bloat và tranh lock. Dùng WHERE ... IS DISTINCT FROM hoặc mẫu CTE có retry.

Cách hiểu sai	Hệ quả	Cần làm
MERGE là upsert an toàn đồng thời	23505 khi hai request cùng chèn	Dùng ON CONFLICT hoặc retry
id liên tục vì conflict không chèn	Số đứt quãng	Đánh số nghiệp vụ bằng cơ chế khác
Batch nhận mảng thô từ client	21000 khi có key trùng	Gộp trước khi upsert

Tự kiểm tra

1. Vì sao B chờ A trước khi nhận 23505, thay vì lỗi ngay khi INSERT?
2. Vì sao SET view_count = pv.view_count + 1 không mất lượt đếm?
3. Khi nào mẫu CTE + UNION ALL trả 0 row, và vì sao SELECT trong cùng câu lệnh không thấy row đó?

Tóm tắt

Tóm tắt

• SELECT rồi INSERT race ở mọi isolation level (23505, hoặc 40001 ở Serializable); unique index mới chặn trùng.
• ON CONFLICT DO UPDATE bảo đảm INSERT hoặc UPDATE ở Read Committed (mức cao hơn có thể ném 40001).
• DO NOTHING không trả row trùng trong RETURNING; DO UPDATE no-op trả được id nhưng tạo row version và chạy trigger.
• Batch không được trùng key; sequence tiêu tốn kể cả khi conflict.
• MERGE có thể ném 23505 khi chèn đồng thời; dùng cho đồng bộ hàng loạt.

Tham khảo

• PostgreSQL 17 — INSERT, ON CONFLICT: conflict target, EXCLUDED, WHERE, cardinality violation, RETURNING.
• PostgreSQL 17 — Read Committed: bảo đảm của ON CONFLICT và vì sao MERGE gặp unique violation.
• PostgreSQL 17 — MERGE: NOT MATCHED BY SOURCE, RETURNING, merge_action().
• PostgreSQL 17 — Sequence Functions: vì sao ON CONFLICT tạo gap.
• PostgreSQL 17 — Release Notes: MERGE trong bản 17.