"Có thì cập nhật, chưa có thì chèn" là thao tác quen thuộc nhất mà SQL chuẩn không có cú pháp riêng. Cách viết tay — kiểm tra rồi ghi — chạy đúng khi bạn thử, và hỏng khi có nhiều tiến trình. Phần này đo cả hai.

So sánh một tiến trình và sáu tiến trình, ba cái bẫy, ba tính năng ít dùng

Một tiến trình: không có khác biệt

Bảng kho 500.000 dòng. 300 lượt cập nhật ngẫu nhiên, hai phần ba trúng khoá đã có, một phần ba là khoá mới:

Cách Thời gian
insert ... on conflict (ma) do update 0,85 s
update rồi insert ... where not exists 0,87 s

Ngang nhau. Không có lý do hiệu năng nào để chọn cách này hay cách kia.

Đây là lý do cách viết tay tồn tại lâu đến vậy: nó chạy đúng và chạy nhanh trong mọi bài kiểm thử một luồng.

Sáu tiến trình: một cách hỏng hoàn toàn

25 vòng, mỗi vòng sáu tiến trình cùng ghi vào một khoá mới giống nhau. Kết quả đúng phải là ton = 6: một lần tạo dòng cộng năm lần tăng.

Cách Lỗi Vòng cho kết quả sai
ON CONFLICT DO UPDATE 0 0 / 25
Kiểm tra rồi ghi 99 25 / 25 
ERROR:  duplicate key value violates unique constraint "kho_pkey"

Không vòng nào cho kết quả đúng.

Khe hở nằm ở chỗ này:

begin;
update kho set ton = ton + 1 where ma = 'X';        -- 0 dòng, vì X chưa có
insert into kho select 'X',1,now()
  where not exists (select 1 from kho where ma='X');  -- <- khe hở ở đây
commit;

Giữa lúc not exists trả về đúng và lúc insert thật sự ghi, một tiến trình khác có thể đã chèn cùng khoá đó. Cả hai đều thấy "chưa có", cả hai đều chèn, một cái thua với lỗi trùng khoá.

Đây chính là kiểu lỗi mà phần 25 đã đo ở mức khái niệm — đọc rồi ghi mà không có khoá. ON CONFLICT không có khe hở đó vì PostgreSQL xử lý toàn bộ ở tầng lưu trữ: nó thử chèn, gặp xung đột ở chỉ mục duy nhất, và chuyển sang cập nhật — tất cả bên trong một câu lệnh nguyên tử.

Đáng chú ý là tỷ lệ hỏng: 25 trên 25 vòng. Với sáu tiến trình cùng một khoá thì đây gần như là điều chắc chắn. Còn trên hệ thống thật, nơi trùng khoá xảy ra thưa hơn nhiều, nó chỉ hỏng đôi khi — đủ hiếm để lọt qua kiểm thử, đủ thường xuyên để xuất hiện trong log lỗi hàng tuần.

Ba cái bẫy

Đòi hỏi ràng buộc duy nhất trên đúng cột đó.

insert into nt values ('a',1) on conflict (ma) do nothing;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

ON CONFLICT (ma) chỉ chạy khi có unique hoặc primary key trên ma. Đây là điểm mạnh chứ không phải hạn chế: nó buộc bạn phải nói rõ "trùng" nghĩa là gì, và bảo đảm cơ sở dữ liệu có cách phát hiện điều đó.

DO NOTHING vẫn tiêu số của chuỗi.

sau khi chèn 1.000 dòng                    last_value = 1.000
sau 1.000 lượt DO NOTHING (chèn 0 dòng)    last_value = 2.000

Chuỗi nhảy thêm 1.000 dù không dòng nào được tạo. Với bảng dùng bigserial thì vô hại — bạn có 9,2 tỷ tỷ giá trị. Với serial (32 bit) và một công việc đồng bộ chạy mỗi phút mà hầu hết là DO NOTHING, bạn sẽ cạn số nhanh hơn dự kiến rất nhiều.

Tôi cũng kiểm tra giả định rằng DO NOTHING để lại dòng chết — sai. Bảng vẫn 3.544 kB và n_dead_tup bằng 0 sau 100.000 lượt. PostgreSQL kiểm chỉ mục trước khi thật sự ghi dòng, nên không có gì để dọn.

Khoá trùng trong cùng một lệnh thì báo lỗi.

insert into kho select ma, ton, now() from src2 on conflict (ma) do update ...
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have
       duplicate constrained values.

Nếu nguồn có hai dòng cùng khoá, ON CONFLICT từ chối. Nó không biết dòng nào nên thắng, và nó không đoán.

Cách chữa là khử trùng ở nguồn trước:

insert into kho(ma, ton, cap_nhat)
select distinct on (ma) ma, ton, now()
from src2
order by ma, ton desc          -- dòng có ton lớn nhất thắng
on conflict (ma) do update set ton = excluded.ton;

Đây là lỗi hay gặp nhất khi nạp dữ liệu từ hệ thống ngoài, và nó chỉ nổ ra khi dữ liệu nguồn có trùng — tức là lúc bạn ít chờ đợi nhất.

Ba thứ ít dùng mà đáng biết

excluded là dòng đề nghị chèn, tên bảng là dòng đang có.

insert into sq(ma, v) values ('K1', 99)
on conflict (ma) do update set v = sq.v + excluded.v;
--  v cũ là 0, excluded.v là 99  ->  v thành 99

Đây là cách viết bộ đếm cộng dồn: set ton = kho.ton + excluded.ton. Nếu quên tiền tố và viết set ton = ton + excluded.ton thì PostgreSQL vẫn hiểu ton là cột của bảng đích — nhưng viết rõ tên bảng thì người đọc sau không phải đoán.

WHERE trong DO UPDATE để ghi đè có điều kiện.

insert into sq(ma, v) values ('K1', 5)
on conflict (ma) do update set v = excluded.v where sq.v < 10;

Đo được: K1 đang có v = 99 nên không đổi; K2 có v = 0 nên đổi thành 5.

Dùng cho mẫu "chỉ cập nhật nếu dữ liệu mới hơn":

on conflict (ma) do update
set ton = excluded.ton, cap_nhat = excluded.cap_nhat
where kho.cap_nhat < excluded.cap_nhat;

Không có mệnh đề này thì một bản ghi cũ đến muộn sẽ ghi đè bản mới — lỗi kinh điển khi xử lý dữ liệu đến không đúng thứ tự.

RETURNING phân biệt được chèn mới hay cập nhật.

insert into sq(ma, v) values ('MOI',1),('K3',1)
on conflict (ma) do update set v = excluded.v
returning ma, (xmax = 0) as la_dong_moi;

--  MOI | t     dòng vừa được chèn
--  K3  | f     dòng đã có, vừa được cập nhật

xmax = 0 nghĩa là dòng chưa từng bị giao dịch nào đánh dấu hết hạn — tức là nó mới toanh. Phần 21 đã đo chính cột hệ thống này. Đây là mẹo duy nhất tôi biết để phân biệt hai trường hợp trong một lệnh.

UPSERT hàng loạt

insert into kho(ma, ton, cap_nhat)
select ma, ton, now() from src
on conflict (ma) do update
set ton = kho.ton + excluded.ton, cap_nhat = now();

200.001 dòng trong 1,24 giây bằng một lệnh duy nhất — vừa cập nhật dòng đã có, vừa chèn dòng mới.

So với vòng lặp ở tầng ứng dụng gọi 200.001 lần, phần 37 đã đo con số tương ứng: 7.278 dòng mỗi giây, tức khoảng 27 giây. Nhanh hơn 22 lần, và đúng đắn dưới tranh chấp.

Khi nào không dùng được ON CONFLICT

Khi "trùng" không phải là một ràng buộc duy nhất. Ví dụ "cùng khách hàng và cùng ngày" mà bạn không muốn đặt unique lên hai cột đó. Khi ấy phải dùng khoá tường minh (phần 27) hoặc MERGE (có từ PostgreSQL 15).

Khi cần biết chính xác đã làm gì với từng dòng trong một lô lớn. RETURNING với xmax giải quyết được phần lớn, nhưng nếu logic phức tạp hơn thì MERGE với ba nhánh WHEN rõ ràng hơn.

Khi bảng phân mảnh và ràng buộc duy nhất không phủ được cột phân mảnh. Phần 36 đã nói: khoá chính bắt buộc chứa cột phân mảnh, nên ON CONFLICT (ma) không dùng được nếu bảng phân mảnh theo luc.

Thử ba mươi giây

Tìm trong log xem cách kiểm-tra-rồi-ghi có đang hỏng không:

select * from pg_stat_database where datname = current_database();

Rồi tìm trong log máy chủ:

grep "duplicate key value violates" postgresql.log | wc -l

Mỗi dòng như vậy là một lần hai tiến trình cùng chèn một khoá. Nếu ứng dụng của bạn có bắt lỗi và thử lại thì không sao. Nếu không, mỗi dòng đó là một thao tác của người dùng đã thất bại — và cách chữa là một câu ON CONFLICT.

Phần sau đo MERGE của PostgreSQL 15: nó khác ON CONFLICT chỗ nào, và có đáng đổi không.