Thứ Bảy, 31 tháng 5, 2025

Cơ chế rollback một batch job lớn (update/delete/insert hàng chục triệu bản ghi) trong PostgreSQL, các rủi ro có thể gặp, và biện pháp phòng ngừa an toàn

Bài toán: Khi tôi chạy 1 batch job ví dụ update/delete 100 triệu bản ghi, thậm chí insert 100 triệu bản ghi, chạy giữa chừng cancel hoặc bị kill gây ra quá trình rollback transaction, thậm chi treo database, crash database 
Qua bài viết này tôi sẽ giải thích kỹ cơ chế rollback batch job lớn: 
- Khi nào diễn ra quá trình này 
- Các rủi ro có thể xảy ra với quá trình 
- Các biện pháp phòng ngừa
Vào việc:

1. Khi nào diễn ra rollback trong batch job lớn?

Rollback xảy ra khi một transaction chưa commit bị:

  • Người dùng chủ động cancel (Ctrl+C hoặc pg_cancel_backend)

  • Bị timeout, lỗi mạng, hoặc lỗi hệ thống (out-of-disk, full WAL, lỗi logic)

  • Bị kill bằng pg_terminate_backend

  • Gặp exception khi chạy PL/pgSQL hoặc lỗi trigger

PostgreSQL đảm bảo ACID, nên nếu 1 transaction chưa COMMIT thì rollback sẽ được tự động kích hoạt.

2. Cơ chế rollback hoạt động như thế nào?

Khi rollback:

  • PostgreSQL sử dụng MVCC: mỗi hành động ghi dữ liệu tạo ra phiên bản mới.

  • Rollback không xoá dữ liệu ngay, mà đánh dấu các phiên bản vừa ghi là vô hiệu.

  • Dữ liệu sẽ được dọn dẹp trong lần VACUUM kế tiếp.

Với hàng triệu bản ghi, rollback sẽ quét toàn bộ các bản ghi vừa tạo/ghi, gây IO rất lớn, kéo dài thời gian rollback → làm treo hoặc full disk, đặc biệt khi WAL chưa được archive.

3. Rủi ro khi rollback batch lớn

Rủi roNguyên nhân
Rollback mất hàng giờPostgreSQL phải dọn sạch 100 triệu bản ghi chưa COMMIT
Treo databaseQuá trình rollback lock buffer, I/O, gây full shared_buffers
Full disk hoặc pg_wal/WAL chưa được ghi hết, hoặc chưa archive kịp
VACUUM chạy lâuHàng loạt tuple chết phải dọn
Autovacuum không kịp chạyTrì hoãn do hệ thống overload
Long lockBlock các truy vấn khác khi rollback diễn ra

4. Biện pháp phòng ngừa rollback batch job lớn

✅ A. Chia nhỏ batch

Không update 100 triệu bản ghi trong 1 transaction!

sql
-- Xử lý theo batch 100,000 dòng
DO $$ 
DECLARE
  r INT := 0;
BEGIN
  LOOP
    WITH cte AS (
      SELECT id FROM logs WHERE created_at < now() - interval '90 days' LIMIT 100000
    )
    DELETE FROM logs WHERE id IN (SELECT id FROM cte);
    
    EXIT WHEN NOT FOUND;
    
    r := r + 1;
    RAISE NOTICE 'Batch % processed', r;
    COMMIT;  -- Nếu cần, hoặc giữ trong 1 transaction lớn
  END LOOP;
END $$;

Giúp rollback nhanh nếu 1 batch lỗi, không ảnh hưởng toàn bộ.

✅ B. Dùng COMMIT từng phần (manual batching)

bash
BEGIN;
-- Update 1 triệu dòng đầu
UPDATE customers SET active = false WHERE last_login < now() - interval '1 year' LIMIT 1000000;
COMMIT;

-- Tiếp tục batch kế tiếp

✅ C. Giới hạn thời gian hoặc lock

sql
SET statement_timeout = '10min';  -- Tự động cancel truy vấn lâu

✅ D. Ghi log toàn bộ transaction rollback

Trong postgresql.conf:

conf
log_min_error_statement = ERROR
log_statement = 'ddl'

✅ E. Theo dõi WAL trước khi chạy batch

bash
du -sh /var/lib/pgsql/13/data/pg_wal

Nếu WAL sắp đầy, dừng batch job để tránh treo hệ thống.

5. Kiểm tra session đang rollback

sql
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'idle in transaction (aborted)';

Nếu có nhiều session idle in transaction (aborted) → cần ROLLBACK; hoặc kill session.

6. Kill session gây rollback lớn – nên làm gì?

Không dùng pg_terminate_backend cho transaction lớn nếu không biết rõ đang rollback!

Thay vào đó:

sql
-- Đợi rollback xong rồi dọn bằng VACUUM:
SELECT * FROM pg_stat_progress_vacuum;

Hoặc:

bash
pg_cancel_backend(<pid>) -- nhẹ nhàng hơn terminate

✅ Tổng kết

Cần làmLý do
Luôn chia nhỏ batch jobTránh rollback toàn bộ
Giới hạn statement_timeoutBảo vệ hệ thống khỏi truy vấn treo
Theo dõi pg_wal, pg_stat_activityPhát hiện rollback hoặc full disk
Ghi log và kiểm tra session lỗiTối ưu vận hành và cảnh báo
Dùng PL/pgSQL để điều khiển vòng lặp batchCho phép rollback từng phần

=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels: ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master