Ngăn dữ liệu trùng khi nhiều API gọi đồng thời trong Oracle APEX bằng DBMS_LOCK

Khi xây dựng REST API bằng Oracle APEX và ORDS, một lỗi khó phát hiện là hai request đến gần như cùng thời điểm có thể cùng tạo một bản ghi mới, dù code đã kiểm tra dữ liệu tồn tại trước khi insert.

Tình huống này thường xuất hiện trong các luồng booking, mã giao dịch, placement test, đơn hàng hoặc bất kỳ dữ liệu nào được xác định bằng một khóa nghiệp vụ như TEST_CODE.

Trong bài viết này, chúng ta sẽ phân tích nguyên nhân tạo dữ liệu trùng, vì sao câu lệnh kiểm tra trước khi insert vẫn chưa đủ an toàn, và cách dùng DBMS_LOCK để tuần tự hóa các request cùng xử lý một khóa.

Bài toán kiểm tra trước rồi insert

Một API thường sử dụng logic như sau:

SELECT COUNT(*)
INTO l_count
FROM test_support
WHERE test_code = p_test_code;

IF l_count > 0 THEN
    UPDATE test_support
    SET appointment_time = p_appointment_time
    WHERE test_code = p_test_code;
ELSE
    INSERT INTO test_support (
        test_code,
        appointment_time
    )
    VALUES (
        p_test_code,
        p_appointment_time
    );
END IF;

Nhìn qua, logic này có vẻ hợp lý:

• Nếu TEST_CODE đã tồn tại thì update.
• Nếu chưa tồn tại thì insert.

Tuy nhiên, logic này vẫn có thể tạo hai dòng cùng test code khi có nhiều session chạy đồng thời.

Race condition xảy ra như thế nào?

Giả sử Request A và Request B cùng nhận mã:

K1P9P4

Kết quả là bảng có hai dòng cùng TEST_CODE. Đây được gọi là race condition: kết quả phụ thuộc vào thời điểm và thứ tự thực thi của nhiều session chạy song song.

Vì sao thêm COMMIT không giải quyết được?

Một số trường hợp thử thêm COMMIT ngay sau khi insert:

INSERT INTO test_support (...);
COMMIT;

Nhưng Request B có thể đã thực hiện câu kiểm tra trước thời điểm Request A commit. Vì vậy, B vẫn tiếp tục chạy nhánh insert.

Ngoài ra, commit giữa chừng còn làm transaction bị chia nhỏ. Nếu phần tạo notification, copy kết quả hoặc ghi audit phía sau bị lỗi, record chính vẫn đã được lưu.

DBMS_LOCK là gì?

DBMS_LOCK là package của Oracle cho phép ứng dụng tạo application lock. Đây là một khóa logic do chương trình chủ động quản lý.

Trong bài toán này, mỗi test code được xem như một khóa riêng:

TEST_SUPPORT_K1P9P4
TEST_SUPPORT_ABC001
TEST_SUPPORT_XYZ999

Hai request xử lý cùng một test code sẽ sử dụng cùng application lock. Request đến sau phải chờ request trước hoàn thành.

Hai request dùng test code khác nhau vẫn chạy song song bình thường.

Luồng xử lý sau khi dùng DBMS_LOCK

Tạo lock ID từ TEST_CODE

Hàm DBMS_LOCK.REQUEST có thể nhận một lock ID dạng số. Ta có thể dùng DBMS_UTILITY.GET_HASH_VALUE để tạo lock ID từ test code:

l_test_code := TRIM(p_test_code);

l_lock_id := DBMS_UTILITY.GET_HASH_VALUE(
    name      => 'TEST_SUPPORT_' || l_test_code,
    base      => 1,
    hash_size => 1073741823
);

Nếu test code trong hệ thống phân biệt hoa thường, không nên dùng UPPER hoặc LOWER.

Khi đó các giá trị sau được xem là các mã khác nhau:

AbC001
ABC001
abc001

Xin exclusive application lock

Sau khi có lock ID, request xin quyền xử lý độc quyền:

l_lock_result := DBMS_LOCK.REQUEST(
    id                => l_lock_id,
    lockmode          => DBMS_LOCK.X_MODE,
    timeout           => 10,
    release_on_commit => TRUE
);

Ý nghĩa các tham số:

• DBMS_LOCK.X_MODE: tại một thời điểm chỉ một session được giữ lock.
• timeout => 10: request chờ tối đa 10 giây.
• release_on_commit => TRUE: tự nhả lock khi commit hoặc rollback.

Xử lý kết quả trả về từ DBMS_LOCK

CASE l_lock_result
    WHEN 0 THEN
        NULL;

    WHEN 1 THEN
        RAISE e_lock_timeout;

    WHEN 2 THEN
        RAISE e_lock_deadlock;

    WHEN 4 THEN
        NULL;

    ELSE
        RAISE e_lock_failed;
END CASE;

Các mã quan trọng:

• 0: lấy lock thành công.
• 1: hết thời gian chờ.
• 2: phát hiện deadlock.
• 4: session hiện tại đã giữ lock.

Thứ tự xử lý đúng

Điểm quan trọng là phải lấy lock trước khi kiểm tra dữ liệu tồn tại:

Lấy DBMS_LOCK theo TEST_CODE
        ↓
Kiểm tra TEST_SUPPORT
        ↓
Nếu tồn tại: UPDATE
        ↓
Nếu chưa tồn tại: INSERT
        ↓
Xử lý dữ liệu liên quan
        ↓
COMMIT
        ↓
Tự động nhả lock

Ví dụ:

SELECT COUNT(*),
       MIN(id)
INTO l_existing_count,
     l_existing_id
FROM test_support
WHERE test_code = l_test_code;

IF l_existing_count > 0 THEN
    UPDATE test_support
    SET appointment_time = p_appointment_time,
        online_link      = p_online_link
    WHERE id = l_existing_id;
ELSE
    INSERT INTO test_support (
        test_code,
        appointment_time,
        online_link
    )
    VALUES (
        l_test_code,
        p_appointment_time,
        p_online_link
    )
    RETURNING id INTO l_existing_id;
END IF;

COMMIT;

Việc dùng MIN(ID) giúp tránh lỗi ORA-01422: exact fetch returns more than requested number of rows nếu bảng đã có một số dữ liệu duplicate từ trước.

Kết quả khi hai request cùng chạy

Giả sử Request A lấy lock trước:

Request A lấy lock K1P9P4
Request A kiểm tra chưa có dữ liệu
Request A insert
Request A commit và nhả lock

Trong lúc đó Request B:

Request B xin cùng lock K1P9P4
Request B phải chờ
Request A commit
Request B lấy được lock
Request B kiểm tra lại
Request B thấy record đã tồn tại
Request B update thay vì insert

DBMS_LOCK có khóa toàn bộ bảng không?

Không. Application lock theo test code không tương đương với:

LOCK TABLE test_support IN EXCLUSIVE MODE;

Nếu API đang xử lý K1P9P4, request xử lý ABC001 vẫn chạy bình thường vì hai mã sử dụng hai lock ID khác nhau.

Người dùng trên giao diện vẫn có thể xem dữ liệu bằng câu lệnh SELECT.

Nếu giao diện đồng thời update đúng dòng mà API đang update, thao tác đó có thể phải chờ row lock thông thường của Oracle. Đây là hành vi transaction bình thường, không phải DBMS_LOCK khóa toàn bộ bảng.

Cẩn thận với COMMIT trong procedure con

Khi dùng:

release_on_commit => TRUE

mọi câu lệnh commit trong cùng session đều có thể giải phóng application lock.

Ví dụ API gọi một procedure khác:

SP_GET_STUDENT_FROM_COREEDU(...);

Nếu procedure con có COMMIT, lock có thể bị nhả trước khi API kiểm tra và insert vào TEST_SUPPORT.

Có thể kiểm tra source của procedure con:

SELECT line,
       text
FROM user_source
WHERE name = 'SP_GET_STUDENT_FROM_COREEDU'
  AND type = 'PROCEDURE'
  AND (
      UPPER(text) LIKE '%COMMIT%'
      OR UPPER(text) LIKE '%ROLLBACK%'
  )
ORDER BY line;

Tốt nhất nên để procedure cấp cao nhất quản lý transaction.

Xử lý giá trị :not-update

Một số API sử dụng chuỗi:

:not-update

để biểu thị field không cần thay đổi.

Ví dụ:

{
  "test_code": "K1P9P4",
  "appointment_time": ":not-update",
  "link_ms_team": "https://teams.microsoft.com/example"
}

Khi update, nên xử lý cả NULL và :not-update:

appointment_time =
    CASE
        WHEN rec.appointment_time IS NULL
          OR rec.appointment_time = ':not-update'
        THEN appointment_time
        ELSE rec.appointment_time
    END

Nếu JSON không truyền một field, JSON_TABLE có thể trả về NULL. Nếu không kiểm tra NULL, API có thể vô tình ghi đè dữ liệu hiện tại thành NULL.

OLD_TEST_CODE dùng để tracking mã thay thế

Một số luồng nghiệp vụ thay test code cũ bằng test code mới:

{
  "test_code": "NEW001",
  "old_test_code": "OLD001"
}

Trong trường hợp này:

• NEW001 là mã hiện tại.
• OLD001 là mã trước đó.
• API có thể lưu mã cũ vào cột REMAKE_CODE.
• Kết quả từ mã cũ có thể được copy sang mã mới.

Nếu payload gửi:

{
  "old_test_code": ":not-update"
}

thì :not-update không phải một test code thật. Nó chỉ biểu thị request hiện tại không thay đổi thông tin mã cũ.

Điều kiện xử lý phù hợp:

IF rec.old_test_code IS NOT NULL
   AND TRIM(rec.old_test_code) <> ':not-update'
THEN
    -- Copy dữ liệu từ mã cũ sang mã mới.
END IF;

Có cần unique constraint không?

DBMS_LOCK có thể ngăn duplicate nếu mọi luồng ghi dữ liệu đều tuân theo cùng một cơ chế khóa.

Tuy nhiên, DBMS_LOCK vẫn là quy tắc ở tầng application. Một job, procedure khác hoặc developer insert trực tiếp mà không lấy lock vẫn có thể tạo dữ liệu trùng.

Kiến trúc an toàn nhất là kết hợp:

DBMS_LOCK
    Xử lý concurrency và giúp request thứ hai chờ

UNIQUE CONSTRAINT
    Lớp bảo vệ cuối cùng tại database

Nếu bảng đang có dữ liệu trùng và chưa thể tạo unique constraint, có thể triển khai DBMS_LOCK trước để ngăn duplicate mới. Sau đó làm sạch dữ liệu cũ và bổ sung constraint khi phù hợp.

Kiểm thử concurrency trên UAT

Khi API xử lý quá nhanh, việc bấm hai request bằng tay có thể chưa tạo được concurrency thực sự.

Có thể tạm thời giữ lock trong 7 giây:

DBMS_LOCK.SLEEP(7);

Đặt câu lệnh này sau khi lấy lock thành công. Sau đó mở hai session và gửi hai request cùng một test code.

Request A sẽ giữ lock trong 7 giây. Request B phải chờ cho đến khi A commit và nhả lock.

Sau khi kiểm thử xong, phải xóa:

DBMS_LOCK.SLEEP(7);

Không để delay này trên môi trường production.

Kiểm tra kết quả test

Kiểm tra số lượng record:

SELECT COUNT(*)
FROM test_support
WHERE test_code = 'LOCK_UAT_TEST_001';

Kết quả mong đợi:

1

Kiểm tra log:

SELECT action,
       status,
       error_message,
       log_time
FROM api_logs
WHERE api_name = 'support_test'
  AND item_value = 'LOCK_UAT_TEST_001'
ORDER BY log_time;

Kết quả đúng thường gồm:

INSERT  SUCCESS
UPDATE  SUCCESS

Không nên có:

INSERT  SUCCESS
INSERT  SUCCESS

Quyền cần cấp cho schema

Schema chứa stored procedure cần được cấp quyền trực tiếp:

GRANT EXECUTE ON SYS.DBMS_LOCK TO VUSCONNECT;

Sau đó compile lại procedure:

ALTER PROCEDURE SP_SUPPORT_TEST_API COMPILE;

Và kiểm tra trạng thái:

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'SP_SUPPORT_TEST_API'
  AND object_type = 'PROCEDURE';

Kết quả cần là:

SP_SUPPORT_TEST_API   VALID

Best practice khi dùng DBMS_LOCK trong API

• Tạo lock theo từng khóa nghiệp vụ, không khóa chung toàn bộ module.
• Lấy lock trước khi kiểm tra record tồn tại.
• Kiểm tra lại dữ liệu sau khi lấy được lock.
• Đặt timeout hợp lý để tránh request chờ vô hạn.
• Dùng release_on_commit => TRUE.
• Không commit giữa quá trình kiểm tra và insert/update.
• Kiểm tra các procedure con có commit hoặc rollback hay không.
• Ghi log rõ action INSERT, UPDATE, LOCK TIMEOUT và lỗi.
• Không để câu lệnh sleep kiểm thử trên production.
• Về lâu dài nên bổ sung unique constraint nếu business cho phép.

Kết luận

Logic kiểm tra trước rồi insert sau không đủ an toàn trong môi trường có nhiều request chạy đồng thời.

DBMS_LOCK giúp tuần tự hóa xử lý theo từng khóa nghiệp vụ:

Cùng TEST_CODE → xử lý tuần tự
Khác TEST_CODE → xử lý song song

Đây là giải pháp phù hợp cho các REST API Oracle APEX và ORDS xử lý booking, test code, đơn hàng, mã giao dịch hoặc các nghiệp vụ cần tránh tạo trùng do race condition.

Mô hình bền vững nhất là kết hợp:

DBMS_LOCK
+ transaction được quản lý đúng
+ kiểm tra procedure con
+ unique constraint khi có thể

Cách tiếp cận này vừa giúp request thứ hai xử lý đúng luồng, vừa bảo vệ tính toàn vẹn của dữ liệu trong hệ thống Oracle APEX.