Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMSenior16 phút đọc

Transaction gây hại: quá lớn, chạy lâu, treo giữa chừng, bị bỏ rơi

Ôn 12 thẻ
Trong bài này

Cụm bài Transaction và locking chuyên sâu: 1. Transaction từ bên trong · 2. Transaction gây hại · 3. Lock trong InnoDB · 4. Optimistic, pessimistic và bài đặt vé

Phần 1 cho thấy transaction mở lâu có hại vì nó giữ lock và giữ snapshot: InnoDB không purge được undo log (history list length tăng, đọc chậm dần), PostgreSQL không VACUUM được dead tuple. Phần này đi vào việc thực tế: nhận diện, tìm ra và sửa các transaction như vậy. Đọc xong, bạn tìm được từng kiểu và biết đặt timeout ở đâu.

Sơ đồ tóm tắt:

flowchart LR
  A["Transaction gây hại"] --> B["Quá lớn"]
  A --> C["Chạy lâu"]
  A --> D["Treo giữa chừng"]
  A --> E["Bị bỏ rơi"]
  B --> B2["Chia lô theo khoá chính"]
  C --> C2["Tìm gốc: index, N+1, lock"]
  D --> D2["Đưa HTTP ra ngoài"]
  E --> E2["Sửa connection leak"]
  B2 & C2 & D2 & E2 --> F["innodb_trx, pg_stat_activity"]
  F --> G["Timeout mỗi tầng, theo user"]

1. Bốn kiểu transaction gây hại

Ý chính: mỗi kiểu có một nguyên nhân và một cách chữa riêng, nên phải gọi đúng tên trước khi sửa.

Kiểu Đặc điểm Nguyên nhân thường gặp
Quá lớn (large) Sửa quá nhiều dòng Một câu UPDATE/DELETE cho cả bảng
Chạy lâu (long-running) Từ BEGIN tới COMMIT quá lâu Câu chậm, quá nhiều câu, chờ lock
Treo giữa chừng (stalled) Đứng yên giữa các câu Ứng dụng gọi HTTP, xử lý nặng trong transaction
Bị bỏ rơi (abandoned) Client đã biến mất, transaction vẫn mở Connection leak, connection nửa đóng

Các truy vấn trong bài đã chạy thử trên MySQL 8.4.11 và PostgreSQL 16.14 (Docker).

1.1. Transaction quá lớn (large)

Ý chính: một transaction sửa hàng triệu dòng thì khoá nhiều, làm replica trễ và rollback lâu. Hầu như luôn chia lô được.

Triệu chứng: ví dụ UPDATE orders SET status = 'ARCHIVED' WHERE created_at < '2024-01-01'. Nó khoá rất nhiều dòng (kèm gap lock ở RR — khoá cả khoảng trống giữa các dòng) và sinh nhiều undo, redo. Replica trễ vì cả transaction được áp một lần trên replica.

Tìm: information_schema.innodb_trx có trx_rows_modified và trx_rows_locked; performance_schema.events_statements_history cho biết câu nào. Trên PostgreSQL, pg_stat_activity cộng pg_stat_user_tables.n_tup_upd.

Sửa: câu hỏi đầu tiên của slide là đúng: vì sao phải sửa nhiều dòng như vậy trong một transaction? Thường là không cần. Chia lô theo khoá chính:

-- Lặp từ ứng dụng hoặc job cho tới khi affected rows = 0
UPDATE orders
SET status = 'ARCHIVED'
WHERE created_at < '2024-01-01' AND status <> 'ARCHIVED'
ORDER BY id
LIMIT 5000;
  • UPDATE ... ORDER BY ... LIMIT là cú pháp MySQL. PostgreSQL viết WHERE id IN (SELECT id FROM orders WHERE ... ORDER BY id LIMIT 5000).
  • Mỗi lô vài nghìn dòng, nghỉ ngắn giữa các lô để replica theo kịp. Bài xoá dữ liệu cũ an toàn đi chi tiết mẫu này cho DELETE.
  • Cái giá: mất tính nguyên tử của cả thao tác, nên mỗi lô phải chạy lại được an toàn (điều kiện status <> 'ARCHIVED' làm việc đó).

1.2. Transaction chạy lâu (long-running)

Ý chính: transaction mất nhiều giây hay nhiều phút vì câu chậm (thiếu index), quá nhiều câu (vòng lặp N+1 trong @Transactional), hoặc chờ lock. Tìm nguyên nhân gốc trước, rồi đặt giới hạn thời gian.

Tìm:

-- MySQL: transaction mở lâu nhất và nó đang chờ gì
SELECT trx_mysql_thread_id, trx_state,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

-- Ai đang chặn ai (view của sys schema)
SELECT waiting_query, blocking_pid, blocking_query, wait_age
FROM sys.innodb_lock_waits;

Sửa: slide gợi ý đúng: rất có thể là chờ lock. Thêm index, đưa việc không cần nguyên tử ra ngoài transaction, giảm số câu. Đặt giới hạn: @Transactional(timeout = 5) trong Spring, innodb_lock_wait_timeout (mặc định 50 giây, quá dài cho request web), lock_timeout và statement_timeout ở PostgreSQL.

1.3. Transaction bị treo giữa chừng (stalled)

Ý chính: các câu SQL đều nhanh, nhưng giữa các câu ứng dụng làm việc khác. Trong lúc đó lock và read view vẫn bị giữ; cách chữa là đưa việc bên ngoài ra khỏi transaction.

Triệu chứng: ứng dụng gọi HTTP, render PDF, chờ người dùng. Database thấy transaction mở mà không chạy gì: MySQL command = 'Sleep' trong processlist, PostgreSQL state = 'idle in transaction'.

@Transactional
public void checkout(long orderId) {
    Order order = orders.findByIdForUpdate(orderId);   // khoá dòng order
    PaymentResult r = paymentClient.charge(order);     // gọi HTTP 2 giây, lock vẫn bị giữ
    order.markPaid(r.transactionId());
}

Sửa: câu hỏi của slide “những câu này có cần nằm trong một transaction không?” thường có câu trả lời là không.

  • Gọi bên ngoài trước hoặc sau transaction; transaction chỉ bao quanh phần đọc-ghi DB.
  • Cần “ghi DB và gửi sự kiện” cùng xảy ra thì dùng transactional outbox (bài message broker).
  • Slide gợi ý chuyển sang READ COMMITTED để bớt gap lock: giảm số lock bị giữ, nhưng không chữa gốc bệnh là transaction đứng chờ.

Lưới an toàn: PostgreSQL có idle_in_transaction_session_timeout tự cắt transaction đứng yên; từ PostgreSQL 17 còn có transaction_timeout giới hạn tổng thời gian. MySQL không có tham số tương đương; wait_timeout (mặc định 8 giờ) chỉ đóng connection rảnh, quá dài để làm lưới an toàn.

Thử idle_in_transaction_session_timeout trên PostgreSQL 16

Mình đặt idle_in_transaction_session_timeout = '2s', BEGIN, chạy một câu rồi để yên 4 giây:

FATAL:  terminating connection due to idle-in-transaction timeout

1.4. Transaction bị bỏ rơi (abandoned)

Ý chính: client đã biến mất nhưng transaction vẫn mở, thường do connection leak. Chữa ở ứng dụng; timeout ở database chỉ là lưới an toàn.

Triệu chứng: hai nguyên nhân. Connection leak (rò connection): mượn connection từ pool, mở transaction, gặp exception ở nhánh không đóng connection. Hiếm hơn là connection nửa đóng: client chết mà TCP chưa báo cho server.

Tìm: slide gợi ý “tạo báo cáo để tìm transaction bị bỏ rơi”. Trên MySQL 8.4: lấy transaction đang mở mà connection đang Sleep, kèm câu lệnh cuối cùng để lần ra đoạn code.

Truy vấn báo cáo transaction bị bỏ rơi trên MySQL 8.4 và kết quả thử
SELECT t.trx_mysql_thread_id AS conn_id, p.user, p.host,
       p.time AS idle_s,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_s,
       t.trx_rows_locked, t.trx_rows_modified,
       s.sql_text AS last_statement
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
JOIN performance_schema.threads th ON th.processlist_id = t.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_current s ON s.thread_id = th.thread_id
WHERE p.command = 'Sleep'
  AND TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 10
ORDER BY t.trx_started;

Kết quả thử với một session UPDATE rồi để yên:

conn_id | idle_s | trx_age_s | trx_rows_locked | last_statement
    336 |      3 |         3 |               1 | UPDATE account_balance SET balance=balance-1 WHERE account_number=1

Sửa: dùng try-with-resources hoặc để Spring quản lý transaction. Bật leakDetectionThreshold (ví dụ 30 giây) để HikariCP in stack trace nơi mượn connection mà chưa trả. Biện pháp cuối cùng là KILL <conn_id>, nhưng InnoDB sẽ rollback toàn bộ thay đổi của nó, có thể mất thời gian.

Timeout ở đâu: không có một nút duy nhất. Ứng dụng, pool, database và giám sát mỗi tầng bắt một kiểu lỗi; chỉ giám sát bắt được mọi kiểu nhưng không chặn.

Timeout ở từng tầng: tham số cụ thể và cách đặt theo user

2. Mấy chi tiết hay bị nhầm

Ý chính: SET TRANSACTION không có SESSION chỉ áp cho một transaction, autocommit bật mặc định, và transaction không thay được lock.

Phạm vi của SET TRANSACTION trên MySQL, PostgreSQL và với connection pool
  • Autocommit bật mặc định ở MySQL (và mặc định của JDBC): mỗi câu lệnh là một transaction riêng. START TRANSACTION/BEGIN tạm tắt nó tới khi COMMIT/ROLLBACK. Trên MySQL, DDL như ALTER TABLE gây commit ngầm transaction đang mở.
  • Transaction và locking giải hai bài toán khác nhau. Transaction lo tính nguyên tử và cách một đơn vị công việc nhìn dữ liệu (isolation); lock lo thứ tự khi nhiều đơn vị cùng sửa một thứ. Transaction ở RR vẫn có thể bán âm kho nếu đọc snapshot rồi ghi theo giá trị đã đọc. Phần locking ở phần 3 và phần 4.

Bài tập

Bài 1: Báo cáo transaction bị bỏ rơi

Đề: viết truy vấn liệt kê transaction đang mở mà client không làm gì, đủ thông tin để lần ra code.

Lời giải Bài 1: bản PostgreSQL và cách chỉ ra service bị leak

Truy vấn ở mục 1.4 cho MySQL. Bản PostgreSQL:

SELECT pid, usename, application_name, client_addr,
       now() - xact_start  AS trx_age,
       now() - state_change AS idle_for,
       left(query, 80)      AS last_statement
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '10 seconds'
ORDER BY xact_start;

Đặt application_name khác nhau cho từng service (tham số JDBC ApplicationName với PostgreSQL, connectionAttributes với MySQL) thì báo cáo chỉ thẳng ra service nào leak. Chạy truy vấn định kỳ và đẩy số dòng thành metric để cảnh báo.

Bài 2: Chia nhỏ một transaction lớn

Đề (tự đặt thêm): bảng orders 20 triệu dòng, cần chuyển mọi đơn trước 2024 sang ARCHIVED mà không làm trễ replica và không khoá bảng.

Lời giải Bài 2: vòng lặp chia lô theo khoá chính

Vòng lặp từ ứng dụng, mỗi lô một transaction ngắn, đi theo khoá chính để mỗi lô chỉ khoá đúng các dòng nó sửa:

long lastId = 0;
while (true) {
    long from = lastId;
    List<Long> ids = jdbc.queryForList(
        "SELECT id FROM orders WHERE id > ? AND created_at < '2024-01-01' AND status <> 'ARCHIVED' ORDER BY id LIMIT 5000",
        Long.class, from);
    if (ids.isEmpty()) break;
    tx.executeWithoutResult(s -> jdbc.update(
        "UPDATE orders SET status = 'ARCHIVED' WHERE id BETWEEN ? AND ? AND created_at < '2024-01-01' AND status <> 'ARCHIVED'",
        ids.getFirst(), ids.getLast()));
    lastId = ids.getLast();
    Thread.sleep(50);   // nhường replica và các request khác
}
  • Điều kiện được lặp lại trong UPDATE vì giữa lúc đọc id và lúc update, dòng có thể đã đổi.
  • Lô theo khoảng id liên tục (BETWEEN) cho mỗi transaction một vùng khoá gọn.
  • Theo dõi độ trễ replica và tự giảm kích thước lô khi trễ tăng.

Đoạn code này viết theo JdbcTemplate/TransactionTemplate của Spring và chưa chạy; vòng lặp chia lô cùng ý tưởng đã chạy trong bài xoá dữ liệu cũ.

Ghi nhớ

  • Kể được bốn kiểu transaction gây hại và cách sửa từng kiểu.
  • Giải thích được vì sao gọi HTTP trong @Transactional làm cạn pool, và tách nó ra thế nào.
  • Biết truy vấn nào tìm transaction mở lâu, ai chặn ai, và transaction bị bỏ rơi trên MySQL lẫn PostgreSQL.
  • Biết vì sao connection bị leak giữ transaction tới 8 giờ, và công cụ nào bắt được nó.
  • Kể được timeout ở bốn tầng và vì sao nên đặt theo user.

Câu hỏi phỏng vấn

Junior

Autocommit là gì, và vì sao cần biết nó bật mặc định?

Autocommit bật thì mỗi câu lệnh là một transaction riêng và commit ngay; MySQL và JDBC mặc định bật.

Muốn nhiều câu thành một đơn vị thì phải START TRANSACTION/BEGIN (hoặc setAutoCommit(false), hoặc @Transactional). Quên điều này dẫn tới việc tưởng hai câu UPDATE nguyên tử với nhau trong khi thực ra không.

Một transaction chỉ có câu SELECT thì có gây hại được không?

Có. Ở REPEATABLE READ nó giữ một snapshot, chặn purge (InnoDB) và VACUUM (PostgreSQL).

Chừng nào còn mở, InnoDB không purge được undo log và PostgreSQL không VACUUM được dead tuple mới hơn snapshot đó. Nếu là SELECT ... FOR UPDATE thì nó còn giữ lock. Vì vậy transaction chỉ đọc cũng phải ngắn, và báo cáo dài nên chạy trên replica.

Mid

Transaction “idle in transaction” là gì và vì sao nguy hiểm?

Là transaction đã mở nhưng client không gửi câu nào; nó vẫn giữ lock và snapshot.

Thường do ứng dụng làm việc khác giữa các câu (gọi HTTP) hoặc connection bị leak. Nó chặn người ghi khác, chặn VACUUM (PostgreSQL) hay purge (InnoDB). Tìm bằng pg_stat_activity.state hoặc innodb_trx + processlist Sleep; chặn bằng idle_in_transaction_session_timeout và sửa code.

Làm sao sửa một câu UPDATE hàng triệu dòng đang làm replica trễ?

Chia lô theo khoá chính, vài nghìn dòng một transaction, vì thường không cần nguyên tử cả triệu dòng.

Lặp lại điều kiện trong mỗi lô để chạy lại an toàn, nghỉ giữa các lô và theo dõi độ trễ replica. Cái giá là mất tính nguyên tử toàn cục, nên thao tác phải idempotent.

SET TRANSACTION ISOLATION LEVEL có tác dụng tới đâu?

Trên MySQL, không có từ khoá thì chỉ cho transaction kế tiếp; SESSION cho cả session; GLOBAL cho session mới.

Trên PostgreSQL phải đặt bên trong transaction (hoặc BEGIN ISOLATION LEVEL ...), cho cả session thì dùng SET SESSION CHARACTERISTICS. Với connection pool, đổi ở mức session mà không đổi lại sẽ ảnh hưởng request sau; @Transactional(isolation = ...) của Spring tự khôi phục.

Senior

Một service thỉnh thoảng làm cả database chậm vài phút, rồi tự hết. Bạn nghi điều gì và kiểm tra ra sao?

Nghi một transaction mở lâu theo chu kỳ (báo cáo, batch, hoặc request gọi API chậm bên trong transaction) đang giữ lock hoặc snapshot.

  • Kiểm tra lúc sự cố: sys.innodb_lock_waits (ai chặn ai), innodb_trx (tuổi, số dòng khoá), HLL trên dashboard có dạng răng cưa trùng thời điểm không; trên PostgreSQL là pg_stat_activity và pg_locks.
  • Sửa: rút transaction lại quanh phần DB, chia lô cho batch, đặt timeout theo user, chạy báo cáo dài trên replica.
Vì sao kill một transaction lớn trên MySQL có thể không giải quyết được sự cố ngay?

Vì InnoDB phải rollback bằng cách áp ngược từng bản ghi undo, và vẫn giữ lock trong lúc đó.

Transaction đã sửa hàng triệu dòng trong một giờ có thể mất thời gian tương đương để rollback. Restart server cũng không giúp: sau khi khởi động, InnoDB vẫn phải rollback nốt. Vì vậy cách đúng là phòng từ trước: chia lô, giới hạn kích thước transaction, và cân nhắc kỹ trước khi kill (có khi để nó chạy xong còn nhanh hơn).

Bạn đặt timeout ở những tầng nào để chặn transaction gây hại?

Ở cả bốn tầng: ứng dụng, pool, database và giám sát; mỗi tầng bắt một kiểu lỗi, không tầng nào đủ một mình.

  • Ứng dụng: @Transactional(timeout) và timeout cho HTTP client.
  • Pool: leakDetectionThreshold của HikariCP để thấy connection bị giữ quá lâu.
  • Database: innodb_lock_wait_timeout/lock_timeout cho chờ lock, statement_timeout hoặc max_execution_time cho từng câu, idle_in_transaction_session_timeout trên PostgreSQL; đặt theo user để web app thấp, job cao hơn.
  • Giám sát: cảnh báo theo tuổi transaction già nhất và HLL/dead tuple.

Từ vựng tiếng Anh

Term Phát âm Nghĩa Ví dụ câu tiếng Anh dùng trong phỏng vấn
long-running transaction /ˌlɔːŋˈrʌn.ɪŋ trænˈzæk.ʃən/ transaction chạy lâu “Most of our lock waits came from one long-running transaction.”
stalled transaction /stɔːld trænˈzæk.ʃən/ transaction đứng chờ giữa các câu “The transaction stalled while the app called the payment API.”
abandoned transaction /əˈbæn.dənd trænˈzæk.ʃən/ transaction bị bỏ rơi “We wrote a report to find abandoned transactions every minute.”
idle in transaction /ˈaɪ.dəl ɪn trænˈzæk.ʃən/ transaction mở mà không chạy gì “We set an idle-in-transaction timeout to kill abandoned sessions.”
connection leak /kəˈnek.ʃən liːk/ rò connection “The abandoned transactions came from a connection leak in an error path.”
lock wait timeout /lɑːk weɪt ˈtaɪm.aʊt/ thời gian chờ khoá tối đa “Fifty seconds is far too long a lock wait timeout for a web request.”
replication lag /ˌrep.lɪˈkeɪ.ʃən læɡ/ độ trễ replica “One huge transaction caused minutes of replication lag.”
history list length /ˈhɪs.tər.i lɪst leŋθ/ số undo chưa dọn “A growing history list length usually means a transaction was left open.”

Ghi chép dựa trên khoá Advanced Backend (Ronin Engineer), viết lại theo cách hiểu của mình.