Dùng giao dịch để chuyển credit giữa hai tài khoản

Chuyển 20 credit cần hai thay đổi: trừ 20 ở tài khoản gửi và cộng 20 vào tài khoản nhận. Một giao dịch SQL có thể công bố cả hai thay đổi cùng lúc hoặc hủy cả hai. Nhưng giao dịch không tự biết câu UPDATE thứ hai đã tìm thấy đúng người nhận hay chưa. Bạn phải biến yêu cầu nghiệp vụ đó thành một kết quả được kiểm tra.

Bài này dành cho người đã biết SELECT, UPDATE và bắt đầu xây dựng thao tác API có cơ sở dữ liệu phía sau. Ví dụ dùng PostgreSQL 17, hai tài khoản và một loại credit nguyên đơn vị. Không có quy đổi tiền tệ, nhà cung cấp thanh toán hay hệ thống phân quyền. Hãy chạy trong cơ sở dữ liệu dùng thử với hai phiên psql độc lập; giữ phiên B ở chế độ autocommit thông thường, tức tự commit từng câu lệnh thành công.

Bắt đầu với một tổng có thể đối chiếu

CREATE TABLE credit_accounts (
  id bigint PRIMARY KEY,
  credits bigint NOT NULL CHECK (credits >= 0)
);
INSERT INTO credit_accounts VALUES (1, 50), (2, 10);

Số dư ban đầu là 50 và 10, tổng cộng 60. Sau một lần chuyển thành công, chúng phải là 30 và 30. Nếu chuyển thất bại, chúng phải giữ nguyên 50 và 10. Khóa chính giúp mỗi lần tra mã tài khoản tìm thấy nhiều nhất một hàng. Ràng buộc không âm bảo vệ từng số dư được lưu; nó không yêu cầu tổng giữa các tài khoản luôn bằng 60.

Hướng dẫn về giao dịch của PostgreSQL giải thích ranh giới cùng thành công hoặc cùng bị hủy của nhiều câu lệnh. Câu lệnh nằm ngoài một khối giao dịch tường minh cũng chạy trong giao dịch. Vì thế, nếu gửi hai câu cập nhật riêng ở chế độ autocommit, câu đầu có thể đã commit trước cả khi câu thứ hai được thử chạy.

Giao dịch vẫn có thể commit một lần chuyển chưa đủ bước

Hãy thử thao tác cố ý thiếu bước sau:

BEGIN;
UPDATE credit_accounts SET credits = credits - 20 WHERE id = 1;
UPDATE credit_accounts SET credits = credits + 20 WHERE id = 999
RETURNING id;
SELECT id, credits FROM credit_accounts ORDER BY id;
ROLLBACK;

Câu cập nhật người nhận không trả về mã nào vì tài khoản 999 không tồn tại. Tuy vậy, truy vấn tiếp theo vẫn cho thấy tài khoản 1 còn 30 và tài khoản 2 có 10. Không có lỗi SQL. Hai số dư còn lại đều thỏa ràng buộc của hàng. Nếu thay ROLLBACK cuối bằng COMMIT, cơ sở dữ liệu sẽ lưu tổng 40. Lệnh rollback đang có trong ví dụ khôi phục 50 và 10 ban đầu để chuẩn bị cho phần tiếp theo.

Tính nguyên tử gộp những thao tác bạn thực sự chạy. Một câu cập nhật ảnh hưởng 0 hàng vẫn là thao tác SQL thành công, kể cả khi ứng dụng cần đúng một hàng được sửa. Vì thế, chỉ bắt ngoại lệ sẽ bỏ sót lỗi này. Bạn còn phải kiểm tra kết quả của từng thay đổi bắt buộc.

Chỉ chấp nhận thành công khi cả hai hàng đều được cập nhật

Hàm PL/pgSQL nhỏ dưới đây đặt phần kiểm tra ngay cạnh phần ghi. BEGIN và END bên trong hàm đánh dấu một khối chương trình; chúng không mở hoặc commit một giao dịch độc lập.

CREATE FUNCTION transfer_credits(
  p_from bigint, p_to bigint, p_amount bigint
) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  IF p_from IS NULL OR p_to IS NULL OR p_amount IS NULL
     OR p_amount <= 0 OR p_from = p_to THEN
    RAISE EXCEPTION 'invalid transfer arguments'
      USING ERRCODE = '22023';
  END IF;

  UPDATE credit_accounts
  SET credits = credits - p_amount
  WHERE id = p_from AND credits >= p_amount;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'source missing or insufficient credits'
      USING ERRCODE = 'P0001';
  END IF;

  UPDATE credit_accounts
  SET credits = credits + p_amount
  WHERE id = p_to;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'destination missing'
      USING ERRCODE = 'P0002';
  END IF;
END;
$$;

Điều kiện đầu vào từ chối giá trị thiếu, số lượng không dương và việc chuyển cho chính tài khoản gửi. Câu cập nhật nguồn gộp phép trừ với điều kiện đủ credit, dùng trực tiếp giá trị đang lưu để tính toán. Cách này tránh đọc số dư vào bộ nhớ ứng dụng rồi ghi trở lại một kết quả đã tính từ dữ liệu cũ.

Sau mỗi câu cập nhật, FOUND cho hàm biết có ít nhất một hàng bị ảnh hưởng hay không. Với điều kiện theo khóa chính, kết quả chỉ có thể là đúng một hoặc 0 hàng. Phải kiểm tra ngay vì câu SQL chạy sau có thể thay đổi giá trị này. Ví dụ cố ý dùng chung một lỗi cho nguồn không tồn tại và nguồn không đủ số dư; đích không tồn tại có lỗi khác. Đây là thông tin chẩn đoán từ cơ sở dữ liệu, chưa phải hợp đồng phản hồi HTTP hay kiểm tra quyền.

RAISE EXCEPTION biến yêu cầu nghiệp vụ không đạt thành một lỗi. Ví dụ không có bộ xử lý ngoại lệ hay khôi phục qua savepoint, tức điểm lưu trong giao dịch. Nếu không tìm thấy đích sau khi đã trừ ở nguồn, hàm không thể để riêng khoản trừ đó được commit. Bên gọi phải coi thao tác là thất bại.

Đặt quy tắc nhỏ này trong hàm SQL là một lựa chọn hợp lý. Mã ứng dụng cũng có thể chạy hai câu cập nhật trên cùng một kết nối đã lấy ra từ pool, yêu cầu mỗi câu ảnh hưởng đúng một hàng rồi rollback nếu có kết quả khác. Khi chọn cách đó, xử lý lỗi của driver và việc giữ đúng kết nối trở thành một phần của triển khai. Gửi BEGIN qua một kết nối trong pool rồi gửi cập nhật qua kết nối khác không tạo thành một giao dịch chung.

Quan sát ranh giới commit từ hai phiên

Trong phiên A, chạy:

BEGIN;
SELECT transfer_credits(1, 2, 20);
SELECT id, credits FROM credit_accounts ORDER BY id;

A thấy 30 và 30 vì giao dịch được đọc những thay đổi do chính nó thực hiện. Hãy để giao dịch mở. Trong phiên B, chạy:

SELECT id, credits FROM credit_accounts ORDER BY id;

B thấy 50 và 10. Truy vấn đọc thông thường này không phải chờ các hàng A vừa sửa, cũng không thấy các trạng thái trung gian chưa được A công bố. Đây là hành vi theo quy tắc Read Committed của PostgreSQL: truy vấn đọc dữ liệu đã commit tại snapshot, hay ảnh chụp trạng thái, của câu lệnh. Ví dụ giả định không có bên ghi nào khác đang thay đổi hai tài khoản này.

Bây giờ kết thúc A:

COMMIT;

Chạy lại truy vấn của B. Lần này B thấy 30 và 30. “Sau commit” ở đây nghĩa là một câu lệnh mới có snapshot phù hợp; commit không sửa lại kết quả đã trả về hay buộc một snapshot cũ đang tồn tại phải làm mới.

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

Phiên sở hữu giao dịch có thể kiểm tra việc vừa làm trước khi công bố. Kết nối thứ hai thấy trọn cặp số dư đã commit.

Bản thân hàm không có commit. Gọi hàm mà không viết BEGIN vẫn đặt các thay đổi vào giao dịch ngầm của chính câu lệnh đó. Giao dịch bao ngoài hữu ích khi lần chuyển và một thay đổi cơ sở dữ liệu khác phải cùng thành công, chẳng hạn thêm bản ghi kiểm toán. Một lời gọi HTTP riêng vẫn nằm ngoài bảo đảm của cơ sở dữ liệu này.

Câu lệnh thành công vẫn có thể bị rollback

Từ trạng thái đã commit 30 và 30, chạy trong A:

BEGIN;
SELECT transfer_credits(1, 2, 20);
SELECT id, credits FROM credit_accounts ORDER BY id;
ROLLBACK;
SELECT id, credits FROM credit_accounts ORDER BY id;

Kết quả đầu là 10 và 50. Kết quả sau là 30 và 30. Bên trong hàm không có lỗi; bên gọi chủ động hủy toàn bộ giao dịch đang mở. Rollback khôi phục trạng thái cơ sở dữ liệu trước đó, chứ không tạo một lần chuyển mới theo chiều ngược lại.

Tiếp theo, thử người nhận không tồn tại:

BEGIN;
SELECT transfer_credits(1, 999, 20);
ROLLBACK;
SELECT id, credits FROM credit_accounts ORDER BY id;

Hàm báo P0002; sau rollback, số dư vẫn là 30 và 30. Trong psql tương tác, hãy chạy rollback sau lỗi dự kiến này. Script được cấu hình dừng ngay khi gặp lỗi sẽ dừng trước các câu còn lại; bên gọi phải chịu trách nhiệm dọn trạng thái. Khi dùng driver, cần rollback giao dịch đã lỗi trước khi dùng lại hoặc trả kết nối về pool.

Nếu không khôi phục qua savepoint, một lỗi trong giao dịch tường minh để giao dịch ở trạng thái đã hủy: truy vấn thông thường tiếp theo bị từ chối cho tới khi rollback. Những thay đổi trước đó trong cùng giao dịch cũng bị bỏ đi. Các thay đổi đã commit từ giao dịch trước không bị ảnh hưởng. Sự khác biệt này giúp xác định những thao tác ghi nào cần nằm trong cùng một đơn vị công việc.

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

Kiểm tra số hàng quyết định bước nghiệp vụ có thực sự xảy ra hay không. Ranh giới giao dịch quyết định các bước thành công có trở thành trạng thái đã commit hay không.

Kiểm tra ranh giới trước khi bọc nó thành endpoint

Các kiểm tra PostgreSQL 17.11 cục bộ còn thử tài khoản nhận đang có giá trị bigint lớn nhất. Cộng thêm 20 gây lỗi tràn số và giữ nguyên số dư nguồn. Các kiểu số nguyên của PostgreSQL có phạm vi hữu hạn. Kiểu lưu trữ cũng không thay thế việc kiểm tra đầu vào: bên gọi có thể ép số numeric 1.5 sang bigint, tạo ra 2 trước khi hàm nhận tham số. API cần định nghĩa riêng dạng biểu diễn được chấp nhận.

Hai kết nối thật đã kiểm tra việc cùng trừ từ nguồn có 50 credit. A giữ một lần chuyển 40 credit chưa commit; lần chuyển 20 credit của B phải chờ. Sau khi A commit, B kiểm tra lại điều kiện nguồn và thất bại vì không đủ số dư. Nếu A rollback, B thành công. Điều đó kiểm chứng phép tính có điều kiện này trong hai thứ tự đã thử. Nó không chứng minh hệ thống không có deadlock: các lần chuyển ngược chiều có thể khóa hai tài khoản theo thứ tự xung đột. Tài liệu khóa của PostgreSQL trình bày vấn đề riêng này.

Cuối cùng, giao dịch cơ sở dữ liệu thành công không khiến việc gửi lại yêu cầu trở nên vô hại. Từ 50 và 10, hai lần gọi với cùng tham số đều thành công và để lại 10 và 50. Bài viết ngày 6 tháng 9 năm 2017 của Brandur Leach về giao dịch và API có tính idempotent phân biệt công việc trong cơ sở dữ liệu, việc thử lại yêu cầu và tác động bên ngoài. Hàm ở đây chưa có khóa idempotency để nhận diện lần gọi lặp, cũng chưa có giao thức phối hợp tác động bên ngoài. Mất phản hồi commit cũng không có nghĩa commit đã thất bại; lặp lại lần chuyển một cách máy móc là không an toàn.

Để tự thực hành, mỗi lần hãy bắt đầu lại từ số dư 50/10 và dự đoán ba tình huống: chuyển cả 50, chuyển 51 và chuyển 20 tới tài khoản 999. Kiểm tra cả hai tài khoản sau từng lần thử độc lập. Sau đó đóng một kết nối khi lần chuyển thành công của nó vẫn chưa commit, rồi dùng kết nối kia xác nhận số dư ban đầu còn nguyên. Những kiểm tra đó đánh giá ranh giới cơ sở dữ liệu thực tế; phân quyền, lịch sử kiểm toán, chính sách thử lại và cấu hình độ bền dữ liệu trong sản phẩm vẫn cần thiết kế và bằng chứng riêng.