Chương 6. Database, SQL và JPA/Hibernate

Phần lớn sự cố production tôi từng xử lý nằm ở tầng dữ liệu: query chậm vì thiếu index, N+1 làm API từ 50ms thành 5 giây, hai người cùng mua món hàng cuối cùng, connection pool cạn kiệt. Vì vậy tôi dành nhiều thời gian nhất cho phần này. Tôi không cần bạn thuộc cú pháp, tôi cần bạn đọc được EXPLAIN, biết Hibernate sinh ra câu SQL gì, và biết chọn cơ chế khóa phù hợp.

Người phỏng vấn muốn kiểm tra

• Viết SQL thực chiến: JOIN, GROUP BY/HAVING, tìm trùng, top N mỗi nhóm bằng window function.
• Hiểu index ở mức cấu trúc: B-Tree, composite index, covering index, khi nào index bị bỏ qua.
• Nắm isolation level, anomaly, MVCC, và khác biệt mặc định giữa MySQL và PostgreSQL.
• Chọn đúng optimistic/pessimistic lock, xử lý deadlock.
• Hiểu persistence context, dirty checking, lazy loading, N+1, batch insert và connection pool.

6.1 SQL cốt lõi

Nhắc lại kiến thức

Chương này xây trên SQL cơ bản ở N2.1–N2.3 (ràng buộc, SELECT, JOIN, GROUP BY, subquery) và transaction ở N2.4. Nhắc nhanh: JOIN ghép dòng của hai bảng theo điều kiện trong ON; GROUP BY gom nhiều dòng có cùng giá trị thành một nhóm để tính hàm tổng hợp như COUNT, SUM.

Bộ dữ liệu mẫu dùng xuyên suốt chương

Để mọi query trong chương có kết quả cụ thể, tôi dùng chung một bộ dữ liệu nhỏ gồm khách hàng, đơn hàng và nhân viên. Bạn chép hai khối dưới vào SQLite, MySQL 8 hoặc PostgreSQL là chạy được.

CREATE TABLE customers (id BIGINT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    status      VARCHAR(20) NOT NULL, -- NEW, PAID, CANCELLED
    total       INT NOT NULL,
    created_at  TIMESTAMP NOT NULL
);
CREATE TABLE employees (
    id            BIGINT PRIMARY KEY,
    name          VARCHAR(50) NOT NULL,
    department_id BIGINT NOT NULL,
    salary        INT NOT NULL
);

INSERT INTO customers VALUES (1, 'An'), (2, 'Bình'), (3, 'Chi');
INSERT INTO orders VALUES
    (101, 1, 'PAID', 300, '2026-09-01 09:00'), (102, 1, 'NEW', 150, '2026-09-01 10:00'),
    (103, 2, 'PAID', 200, '2026-09-02 08:00'), (104, 2, 'PAID', 500, '2026-09-02 08:00'),
    (105, 1, 'CANCELLED', 80, '2026-09-03 07:30');
INSERT INTO employees VALUES
    (1, 'Lan', 10, 5000), (2, 'Minh', 10, 5000), (3, 'Nga', 10, 4000), (4, 'Oanh', 10, 3000),
    (5, 'Phúc', 10, 2000), (6, 'Quân', 20, 4500), (7, 'Sơn', 20, 3500);

Giải thích: Khối đầu tạo ba bảng, khối sau nạp dữ liệu. Bảng orders trông như sau:

id	Khách (customer_id)	status, total	created_at
101	An (1)	PAID, 300	2026-09-01 09:00
102	An (1)	NEW, 150	2026-09-01 10:00
103	Bình (2)	PAID, 200	2026-09-02 08:00
104	Bình (2)	PAID, 500	2026-09-02 08:00
105	An (1)	CANCELLED, 80	2026-09-03 07:30

Dữ liệu được cố ý chọn để lộ các bẫy hay gặp:

• Khách Chi chưa có đơn nào, dùng để thấy khác biệt giữa INNER JOIN và LEFT JOIN.
• Đơn 103 và 104 trùng created_at, dùng để thấy vì sao phân trang keyset cần cột phụ id.
• Nhân viên Lan và Minh cùng lương 5000 ở phòng 10, dùng để phân biệt ROW_NUMBER, RANK, DENSE_RANK.

JOIN

Loại	Kết quả
INNER JOIN	Chỉ các dòng khớp ở cả hai bảng
LEFT JOIN	Mọi dòng bảng trái, bảng phải không khớp thì NULL
RIGHT JOIN	Ngược lại LEFT JOIN, thực tế ít dùng
FULL OUTER JOIN	Mọi dòng của cả hai bảng; MySQL không hỗ trợ, phải UNION hai LEFT JOIN
CROSS JOIN	Tích Descartes, mọi cặp dòng

Bẫy hay gặp: LEFT JOIN orders o ... WHERE o.status = 'PAID' biến LEFT JOIN thành INNER JOIN, vì các dòng NULL bị WHERE loại. Muốn giữ khách hàng chưa có đơn, đặt điều kiện vào ON.

Ví dụ: đặt điều kiện ở ON hay ở WHERE

Yêu cầu: liệt kê mọi khách hàng kèm các đơn đã thanh toán; khách chưa có đơn PAID vẫn phải hiện ra. Hai câu dưới chỉ khác chỗ đặt điều kiện status:

-- Wrong: the WHERE filter removes customers without a PAID order
SELECT c.name, o.id, o.status
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PAID';

-- Right: the filter lives in ON, unmatched customers keep NULL columns
SELECT c.name, o.id, o.status
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID';

Kết quả trên dữ liệu mẫu:

name	id	status	Câu nào trả dòng này
An	101	PAID	Cả hai câu
Bình	103	PAID	Cả hai câu
Bình	104	PAID	Cả hai câu
Chi	NULL	NULL	Chỉ câu thứ hai

Giải thích: LEFT JOIN sinh dòng Chi | NULL | NULL vì Chi không có đơn nào khớp. Ở câu thứ nhất, WHERE chạy sau JOIN và so NULL = 'PAID'; trong SQL, so sánh với NULL cho kết quả UNKNOWN chứ không phải TRUE, nên dòng của Chi bị loại và kết quả giống hệt INNER JOIN. Ở câu thứ hai, điều kiện nằm trong ON nên nó chỉ quyết định "đơn nào được ghép vào", còn mọi dòng của bảng bên trái luôn được giữ. Quy tắc nhớ: với LEFT JOIN, điều kiện lên bảng bên phải đặt ở ON; điều kiện lên bảng bên trái đặt ở WHERE.

WHERE, GROUP BY, HAVING và thứ tự thực thi logic

Thứ tự thực thi logic: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Vì vậy WHERE lọc từng dòng trước khi gom nhóm và không dùng được hàm tổng hợp; HAVING lọc nhóm sau khi gom. Điều kiện không liên quan đến aggregate nên đặt ở WHERE để giảm dữ liệu sớm. MySQL từ 5.7 bật ONLY_FULL_GROUP_BY: cột trong SELECT phải nằm trong GROUP BY hoặc trong hàm tổng hợp.

Bài toán hay gặp cả khi phỏng vấn lẫn khi dọn dữ liệu thật: bảng users lỡ có email trùng vì thiếu ràng buộc UNIQUE, cần liệt kê các email trùng rồi xóa bản thừa.

-- Find duplicated emails
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1; -- keep only groups with 2 or more rows

-- Delete duplicates, keep the smallest id (MySQL 8+, PostgreSQL)
DELETE FROM users
WHERE id IN (
    SELECT id FROM (
        -- number rows 1, 2, 3... per email, the smallest id gets 1
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM users
    ) t -- wrap in derived table t to avoid MySQL error 1093
    WHERE t.rn > 1 -- every row from the 2nd one is a duplicate
);

Giải thích: Giả sử bảng có các dòng (1, a@x.vn), (2, b@x.vn), (3, a@x.vn), (4, a@x.vn).

• Query thứ nhất gom theo email rồi dùng HAVING lọc nhóm, trả về đúng một dòng a@x.vn | 3. Không viết được WHERE COUNT(*) > 1 vì WHERE chạy trước khi gom nhóm.
• Query thứ hai: subquery bên trong đánh số rn theo từng email, được id 1 → 1, id 3 → 2, id 4 → 3, id 2 → 1. Lọc rn > 1 còn id 3 và 4, nên DELETE xóa hai dòng này và giữ id 1, id 2. Phải lọc rn ở tầng ngoài vì window function được tính sau WHERE; lớp bọc t còn giúp tránh lỗi 1093 của MySQL ("không được SELECT từ chính bảng đang DELETE").
• Sau khi dọn trùng, thêm UNIQUE constraint; kiểm tra trùng ở tầng Java không chống được race condition.

Ví dụ: WHERE và HAVING chạy trên dữ liệu mẫu

Yêu cầu: với mỗi khách, đếm số đơn đã thanh toán và tổng tiền, chỉ giữ khách có tổng từ 300 trở lên.

SELECT c.name, COUNT(*) AS paid_orders, SUM(o.total) AS paid_total
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PAID'          -- row filter, runs before grouping
GROUP BY c.id, c.name
HAVING SUM(o.total) >= 300;      -- group filter, runs after grouping

name	paid_orders	paid_total
An	1	300
Bình	2	700

Giải thích: Đi theo đúng thứ tự thực thi logic:

1. FROM ... JOIN ghép mỗi đơn với khách của nó (5 dòng; Chi không có đơn nên không có dòng nào vì đây là INNER JOIN).
2. WHERE o.status = 'PAID' giữ lại đơn 101, 103, 104.
3. GROUP BY tạo hai nhóm: An gồm {101}, Bình gồm {103, 104}.
4. HAVING SUM(o.total) >= 300 xét từng nhóm: An 300, Bình 700, cả hai đều đạt. Nếu đổi thành >= 500 thì chỉ còn Bình.
5. SELECT tính các cột cuối cùng cho mỗi nhóm còn lại.

Window function: top N mỗi nhóm

Đề bài: lấy 3 nhân viên lương cao nhất của mỗi phòng ban. GROUP BY không làm được vì nó gộp mỗi phòng thành một dòng; window function thì giữ nguyên từng dòng và gắn thêm thứ hạng cho mỗi dòng.

SELECT department_id, name, salary
FROM (
    SELECT e.*,
           -- rank restarts at 1 in each department, highest salary is rank 1
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees e
) t
WHERE t.rnk <= 3; -- filter in the outer query: WHERE cannot use window functions

Giải thích: PARTITION BY department_id chia dữ liệu thành từng phòng, ORDER BY salary DESC sắp xếp trong mỗi phòng, rồi DENSE_RANK() gán hạng cho từng dòng mà không gộp dòng nào. Query ngoài giữ các dòng có hạng từ 1 đến 3. Ví dụ phòng 10 có lương 5000, 5000, 4000, 3000, 2000: DENSE_RANK cho hạng 1, 1, 2, 3, 4 nên trả về 4 người (đủ 3 mức lương cao nhất); nếu đổi sang ROW_NUMBER (1, 2, 3, 4, 5) thì trả đúng 3 người; RANK (1, 1, 3, 4, 5) cũng trả 3 người. Vì vậy phải hỏi lại yêu cầu "top 3 người" hay "top 3 mức lương" trước khi chọn hàm.

ROW_NUMBER đánh số liên tục kể cả khi bằng nhau (lấy đúng 3 dòng), RANK cho đồng hạng rồi nhảy số (1, 1, 3), DENSE_RANK đồng hạng không nhảy số (1, 1, 2). MySQL chỉ có window function từ 8.0.

Chạy thử window function trên dữ liệu mẫu

Nói gọn: window function là hàm tính trên một "cửa sổ" gồm nhiều dòng liên quan (ví dụ các dòng cùng phòng ban) nhưng vẫn trả về một giá trị cho mỗi dòng, không gộp dòng như GROUP BY. Query dưới đây tính cả ba hàm xếp hạng cạnh nhau trên bảng employees:

SELECT name, department_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drk
FROM employees
ORDER BY department_id, salary DESC, id;

Nhân viên (phòng, lương)	ROW_NUMBER	RANK	DENSE_RANK
Lan (10, 5000)	1	1	1
Minh (10, 5000)	2	1	1
Nga (10, 4000)	3	3	2
Oanh (10, 3000)	4	4	3
Phúc (10, 2000)	5	5	4
Quân (20, 4500)	1	1	1
Sơn (20, 3500)	2	2	2

Giải thích: Số thứ tự bắt đầu lại từ 1 ở phòng 20 vì PARTITION BY department_id. Lan và Minh cùng 5000: RANK và DENSE_RANK cho cả hai hạng 1; ở người kế tiếp (Nga), RANK nhảy lên 3 (vì đã có 2 người đứng trước) còn DENSE_RANK chỉ lên 2. ROW_NUMBER bắt buộc phân định nên ai là 1, ai là 2 giữa Lan và Minh là không xác định nếu chỉ sắp theo lương; muốn kết quả ổn định giữa các lần chạy, thêm cột phụ: ORDER BY salary DESC, id.

Áp query top 3 ở trên (dùng DENSE_RANK, rnk <= 3) vào dữ liệu này:

department_id	name	salary
10	Lan	5000
10	Minh	5000
10	Nga	4000
10	Oanh	3000
20	Quân	4500
20	Sơn	3500

Phòng 10 trả 4 người vì có 3 mức lương cao nhất là 5000, 4000, 3000; phòng 20 chỉ có 2 người nên trả cả 2. Nếu đổi sang ROW_NUMBER, phòng 10 trả đúng 3 người (Lan, Minh, Nga).

DELETE, TRUNCATE, DROP

Lệnh	Bản chất	Ghi chú
DELETE	DML, xóa từng dòng, có WHERE	Kích hoạt trigger, rollback được, chậm với bảng lớn
TRUNCATE	DDL, giải phóng toàn bộ trang dữ liệu	Không WHERE, không trigger xóa dòng, thường reset AUTO_INCREMENT
DROP	DDL, xóa cả cấu trúc bảng	Mất cả index, constraint

Chi tiết phụ thuộc DB: MySQL/Oracle TRUNCATE gây implicit commit nên không rollback được, nhưng PostgreSQL TRUNCATE là transactional và rollback được; PostgreSQL chỉ reset sequence khi dùng TRUNCATE ... RESTART IDENTITY. TRUNCATE bị chặn nếu bảng đang được foreign key tham chiếu (MySQL) hoặc cần CASCADE (PostgreSQL).

6.2 Index

Nhắc lại kiến thức

N2.4 đã giới thiệu index như mục lục sách: thay vì lật từng trang (quét toàn bảng), DB tra mục lục để nhảy thẳng tới dòng cần tìm. Primary key và unique tự có index. Mục này đi sâu vào cấu trúc bên trong và cách đọc EXPLAIN.

MySQL InnoDB và PostgreSQL dùng B+Tree làm index mặc định: cây cân bằng, dữ liệu ở lá được sắp xếp và liên kết, nên tìm theo giá trị bằng, tìm theo khoảng và ORDER BY đều hiệu quả với độ phức tạp O(log n). Trong InnoDB, primary key là clustered index (dòng dữ liệu nằm ngay trong lá của cây PK); secondary index lưu giá trị PK, nên tra theo secondary index phải thêm một bước quay về cây PK (lookup). Đây là lý do PK nên ngắn và tăng dần; UUID v4 ngẫu nhiên làm cây bị chia trang liên tục khi insert.

B+Tree minh họa bằng chữ

Hình dưới là index trên orders(created_at) của dữ liệu mẫu, rút gọn để mỗi nút chỉ chứa vài key. Nút trên cùng là gốc, các nút dưới cùng là lá; lá chứa key đã sắp xếp kèm con trỏ tới dòng, và các lá nối với nhau thành một chuỗi.

                         [ 09-02 08:00 ]                          <- root
                        /               \
  [ 09-01 09:00 | 09-01 10:00 ] <-> [ 09-02 08:00 | 09-02 08:00 | 09-03 07:30 ]   <- leaves
        |             |                   |             |             |
     row 101       row 102             row 103       row 104       row 105

Giải thích: Ba thao tác thường gặp đi trên cây này như sau:

• Tìm bằng created_at = '2026-09-02 08:00': ở gốc, giá trị cần tìm không nhỏ hơn 09-02 08:00 nên rẽ phải; tới lá thứ hai, đọc được hai dòng 103 và 104. Chỉ đọc 2 nút thay vì cả bảng.
• Tìm theo khoảng created_at >= '2026-09-01 10:00': seek xuống lá chứa 09-01 10:00, sau đó chỉ việc đi dọc chuỗi lá sang phải cho tới hết khoảng. Đây là lý do B+Tree hợp với BETWEEN, >, <.
• ORDER BY created_at: lá đã sắp sẵn, đọc lần lượt là có thứ tự, không cần sắp xếp lại.

Trong thực tế mỗi nút là một trang (InnoDB mặc định 16KB) chứa hàng trăm key, nên bảng 10 triệu dòng chỉ cao khoảng 3–4 tầng: tìm một dòng tốn 3–4 lần đọc trang thay vì quét 10 triệu dòng. Với secondary index trong InnoDB, lá không trỏ tới vị trí vật lý mà lưu giá trị PK (ví dụ 103); DB lấy PK đó tra tiếp cây clustered index để lấy đủ các cột, nên một lần tìm thực chất là hai lần đi cây. PK tăng dần thì dòng mới luôn chèn vào lá cuối; PK ngẫu nhiên (UUID v4) chèn vào giữa cây, làm các trang đầy bị tách đôi liên tục.

Composite index và leftmost prefix: index (customer_id, status, created_at) được dùng cho customer_id = ?, customer_id = ? AND status = ?, và cả ORDER BY created_at sau hai điều kiện bằng đó. Không dùng hiệu quả cho điều kiện chỉ có status = ?. Điều kiện khoảng (>, BETWEEN) trên một cột làm các cột phía sau không còn dùng để seek được, nên cột so sánh bằng đặt trước, cột range đặt sau.

Composite index: query nào dùng được và vì sao

Composite index là index trên nhiều cột. Hãy hình dung một cuốn danh bạ sắp theo họ, trong cùng họ sắp theo tên đệm, trong cùng tên đệm sắp theo tên. Tra "họ Nguyễn" rất nhanh; tra "họ Nguyễn, đệm Văn" càng nhanh; nhưng tra "tất cả người tên An" thì phải lật cả cuốn, vì người tên An nằm rải rác khắp các họ. Đó chính là quy tắc leftmost prefix: index chỉ seek được khi điều kiện bắt đầu từ cột trái nhất và đi liên tục sang phải.

Với index idx_orders_cust_status_created (customer_id, status, created_at):

Điều kiện trong query	Dùng index?	Vì sao
customer_id = 1	Có	Là cột trái nhất
customer_id = 1 AND status = 'PAID'	Có, seek 2 cột	Tiền tố liên tục từ trái
status = 'PAID' AND customer_id = 1	Có, seek 2 cột	Thứ tự viết trong WHERE không quan trọng, optimizer tự sắp
customer_id = 1 AND status = 'PAID' AND created_at >= '2026-09-01'	Có, cả 3 cột	Hai cột bằng, cột range nằm cuối
status = 'PAID'	Không seek được	Thiếu cột trái nhất customer_id
created_at >= '2026-09-01'	Không seek được	Thiếu cột trái nhất
customer_id = 1 AND created_at >= '2026-09-01'	Chỉ seek theo customer_id	Thiếu status ở giữa nên created_at chỉ dùng để lọc sau
customer_id > 1 AND status = 'PAID'	Chỉ seek theo customer_id	Range ở cột đầu, các cột sau không seek được
customer_id = 1 ORDER BY status, created_at	Có, không cần sort	Trong cùng customer_id, index đã sắp theo đúng thứ tự đó
customer_id = 1 AND status = 'PAID' ORDER BY created_at DESC	Có, không cần sort	Đọc ngược index
SELECT status, created_at ... WHERE customer_id = 1	Có, covering	Mọi cột cần đều nằm trong index

Giải thích: Tôi đã kiểm chứng bằng EXPLAIN QUERY PLAN trên SQLite: câu status = 'PAID' ra SCAN orders (quét toàn bảng), câu customer_id = 1 AND status = 'PAID' ra SEARCH orders USING INDEX ... (customer_id=? AND status=?), câu chỉ lấy cột trong index ra USING COVERING INDEX. MySQL và PostgreSQL cho kết quả tương tự. Ngoại lệ cần biết: MySQL 8.0.13+ có skip scan, đôi khi dùng được index cho status = 'PAID' khi cột đầu có rất ít giá trị khác nhau; đừng dựa vào nó khi thiết kế index.

Covering index: khi mọi cột query cần đều nằm trong index, DB không cần đọc bảng. MySQL hiện Using index trong EXPLAIN, PostgreSQL hiện Index Only Scan.

Khi nào index bị bỏ qua? Có index nhưng optimizer không dùng được là tình huống rất hay gặp. Các query dưới đây đều lọc trên cột đã có index (orders.created_at, products.name, customers.phone) nhưng vẫn quét toàn bảng, trừ câu đã được viết lại:

-- Function wraps the column: the index on created_at cannot be used
SELECT * FROM orders WHERE DATE(created_at) = '2026-09-01'; -- DATE() must be computed for every row
-- Rewrite as a range to use the index
SELECT * FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'; -- range scan on the index

-- Leading wildcard: no seek possible on a B-Tree
SELECT * FROM products WHERE name LIKE '%phone'; -- LIKE 'phone%' can still use the index

-- phone is VARCHAR but compared to a number: MySQL casts every row, index ignored
SELECT * FROM customers WHERE phone = 912345678; -- fix: phone = '0912345678'

Giải thích: Chạy EXPLAIN cho ba câu "xấu", MySQL đều báo type = ALL (full scan), còn câu viết lại thành khoảng báo type = range. Lý do nằm ở cách B-Tree sắp xếp dữ liệu:

• Hàm bọc cột: cây index được sắp theo giá trị gốc của created_at, không theo DATE(created_at), nên DB không biết seek vào đâu mà phải tính hàm cho từng dòng. Viết lại thành khoảng nửa mở [00:00 ngày 1, 00:00 ngày 2) thì điều kiện so sánh trực tiếp trên cột, vừa dùng được index vừa đúng với giá trị có giờ phút.
• Wildcard ở đầu: B-Tree sắp chuỗi theo tiền tố từ trái sang phải. LIKE 'phone%' biết tiền tố nên seek được; LIKE '%phone' không biết ký tự đầu tiên nên phải duyệt hết.
• Ép kiểu ngầm: khi so cột chuỗi với số, MySQL chuyển giá trị của từng dòng sang số rồi mới so, tương đương với bọc hàm quanh cột nên không seek được. Tệ hơn, '0912345678' và '912345678' đều khớp vì cùng thành một số. Cách sửa là truyền tham số đúng kiểu chuỗi (kể cả khi bind từ Java: dùng String, không dùng Long).

Ngoài ra, index cũng có thể bị bỏ qua khi: cột có độ chọn lọc thấp (optimizer thấy quét bảng rẻ hơn), OR giữa các cột khác nhau, so sánh cột khác collation/charset khi JOIN. Cần tìm theo biểu thức thì dùng functional index (MySQL 8.0.13+, PostgreSQL expression index); tìm kiếm văn bản thì dùng full-text search hoặc Elasticsearch.

EXPLAIN: với MySQL, xem cột type (tệ nhất ALL là full scan, rồi index, range, ref, eq_ref, const), key (index được chọn), rows (ước lượng số dòng quét), Extra (Using filesort, Using temporary là tín hiệu cần xem lại). PostgreSQL dùng EXPLAIN ANALYZE để xem thời gian thực tế, phân biệt Seq Scan, Index Scan, Bitmap Heap Scan.

Đọc EXPLAIN qua ví dụ (MySQL 8)

EXPLAIN là lệnh yêu cầu DB cho xem kế hoạch thực thi (execution plan): nó định đọc bảng nào, bằng index nào, ước lượng bao nhiêu dòng, mà không thực sự chạy query. Giả sử bảng orders có khoảng 1 triệu dòng, chưa có index nào ngoài PK, và màn hình "đơn đã thanh toán mới nhất" chạy câu sau. Phần output dưới đây là của MySQL (định dạng dọc \G), minh họa, không chạy được trên SQLite:

EXPLAIN SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 20\G

           id: 1
  select_type: SIMPLE
        table: orders
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 998214
     filtered: 10.00
        Extra: Using where; Using filesort

Giải thích: Đọc từ trên xuống: type: ALL nghĩa là quét toàn bảng; key: NULL là không dùng index nào; rows: 998214 là DB ước lượng phải đọc gần 1 triệu dòng; filtered: 10.00 là chỉ khoảng 10% trong số đó thỏa status = 'PAID'; Using filesort là sau khi lọc phải sắp xếp lại theo created_at trong bộ nhớ hoặc trên đĩa. Nghĩa là để trả 20 dòng, DB đọc 1 triệu dòng và sắp xếp khoảng 100 nghìn dòng.

Thêm index CREATE INDEX idx_orders_status_created ON orders (status, created_at); rồi chạy lại EXPLAIN:

         type: ref
possible_keys: idx_orders_status_created
          key: idx_orders_status_created
      key_len: 82
          ref: const
         rows: 99821
     filtered: 100.00
        Extra: Backward index scan

Giải thích: type: ref là tra index theo giá trị bằng; key_len: 82 cho biết chỉ cột status được dùng để seek (VARCHAR(20) utf8mb4 = 20 × 4 byte + 2 byte độ dài); ref: const là so với hằng số 'PAID'. Using filesort đã biến mất, thay bằng Backward index scan: DB đọc ngược index vốn đã sắp theo created_at. Cột rows vẫn là 99821 vì nó là ước lượng và không trừ LIMIT; thực tế DB dừng sau khi đọc đủ 20 dòng.

Cột	Ý nghĩa	Dấu hiệu cần xem lại
type	Cách truy cập bảng: const, eq_ref, ref, range, index, ALL (từ tốt tới tệ)	ALL hoặc index trên bảng lớn
key, possible_keys	Index được chọn và các index có thể dùng	key là NULL dù có possible_keys
key_len	Số byte của index được dùng, suy ra dùng mấy cột của composite index	Ngắn hơn kỳ vọng
rows, filtered	Ước lượng số dòng đọc, và phần trăm còn lại sau lọc	rows lớn nhưng filtered thấp
Extra	Using index (covering, tốt), Using where, Using filesort, Using temporary	filesort, temporary trên nhiều dòng

Trên PostgreSQL, EXPLAIN ANALYZE chạy thật query và in cả ước lượng lẫn số thực tế:

Limit  (cost=0.43..8.10 rows=20 width=40) (actual time=0.031..0.058 rows=20 loops=1)
  ->  Index Scan Backward using idx_orders_status_created on orders
        (cost=0.43..38291.12 rows=99821 width=40) (actual time=0.030..0.054 rows=20 loops=1)
        Index Cond: ((status)::text = 'PAID'::text)
Planning Time: 0.120 ms
Execution Time: 0.075 ms

Giải thích: Mỗi dòng có mũi tên là một bước của plan, đọc từ trong ra ngoài: Index Scan Backward đọc index ngược rồi Limit cắt 20 dòng. cost là chi phí ước lượng (đơn vị tương đối), actual time là thời gian thật tính bằng mili giây, rows= trong ngoặc thứ nhất là ước lượng còn trong ngoặc thứ hai là số thật. Khi hai số rows lệch nhau hàng chục lần, thống kê của bảng đã cũ và cần chạy ANALYZE orders. Vì EXPLAIN ANALYZE chạy thật, với câu UPDATE/DELETE hãy bọc trong BEGIN; ... ROLLBACK;.

Index không miễn phí: mỗi INSERT/UPDATE/DELETE phải cập nhật mọi index liên quan và tốn thêm dung lượng. Đừng index mọi cột; index theo query thực tế, và xóa index không dùng.

Phân trang: offset vs keyset

Màn hình danh sách đơn hàng sắp theo thời gian mới nhất, mỗi trang 20 dòng. Hai câu dưới đây cùng lấy trang thứ 5001, một câu bằng offset và một câu bằng keyset:

-- Offset: the DB still reads and discards the first 100000 rows
SELECT id, created_at, total FROM orders
ORDER BY created_at DESC, id DESC -- id breaks ties on equal created_at
LIMIT 20 OFFSET 100000;

-- Keyset: seek straight to the position via index (created_at, id)
SELECT id, created_at, total FROM orders
WHERE created_at < ? OR (created_at = ? AND id < ?) -- ? = created_at, created_at, id of the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

Giải thích: Với offset, DB phải duyệt theo thứ tự sắp xếp đủ 100020 dòng rồi vứt bỏ 100000 dòng đầu, nên chi phí tăng tuyến tính theo số trang: trang 1 mất vài mili giây, trang 5001 có thể mất vài giây. Với keyset, client gửi lên created_at và id của dòng cuối cùng ở trang trước; nhờ index (created_at, id), DB seek thẳng tới vị trí đó trên cây rồi đọc tiếp 20 dòng, nên trang nào cũng nhanh như trang đầu. Điều kiện OR (created_at = ? AND id < ?) là bắt buộc: nếu chỉ viết created_at < ?, các đơn trùng thời điểm với dòng cuối trang trước sẽ bị bỏ sót. PostgreSQL cho phép viết gọn bằng so sánh bộ giá trị (created_at, id) < (?, ?).

Offset đơn giản, nhảy trang tùy ý được, nhưng càng về sau càng chậm và dữ liệu bị lệch khi có bản ghi mới chèn vào. Keyset (cursor) ổn định và nhanh ở mọi vị trí, nhưng không nhảy thẳng tới trang 50 được; hợp với infinite scroll, API đồng bộ dữ liệu.

Ví dụ phân trang trên dữ liệu mẫu

Lấy danh sách orders mới nhất, mỗi trang 2 dòng. Trang 1 (LIMIT 2) trả về đơn 105 và 104; dòng cuối trang 1 là (created_at = '2026-09-02 08:00', id = 104), client gửi cặp giá trị này làm cursor để lấy trang 2.

-- Keyset page 2: correct
SELECT id, created_at, total FROM orders
WHERE created_at < '2026-09-02 08:00'
   OR (created_at = '2026-09-02 08:00' AND id < 104)
ORDER BY created_at DESC, id DESC LIMIT 2;

-- Keyset page 2: wrong, missing the tie-breaker on id
SELECT id, created_at, total FROM orders
WHERE created_at < '2026-09-02 08:00'
ORDER BY created_at DESC, id DESC LIMIT 2;

Cách lấy trang 2	Kết quả	Đánh giá
Keyset đúng	103, 102	Đúng
Keyset thiếu id	102, 101	Mất đơn 103 vì trùng created_at với 104
LIMIT 2 OFFSET 2	103, 102	Đúng khi dữ liệu không đổi
LIMIT 2 OFFSET 2 sau khi có đơn 106 mới chèn	104, 103	Đơn 104 hiện lại lần hai

Giải thích: Câu keyset thiếu id bỏ sót đơn 103 vì 103 có cùng thời điểm với dòng cuối trang trước nên không thỏa created_at < .... Với offset, nếu giữa lúc người dùng xem trang 1 và trang 2 có đơn 106 mới được tạo, mọi dòng bị đẩy lùi một vị trí, nên trang 2 bắt đầu bằng đơn 104 mà người dùng đã thấy. Keyset không bị lệch vì nó bám theo giá trị của dòng cuối chứ không theo vị trí. Cả bốn kết quả trên đã được chạy thử bằng SQLite.

6.3 Transaction, ACID, isolation và MVCC

Nhắc lại kiến thức

Transaction gom nhiều lệnh thành một đơn vị "tất cả hoặc không gì cả", và ACID là bốn tính chất của nó (xem N2.4 với ví dụ tăng lương, trừ ngân sách). Trong Spring, transaction thường được mở bằng @Transactional qua proxy (xem 4.5); isolation level đặt được ngay trên annotation, ví dụ @Transactional(isolation = Isolation.REPEATABLE_READ).

• Atomicity: tất cả hoặc không gì cả; DB dùng undo log để hoàn tác khi rollback hoặc khi khởi động lại sau sự cố.
• Consistency: dữ liệu luôn thỏa các ràng buộc (PK, FK, UNIQUE, CHECK). Phần lớn là trách nhiệm của thiết kế schema và ứng dụng.
• Isolation: các transaction đồng thời không thấy trạng thái dở dang của nhau, ở mức do isolation level quy định.
• Durability: đã commit thì không mất khi sập nguồn, nhờ Write-Ahead Logging: thay đổi được ghi tuần tự vào log (redo log của InnoDB, WAL của PostgreSQL) và fsync xuống đĩa trước khi báo commit; trang dữ liệu được ghi sau. Khi khởi động lại, DB replay log. Với MySQL, innodb_flush_log_at_trx_commit=1 mới đảm bảo durability đầy đủ.

Anomaly và isolation level

• Dirty read: đọc dữ liệu chưa commit của transaction khác.
• Non-repeatable read: đọc lại cùng một dòng ra giá trị khác vì transaction khác đã UPDATE và commit.
• Phantom read: chạy lại cùng điều kiện ra tập dòng khác vì transaction khác INSERT/DELETE.
• Lost update: hai transaction cùng đọc rồi ghi đè, cập nhật của một bên biến mất.

Isolation level	Dirty read	Non-repeatable read	Phantom (theo chuẩn SQL)
READ UNCOMMITTED	Có thể	Có thể	Có thể
READ COMMITTED	Không	Có thể	Có thể
REPEATABLE READ	Không	Không	Có thể
SERIALIZABLE	Không	Không	Không

Bảng trên là chuẩn SQL; cách các DB hiện thực trên thực tế mạnh hơn. MySQL InnoDB mặc định REPEATABLE READ: đọc thường (consistent read) dùng snapshot nên không thấy phantom, còn đọc khóa (SELECT ... FOR UPDATE, UPDATE, DELETE) dùng next-key lock để chặn insert vào khoảng. Nhưng InnoDB RR không chặn lost update kiểu đọc rồi ghi đè từ ứng dụng. PostgreSQL mặc định READ COMMITTED (Oracle, SQL Server cũng vậy); REPEATABLE READ của PostgreSQL là snapshot isolation, không có phantom và sẽ báo lỗi serialization khi hai transaction cùng cập nhật một dòng, còn SERIALIZABLE dùng SSI (có thể vẫn cần retry).

Bốn anomaly qua bảng thời gian

Anomaly là hiện tượng sai lệch dữ liệu xảy ra khi hai transaction chạy xen kẽ nhau. Cách dễ hiểu nhất là đặt hai transaction T1, T2 cạnh nhau và đi từng bước theo thời gian. Các ví dụ dùng bảng accounts có dòng id = 1, balance = 100, và bảng product có dòng id = 1, stock = 10.

Dirty read (chỉ xảy ra ở READ UNCOMMITTED):

Bước	T1	T2	Kết quả
1	BEGIN; UPDATE accounts SET balance = 50 WHERE id = 1;		T1 chưa commit
2		SELECT balance FROM accounts WHERE id = 1;	T2 đọc được 50
3	ROLLBACK;		balance trở lại 100
4		Dùng 50 để tính tiếp	T2 dựa trên giá trị chưa từng tồn tại

Từ READ COMMITTED trở lên, bước 2 đọc được 100 vì chỉ thấy dữ liệu đã commit.

Non-repeatable read (xảy ra ở READ COMMITTED):

Bước	T1	T2	Kết quả
1		BEGIN; SELECT balance ... WHERE id = 1;	T2 đọc 100
2	BEGIN; UPDATE accounts SET balance = 70 WHERE id = 1; COMMIT;		Đã commit 70
3		SELECT balance ... WHERE id = 1;	T2 đọc 70: cùng câu, cùng transaction, khác giá trị

Ở REPEATABLE READ, bước 3 vẫn đọc 100 vì T2 dùng lại snapshot của lần đọc đầu.

Phantom read (xảy ra ở READ COMMITTED; chuẩn SQL cho phép cả ở REPEATABLE READ):

Bước	T1	T2	Kết quả
1		BEGIN; SELECT COUNT(*) FROM orders WHERE customer_id = 1 AND status = 'PAID';	T2 đếm được 1
2	INSERT INTO orders VALUES (106, 1, 'PAID', 90, '2026-09-04 10:00'); COMMIT;		Thêm một đơn PAID
3		Chạy lại câu COUNT	T2 đếm được 2: xuất hiện "dòng ma"

Khác non-repeatable read ở chỗ: không dòng cũ nào bị sửa, mà tập dòng thỏa điều kiện thay đổi. REPEATABLE READ của MySQL và PostgreSQL đều trả 1 ở bước 3 nhờ snapshot.

Lost update (xảy ra ở READ COMMITTED và cả REPEATABLE READ của MySQL):

Bước	T1	T2	Kết quả
1	SELECT stock FROM product WHERE id = 1;		T1 đọc 10
2		SELECT stock FROM product WHERE id = 1;	T2 cũng đọc 10
3	UPDATE product SET stock = 9 WHERE id = 1; COMMIT;		Ứng dụng tính 10 - 1 = 9
4		UPDATE product SET stock = 9 WHERE id = 1; COMMIT;	Vẫn là 9, lẽ ra phải là 8

Giải thích: Lost update nguy hiểm vì không DB nào báo lỗi ở READ COMMITTED: cả hai UPDATE đều hợp lệ, chỉ có phép tính "đọc rồi ghi đè" ở tầng ứng dụng là sai. PostgreSQL ở REPEATABLE READ sẽ chặn bước 4 bằng lỗi could not serialize access due to concurrent update. Cách sửa chủ động nằm ở mục "Optimistic vs pessimistic lock" bên dưới: atomic update SET stock = stock - 1, SELECT ... FOR UPDATE, hoặc @Version.

MVCC

Multi-Version Concurrency Control giữ nhiều phiên bản của một dòng. Người đọc nhìn thấy phiên bản phù hợp với snapshot của mình nên đọc không chặn ghi, ghi không chặn đọc; hai transaction cùng ghi một dòng thì vẫn phải chờ khóa. InnoDB dựng lại phiên bản cũ từ undo log; PostgreSQL lưu nhiều bản tuple trong bảng (xmin/xmax) và cần VACUUM dọn. Thời điểm lấy snapshot phụ thuộc isolation level: READ COMMITTED lấy snapshot mới cho mỗi câu lệnh, REPEATABLE READ dùng một snapshot cho cả transaction (InnoDB lấy tại lần đọc đầu tiên, không phải lúc BEGIN). Transaction mở quá lâu làm undo log/tuple cũ không dọn được, DB phình và chậm dần.

Ví dụ: các phiên bản của một dòng

Snapshot là "bức ảnh chụp" trạng thái dữ liệu đã commit tại một thời điểm; transaction đọc theo bức ảnh đó thay vì theo dữ liệu đang thay đổi. Xét dòng product id = 1 qua các thời điểm:

Thời điểm	Sự kiện	Các phiên bản của dòng
10:00:00	Trạng thái ban đầu, do trx 100 ghi từ trước	v1: stock = 10 (đã commit)
10:00:01	R1 (REPEATABLE READ) đọc lần đầu, chụp snapshot	v1
10:00:05	Trx 205 UPDATE stock = 9 rồi commit	v1, v2: stock = 9 (đã commit)
10:00:08	Trx 210 UPDATE stock = 7, chưa commit	v1, v2, v3: stock = 7 (chưa commit)
10:00:10	R1 đọc lại; R2 (READ COMMITTED) đọc	Cả ba phiên bản cùng tồn tại

Giải thích: Lúc 10:00:10, mỗi transaction thấy một giá trị khác nhau, và không ai phải chờ ai:

• R1 (REPEATABLE READ) vẫn thấy 10 (v1), vì snapshot của nó chụp lúc 10:00:01, trước khi v2 được commit.
• R2 (READ COMMITTED) chụp snapshot mới cho câu lệnh lúc 10:00:10, thấy 9 (v2). v3 chưa commit nên bị bỏ qua, nhờ vậy không có dirty read.
• Trx 210 đọc lại dòng của chính nó thì thấy 7 (v3).
• Một transaction thứ tư muốn UPDATE dòng này thì phải chờ trx 210 commit hoặc rollback, vì ghi và ghi vẫn tranh nhau khóa dòng.

Cách lưu phiên bản khác nhau theo DB: PostgreSQL lưu v1, v2, v3 thành các tuple riêng trong bảng, mỗi tuple ghi xmin (trx tạo ra nó) và xmax (trx thay thế nó), ví dụ v1 có xmin = 100, xmax = 205. InnoDB chỉ giữ bản mới nhất trong trang dữ liệu và dựng lại bản cũ bằng cách lần theo chuỗi undo log. Khi không còn snapshot nào cần v1, VACUUM (PostgreSQL) hoặc purge thread (InnoDB) dọn nó đi. Nếu R1 mở transaction suốt một giờ, v1 và mọi phiên bản sinh ra sau đó đều phải giữ lại, đó là lý do transaction dài làm DB phình to.

Optimistic vs pessimistic lock

Bài toán trừ kho: nhiều request cùng đọc stock rồi ghi lại giá trị mới, dễ gây lost update. Đoạn code dưới đây khai báo cả hai cơ chế khóa trên cùng entity Product để so sánh: @Version cho optimistic lock, @Lock trên repository cho pessimistic lock.

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
    private int stock;

    @Version // Hibernate increments version after each successful UPDATE
    private Long version; // Hibernate: UPDATE ... WHERE id = ? AND version = ?
}

public interface ProductRepository extends JpaRepository<Product, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE) // generates SELECT ... FOR UPDATE
    @Query("select p from Product p where p.id = :id")
    Optional<Product> findByIdForUpdate(@Param("id") Long id); // row stays locked until the transaction ends
}

Giải thích: Giả sử sản phẩm id 1 đang có stock = 5, version = 3, và hai transaction T1, T2 cùng muốn trừ 1.

• Optimistic (@Version): T1 và T2 đều đọc được stock = 5, version = 3. T1 commit trước, Hibernate sinh UPDATE product SET stock = 4, version = 4 WHERE id = 1 AND version = 3, cập nhật 1 dòng. T2 commit sau với cùng điều kiện version = 3, nhưng version đã thành 4 nên UPDATE ảnh hưởng 0 dòng; Hibernate ném OptimisticLockException (Spring bọc thành ObjectOptimisticLockingFailureException) và T2 rollback. Không ai bị chờ, nhưng ứng dụng phải đọc lại rồi retry hoặc báo cho user.
• Pessimistic (findByIdForUpdate): T1 chạy select ... from product where id = 1 for update và giữ khóa dòng. T2 gọi cùng câu đó sẽ bị chặn ngay tại SELECT cho tới khi T1 commit, sau đó đọc được stock = 4 mới nhất. Không có xung đột lúc ghi, đổi lại T2 phải chờ. Method này phải được gọi bên trong @Transactional của service; nếu không, query method tự khai báo này không có transaction nào (Spring Data không tự áp @Transactional cho nó), và Hibernate ném TransactionRequiredException ("no transaction is in progress") ngay khi chạy câu FOR UPDATE. Kể cả khi thêm @Transactional lên chính method repository, khóa cũng được nhả ngay khi method trả về, coi như không khóa gì cho đoạn "đọc, tính, ghi" phía sau.

Tiêu chí	Optimistic (@Version)	Pessimistic (FOR UPDATE)
Cơ chế	Không khóa, kiểm tra version lúc ghi	Khóa dòng từ lúc đọc tới commit
Khi xung đột	UPDATE 0 dòng, ném OptimisticLockException	Transaction sau phải chờ
Hợp với	Xung đột hiếm, thao tác dài, form sửa nhiều bước	Xung đột cao, cần chắc chắn trong transaction ngắn
Rủi ro	Phải retry hoặc báo user	Chờ lâu, deadlock, giảm throughput

Đừng quên lựa chọn thứ ba, thường là tốt nhất cho bài toán trừ kho: atomic update UPDATE product SET stock = stock - 1 WHERE id = ? AND stock > 0, kiểm tra số dòng bị ảnh hưởng. Với hàng đợi job, SELECT ... FOR UPDATE SKIP LOCKED (MySQL 8+, PostgreSQL) cho nhiều worker lấy việc không giẫm lên nhau.

Kịch bản từng bước với câu SQL

Ba bảng dưới cùng xử lý tình huống: sản phẩm id 1 còn stock = 1, version = 3, T1 và T2 cùng muốn mua món cuối cùng.

Optimistic lock (Hibernate sinh SQL từ entity có @Version):

Bước	T1	T2	Kết quả
1	SELECT id, stock, version FROM product WHERE id = 1;		Đọc stock 1, version 3
2		Cùng câu SELECT	Cũng đọc stock 1, version 3
3	UPDATE product SET stock = 0, version = 4 WHERE id = 1 AND version = 3; COMMIT;		1 dòng bị ảnh hưởng, T1 mua được
4		UPDATE product SET stock = 0, version = 4 WHERE id = 1 AND version = 3;	0 dòng, Hibernate ném OptimisticLockException, T2 rollback

Pessimistic lock (findByIdForUpdate sinh FOR UPDATE):

Bước	T1	T2	Kết quả
1	SELECT ... FROM product WHERE id = 1 FOR UPDATE;		T1 giữ khóa dòng, đọc stock 1
2		SELECT ... FROM product WHERE id = 1 FOR UPDATE;	T2 bị chặn, đứng chờ
3	UPDATE product SET stock = 0 WHERE id = 1; COMMIT;		T1 nhả khóa
4		SELECT của T2 chạy tiếp	T2 đọc stock 0, báo hết hàng

Atomic update (không đọc trước, để DB tự kiểm tra):

Bước	T1	T2	Kết quả
1	UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock > 0;		1 dòng, stock thành 0
2		Cùng câu UPDATE	Chờ T1 commit, rồi đánh giá lại stock > 0 trên bản mới: 0 dòng
3	COMMIT;	Ứng dụng thấy 0 dòng, báo hết hàng	Không bao giờ âm kho

Giải thích: Cả ba cách đều cho kết quả đúng (chỉ một người mua được), khác nhau ở chỗ ai phải trả giá: optimistic để T2 thất bại muộn và phải retry; pessimistic bắt T2 chờ ngay từ lúc đọc; atomic update gộp "kiểm tra và trừ" thành một câu nên chỉ khóa trong thời gian rất ngắn. Tôi đã chạy thử atomic update trên SQLite: lần đầu changes() trả 1, lần thứ hai trả 0.

Optimistic lock chỉ đầy đủ khi có retry. Đoạn code dưới đây tách vòng retry ra một bean riêng, vì mỗi lần thử phải là một transaction mới đọc lại dữ liệu mới:

@Service
public class StockService {
    private final StockTxService tx; // separate bean, so calls go through the @Transactional proxy

    public StockService(StockTxService tx) { this.tx = tx; }

    public void decrease(long productId) {
        for (int attempt = 1; ; attempt++) {
            try {
                tx.decreaseOnce(productId); // each attempt is a new transaction with a fresh read
                return;
            } catch (ObjectOptimisticLockingFailureException e) {
                if (attempt == 3) throw e; // give up after 3 attempts
            }
        }
    }
}

@Service
class StockTxService {
    private final ProductRepository repo;
    StockTxService(ProductRepository repo) { this.repo = repo; }

    @Transactional
    public void decreaseOnce(long id) {
        Product p = repo.findById(id).orElseThrow();
        if (p.getStock() <= 0) throw new IllegalStateException("out of stock");
        p.setStock(p.getStock() - 1); // flushed as UPDATE ... WHERE id = ? AND version = ?
    }
}

Giải thích: Khi xung đột, decreaseOnce ném ObjectOptimisticLockingFailureException lúc commit, transaction đó đã rollback; StockService.decrease bắt lỗi và gọi lại, lần gọi sau mở transaction mới nên đọc được version mới. Nếu đặt vòng retry bên trong method @Transactional, mọi lần thử dùng chung một transaction đã hỏng và một persistence context đang giữ entity cũ, nên retry vô nghĩa. Nếu decreaseOnce nằm cùng class và được gọi qua this, @Transactional bị bỏ qua do self-invocation (xem 4.5). Thực tế có thể dùng @Retryable của Spring Retry thay cho vòng lặp tay. Đoạn code giả định Product có getter/setter.

Deadlock

Deadlock xảy ra khi T1 giữ khóa dòng A và chờ dòng B, còn T2 giữ B và chờ A. InnoDB và PostgreSQL tự phát hiện, chọn một transaction làm nạn nhân và rollback nó (MySQL lỗi 1213, PostgreSQL SQLSTATE 40P01). Phòng tránh: khóa theo thứ tự nhất quán (ví dụ chuyển tiền luôn khóa tài khoản có id nhỏ trước), giữ transaction ngắn, đảm bảo UPDATE/DELETE có index (InnoDB khóa trên index; không có index là khóa rất nhiều dòng), và retry ở tầng ứng dụng vì deadlock không thể loại bỏ 100%.

Deadlock qua bảng thời gian

Deadlock (khóa chết) là tình trạng hai transaction chờ nhau vòng tròn nên không bên nào đi tiếp được. Ví dụ kinh điển: T1 chuyển 10 từ tài khoản 1 sang 2, cùng lúc T2 chuyển 20 từ tài khoản 2 sang 1.

Bước	T1 (1 sang 2)	T2 (2 sang 1)	Kết quả
1	UPDATE accounts SET balance = balance - 10 WHERE id = 1;		T1 khóa dòng 1
2		UPDATE accounts SET balance = balance - 20 WHERE id = 2;	T2 khóa dòng 2
3	UPDATE accounts SET balance = balance + 10 WHERE id = 2;		T1 chờ T2 nhả dòng 2
4		UPDATE accounts SET balance = balance + 20 WHERE id = 1;	T2 chờ T1: vòng chờ, DB rollback T2
5	COMMIT;	Nhận lỗi, ứng dụng retry	T1 hoàn tất

Giải thích: Ở bước 4, MySQL trả cho T2 lỗi ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction, PostgreSQL trả ERROR: deadlock detected. Sửa bằng thứ tự khóa nhất quán: cả hai luôn cập nhật tài khoản có id nhỏ trước. Khi đó T2 bắt đầu bằng dòng 1, bị chặn ngay ở bước 2 chờ T1 xong, và không còn vòng chờ. Để điều tra deadlock đã xảy ra, MySQL có mục LATEST DETECTED DEADLOCK trong SHOW ENGINE INNODB STATUS, PostgreSQL ghi chi tiết hai câu lệnh vào log server.

6.4 JPA/Hibernate

Nhắc lại kiến thức

N2.11 đã giới thiệu JPA là đặc tả, Hibernate là hiện thực; entity là class ánh xạ tới bảng, EntityManager là đối tượng dùng để đọc ghi entity. Mục 4.5 giải thích @Transactional hoạt động qua proxy; trong Spring, persistence context sống cùng transaction đó, nên mọi hành vi ở mục này (dirty checking, lazy loading) phụ thuộc vào việc bạn đang ở trong hay ngoài transaction.

Entity state và persistence context

Trạng thái	Ý nghĩa
Transient	Object vừa new, persistence context không biết tới
Managed	Được persistence context theo dõi, thay đổi sẽ được flush
Detached	Từng managed nhưng context đã đóng hoặc bị clear/detach
Removed	Đã gọi remove, sẽ DELETE khi flush

Tài liệu cũ thường chỉ nêu 3 trạng thái và bỏ quên Removed. Persistence context (gắn với EntityManager, trong Spring mặc định sống theo transaction) chính là first-level cache: trong cùng context, find cùng id hai lần chỉ query một lần và trả về cùng một object. Dirty checking: lúc flush, Hibernate so trạng thái hiện tại của entity managed với snapshot lúc load và tự sinh UPDATE, không cần gọi save(). Flush xảy ra trước commit, khi gọi flush(), và (FlushMode AUTO) trước khi chạy query có thể bị ảnh hưởng bởi thay đổi chưa flush.

Ví dụ: persistence context và câu SQL Hibernate sinh ra

Nói bằng lời thường: persistence context là một "sổ ghi chép" Hibernate giữ trong suốt transaction, ghi lại mọi entity đã load cùng bản sao trạng thái lúc load. Flush là lúc Hibernate đối chiếu sổ đó với các object hiện tại và gửi xuống DB các câu SQL cần thiết. Đoạn code dưới đây làm 6 thao tác trong một transaction; hãy để ý thao tác nào thực sự sinh SQL:

@Service
public class CustomerService {
    private final EntityManager em;
    public CustomerService(EntityManager em) { this.em = em; }

    @Transactional
    public void demo() {
        Customer a1 = em.find(Customer.class, 1L); // SQL: select by id
        Customer a2 = em.find(Customer.class, 1L); // no SQL: served from the persistence context
        System.out.println(a1 == a2);              // prints: true
        a1.setName("An Nguyễn");                   // no SQL yet, only the Java object changes
        List<Customer> all = em.createQuery("select c from Customer c", Customer.class)
                .getResultList();                  // auto flush: UPDATE first, then the SELECT
        Customer d = new Customer(4L, "Dũng");     // transient, Hibernate does not know it
        em.persist(d);                             // managed now, INSERT waits until flush
    }                                              // commit: flush the INSERT, then COMMIT
}

Log SQL (bật spring.jpa.show-sql hoặc logger org.hibernate.SQL) cho thấy 4 câu:

select c1_0.id,c1_0.name from customers c1_0 where c1_0.id=?
update customers set name=? where id=?
select c1_0.id,c1_0.name from customers c1_0
insert into customers (name,id) values (?,?)

Giải thích: 6 thao tác nhưng chỉ 4 câu SQL, theo đúng thứ tự trên:

• find lần đầu sinh SELECT; lần thứ hai lấy từ persistence context nên không có SQL và a1 == a2 in true (cùng một object).
• setName không sinh SQL ngay. Nhưng trước khi chạy câu JPQL trên bảng customers, Hibernate phải auto flush để câu query thấy tên mới, nên UPDATE xuất hiện trước SELECT thứ hai. Nếu không có câu JPQL, UPDATE sẽ được gửi lúc commit.
• persist với id gán tay không INSERT ngay; INSERT được gửi lúc flush trước commit. (Với GenerationType.IDENTITY, Hibernate buộc phải INSERT ngay để lấy id, xem mục Bulk insert.)
• Không có dòng nào gọi save() hay update(), vậy mà DB vẫn bị sửa: đó là dirty checking. Nếu method được đánh dấu @Transactional(readOnly = true), Spring đặt Hibernate sang chế độ không flush và không giữ snapshot, nên thay đổi tên sẽ không được ghi.

Second-level cache gắn với EntityManagerFactory, dùng chung giữa các session, mặc định tắt; cần bật hibernate.cache.use_second_level_cache, cấu hình provider (thường Ehcache qua JCache) và đánh dấu entity. Chỉ hợp cho dữ liệu đọc nhiều, ít đổi (danh mục, cấu hình). Nhiều instance hoặc hệ thống khác ghi thẳng vào DB thì cache dễ lệch; nhiều team chọn Spring Cache + Redis ở tầng service thay vì L2.

Lazy, eager và LazyInitializationException

Mặc định của JPA: @ManyToOne và @OneToOne là EAGER, @OneToMany và @ManyToMany là LAZY. Nguyên tắc thực chiến: đặt mọi association là LAZY (@ManyToOne(fetch = FetchType.LAZY)) rồi fetch chủ động theo từng use case. EAGER không thể "tắt" theo từng query và thường sinh query thừa.

LazyInitializationException xảy ra khi truy cập association lazy (proxy hoặc collection chưa khởi tạo) sau khi persistence context đã đóng, thường là lúc Jackson serialize entity ở controller. Cách sửa đúng: fetch đủ dữ liệu trong transaction ở tầng service (JOIN FETCH, @EntityGraph) và trả về DTO. Cách sai: chuyển sang EAGER, bật hibernate.enable_lazy_load_no_trans.

Open Session In View: Spring Boot bật spring.jpa.open-in-view=true mặc định (kèm log cảnh báo khi khởi động), giữ EntityManager mở tới hết request nên lazy loading "chạy được" ở controller. Cái giá: connection bị giữ tới cuối request, query lazy phát sinh ngoài tầng service khó kiểm soát và N+1 bị che giấu. Tôi khuyên đặt false cho REST API.

N+1 và cách xử lý

N+1: 1 query lấy N đơn hàng, rồi mỗi lần truy cập order.getCustomer() hoặc order.getItems() sinh thêm 1 query. 100 đơn thành 101 round-trip tới DB. Phát hiện bằng log SQL, thống kê Hibernate hoặc test đếm số query.

Ví dụ: orderRepository.findByStatus(PAID) trả 100 đơn, sau đó vòng lặp gọi order.getItems().size() cho từng đơn, và log SQL hiện 1 câu select ... from orders cùng 100 câu select ... from order_item where order_id = ?. Repository dưới đây gom ba cách sửa phổ biến, mỗi method tương ứng một kỹ thuật:

import java.time.LocalDateTime;
import java.util.List;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface OrderRepository extends JpaRepository<Order, Long> {
    // Option 1: JOIN FETCH, load orders and items in one query
    @Query("select o from Order o join fetch o.items where o.status = :status")
    List<Order> findWithItems(@Param("status") OrderStatus status);

    // Option 2: derived query, EntityGraph tells Hibernate to also JOIN customer
    @EntityGraph(attributePaths = {"customer"})
    List<Order> findByCreatedAtAfter(LocalDateTime from);

    // Option 3: DTO projection, SELECT only 3 columns, no managed entities
    @Query("""
        select new com.shop.order.OrderSummary(o.id, c.name, o.total)
        from Order o join o.customer c where o.status = :status
        """)
    List<OrderSummary> findSummaries(@Param("status") OrderStatus status);
}

Giải thích: Cả ba method đều trả dữ liệu bằng một câu SQL thay vì 1 + N câu:

• findWithItems sinh dạng select o.*, i.* from orders o join order_item i on i.order_id = o.id where o.status = ?. Collection items của mỗi đơn được nạp sẵn, nên vòng lặp phía sau không query thêm. Lưu ý join fetch là inner join: đơn không có item nào sẽ không xuất hiện trong kết quả; cần giữ lại thì dùng left join fetch.
• findByCreatedAtAfter sinh select o.*, c.* from orders o left join customer c on c.id = o.customer_id where o.created_at > ?, nên order.getCustomer().getName() không kích hoạt lazy load.
• findSummaries sinh select o.id, c.name, o.total from orders o join customer c on ... where o.status = ?, rồi Hibernate gọi constructor của OrderSummary (thường là một record) cho từng dòng. Kết quả không phải entity nên không bị dirty checking và không có association nào để lazy load.

Chi tiết từng kỹ thuật:

• JOIN FETCH: một query lấy luôn association. Hibernate 6 tự loại entity cha trùng lặp, không cần DISTINCT như Hibernate 5. Không phân trang được khi fetch collection (Hibernate cảnh báo HHH90003004 và phân trang trong bộ nhớ); fetch hai List cùng lúc gây MultipleBagFetchException.
• @EntityGraph: khai báo association cần fetch ngay trên method repository.
• Batch fetching: hibernate.default_batch_fetch_size hoặc @BatchSize biến N query thành khoảng N/size query dạng IN (...); an toàn với phân trang.
• DTO projection: màn hình chỉ đọc thì lấy đúng cột cần, không tạo entity managed, không dirty checking. Đây thường là lựa chọn tốt nhất cho API danh sách.

Phân trang kèm collection: query 1 lấy trang id, query 2 JOIN FETCH theo danh sách id đó.

Đếm số query trước và sau khi sửa

Đoạn code dưới in số item của từng đơn đã thanh toán. Order.items là @OneToMany nên mặc định LAZY:

@Transactional(readOnly = true)
public void printItemCounts() {
    List<Order> orders = orderRepository.findByStatus(OrderStatus.PAID); // 1 query for the list
    for (Order o : orders) {
        System.out.println(o.getId() + ": " + o.getItems().size()); // 1 extra query per order
    }
}

Với dữ liệu mẫu có 3 đơn PAID (101, 103, 104), log SQL hiện:

select o1_0.id,o1_0.customer_id,o1_0.status,o1_0.total from orders o1_0 where o1_0.status=?
select i1_0.order_id,i1_0.id,i1_0.sku from order_item i1_0 where i1_0.order_id=?
select i1_0.order_id,i1_0.id,i1_0.sku from order_item i1_0 where i1_0.order_id=?
select i1_0.order_id,i1_0.id,i1_0.sku from order_item i1_0 where i1_0.order_id=?

Giải thích: 1 câu lấy danh sách, cộng 3 câu lấy items của từng đơn (tham số lần lượt là 101, 103, 104), tổng 4 câu, đúng công thức 1 + N. Bảng dưới đếm số query theo từng cách sửa đã nêu ở trên:

Cách lấy dữ liệu	3 đơn	100 đơn	1000 đơn
Lazy mặc định (N+1)	4	101	1001
join fetch hoặc @EntityGraph	1	1	1
default_batch_fetch_size: 100	2	2	11
DTO projection	1	1	1

Với batch fetching, câu đầu lấy danh sách, sau đó mỗi 100 đơn chỉ tốn một câu where order_id in (?, ?, ...), nên 1000 đơn là 1 + 10 = 11 câu. Để khóa kết quả này bằng test, bật spring.jpa.properties.hibernate.generate_statistics=true rồi assert Statistics.getPrepareStatementCount(), hoặc dùng thư viện đếm query; khi ai đó vô tình thêm một truy cập lazy, test sẽ đỏ ngay.

save, persist, merge

• persist(entity): entity transient thành managed, không trả về gì.
• merge(entity): copy trạng thái của object (thường detached) vào một instance managed và trả về instance managed đó; object truyền vào vẫn detached. Nếu chưa có trong context, Hibernate SELECT trước.
• Spring Data save(): nếu entity được coi là mới thì persist, ngược lại merge. "Mới" được xác định qua @Version null hoặc id null (hoặc implement Persistable).
• saveAndFlush(): flush ngay, nhưng chưa commit.

Ở Hibernate 6, các API native Session.save(), update(), saveOrUpdate() đã bị deprecated, thay bằng persist/merge.

Bulk insert/update

Cấu hình dưới đây là bộ tham số tôi thường bật cho service có import hoặc cập nhật hàng loạt. Mặc định Hibernate gửi từng câu INSERT/UPDATE riêng lẻ, mỗi câu một round-trip tới DB.

spring:
  jpa:
    open-in-view: false # disable OSIV (see the Lazy section above)
    properties: # keys below are passed to Hibernate as-is
      hibernate:
        jdbc:
          batch_size: 50 # group up to 50 identical statements into one JDBC batch
        order_inserts: true # sort INSERTs by entity so batches are not split
        order_updates: true
        default_batch_fetch_size: 100 # batch lazy loading: IN (...) with up to 100 ids

Giải thích: Các key dưới spring.jpa.properties được Boot chuyển thẳng thành property của Hibernate (hibernate.jdbc.batch_size...). Với batch_size: 50, khi persist 1000 entity, Hibernate dùng PreparedStatement.addBatch() và gửi 20 lượt thay vì 1000 lượt. order_inserts quan trọng khi lưu xen kẽ nhiều loại entity (Order, OrderItem, Order, OrderItem...): nếu không sắp xếp lại, mỗi lần đổi loại câu lệnh thì batch hiện tại bị gửi đi sớm, batch thực tế chỉ còn 1–2 câu. default_batch_fetch_size áp dụng cho chiều đọc: N lần lazy load được gom thành vài câu WHERE id IN (...). Tuy vậy, batch chỉ thực sự phát huy khi bạn xử lý thêm các điểm sau.

Khi import nhiều bản ghi: bật jdbc.batch_size, cứ mỗi 50 bản ghi gọi flush() rồi clear() để persistence context không phình. GenerationType.IDENTITY vô hiệu hóa insert batching vì Hibernate phải INSERT ngay để lấy id. Trên PostgreSQL dùng SEQUENCE với allocationSize (ví dụ 50, pooled optimizer). MySQL không có sequence, nên với khối lượng lớn hãy dùng JdbcTemplate.batchUpdate kèm rewriteBatchedStatements=true trên JDBC URL. Bulk update qua JPQL (@Modifying @Query("update ...")) đi thẳng xuống DB, bỏ qua persistence context và dirty checking; nên dùng clearAutomatically = true để tránh đọc dữ liệu cũ.

Migration và connection pool

Flyway/Liquibase: schema được version hóa trong repo (V1__init.sql, V2__add_order_status.sql), chạy tự động lúc khởi động, lưu lịch sử và checksum trong bảng riêng. Không bao giờ sửa migration đã chạy; tạo migration mới. Production dùng spring.jpa.hibernate.ddl-auto=validate hoặc none, không bao giờ update. Với rolling deploy, migration phải tương thích ngược (expand/contract: thêm cột mới, deploy code ghi cả hai, rồi mới xóa cột cũ).

HikariCP là pool mặc định của Spring Boot. Mặc định maximumPoolSize=10, connectionTimeout=30s. Pool lớn hơn không có nghĩa nhanh hơn: DB chỉ xử lý song song hiệu quả theo số core; gợi ý khởi đầu từ tài liệu HikariCP là core_count * 2 + effective_spindle_count của DB server. Nhớ nhân với số instance ứng dụng và so với max_connections của DB. Giảm connectionTimeout để fail fast, bật leakDetectionThreshold để phát hiện code giữ connection quá lâu.

6.5 Câu hỏi phỏng vấn thường gặp

Câu hỏi (Cơ bản): WHERE khác HAVING thế nào? Vì sao không dùng alias của SELECT trong WHERE được?

WHERE lọc dòng trước GROUP BY, HAVING lọc nhóm sau GROUP BY và dùng được hàm tổng hợp. Thứ tự thực thi logic là FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, nên lúc WHERE chạy, alias trong SELECT chưa tồn tại; ORDER BY thì dùng được alias.

Bẫy thường gặp

Đặt điều kiện không liên quan aggregate vào HAVING. Kết quả đúng nhưng DB phải gom nhóm toàn bộ dữ liệu trước khi lọc.

Câu hỏi (Cơ bản): Lấy 3 nhân viên lương cao nhất của mỗi phòng ban?

Dùng window function ROW_NUMBER() hoặc DENSE_RANK() với PARTITION BY department_id ORDER BY salary DESC trong subquery, rồi lọc rn <= 3. Chọn hàm theo yêu cầu khi có đồng lương: đúng 3 người thì ROW_NUMBER, top 3 mức lương thì DENSE_RANK.

Câu hỏi (Cơ bản): DELETE và TRUNCATE khác nhau thế nào?

DELETE là DML, xóa từng dòng theo WHERE, kích hoạt trigger, chậm với bảng lớn. TRUNCATE là DDL, giải phóng cả trang dữ liệu, rất nhanh, thường reset AUTO_INCREMENT.

Bẫy thường gặp

Khẳng định chắc nịch "TRUNCATE không rollback được". Đúng với MySQL/Oracle (implicit commit), sai với PostgreSQL, nơi TRUNCATE nằm trong transaction.

Câu hỏi (Đọc code): Query dưới đây trả về gì trên dữ liệu mẫu?

SELECT c.name, COUNT(*) AS c1, COUNT(o.id) AS c2
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID'
GROUP BY c.id, c.name
ORDER BY c.id;

Dữ liệu: customers gồm An (1), Bình (2), Chi (3); orders có đơn PAID 101 của An, 103 và 104 của Bình, cùng đơn 102 (NEW) và 105 (CANCELLED) của An.

Đáp án:

name	c1	c2
An	1	1
Bình	2	2
Chi	1	0

Chỗ đáng chú ý là dòng Chi: COUNT(*) ra 1 còn COUNT(o.id) ra 0. Điều kiện status = 'PAID' nằm trong ON nên đơn 102, 105 không được ghép và An chỉ còn một dòng. Chi không có đơn nào khớp nên LEFT JOIN vẫn sinh một dòng Chi | NULL. COUNT(*) đếm dòng, nên được 1; COUNT(o.id) chỉ đếm giá trị khác NULL, nên được 0. Muốn đếm số đơn của mỗi khách sau LEFT JOIN, luôn dùng COUNT(cột của bảng phải).

Câu hỏi (Đọc code): Query top 2 dưới đây trả về bao nhiêu dòng?

SELECT department_id, name, salary
FROM (
    SELECT e.*, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk
    FROM employees e
) t
WHERE rk <= 2;

Dữ liệu phòng 10: Lan 5000, Minh 5000, Nga 4000, Oanh 3000, Phúc 2000. Phòng 20: Quân 4500, Sơn 3500.

Đáp án:

4 dòng: Lan, Minh (phòng 10) và Quân, Sơn (phòng 20). Ở phòng 10, RANK cho Lan và Minh cùng hạng 1, rồi nhảy thẳng tới hạng 3 cho Nga, nên rk <= 2 chỉ giữ hai người đồng hạng 1. Phòng 20 có hạng 1 và 2. Nếu dùng DENSE_RANK, Nga được hạng 2 và phòng 10 trả 3 người; dùng ROW_NUMBER thì trả đúng 2 người nhưng ai được chọn giữa Lan và Minh là không xác định nếu không thêm cột phụ vào ORDER BY.

Câu hỏi (Đọc code): Câu SQL dưới đây chạy được trên MySQL 8 và PostgreSQL không?

SELECT customer_id, status, SUM(total) AS total
FROM orders
GROUP BY customer_id;

Đáp án:

Không. Cả hai đều báo lỗi vì status không nằm trong GROUP BY và cũng không nằm trong hàm tổng hợp. Nhóm customer_id = 1 có ba đơn với ba status khác nhau (PAID, NEW, CANCELLED), DB không biết lấy status nào để hiển thị. PostgreSQL báo column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function; MySQL 8 bật ONLY_FULL_GROUP_BY mặc định và báo lỗi 1055. MySQL 5.6 cũ và SQLite thì chấp nhận và trả một status bất kỳ, đó là nguồn gốc của nhiều bug dữ liệu. Sửa bằng cách thêm status vào GROUP BY (tổng theo khách và trạng thái) hoặc bỏ nó khỏi SELECT.

Câu hỏi (Hiểu sâu): Tìm khách hàng chưa có đơn nào. Viết bằng NOT IN có vấn đề gì?

Cách an toàn là NOT EXISTS hoặc LEFT JOIN ... WHERE o.id IS NULL; NOT IN sai khi subquery có giá trị NULL. Trên dữ liệu mẫu, cả hai câu dưới đều trả về Chi:

• SELECT c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
• SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

Vấn đề của NOT IN: c.id NOT IN (1, NULL) tương đương c.id <> 1 AND c.id <> NULL; vế sau luôn là UNKNOWN nên cả biểu thức không bao giờ TRUE, và query trả rỗng. Tôi đã thử với bảng blacklist(customer_id) chứa 1 và NULL: WHERE c.id NOT IN (SELECT customer_id FROM blacklist) trả 0 dòng thay vì Bình và Chi. Cột orders.customer_id ở đây là NOT NULL nên tạm an toàn, nhưng thói quen dùng NOT EXISTS tránh được bẫy khi schema thay đổi.

Câu hỏi (Cơ bản): Window function khác GROUP BY thế nào?

GROUP BY gộp nhiều dòng thành một dòng mỗi nhóm; window function giữ nguyên mọi dòng và gắn thêm giá trị tính trên nhóm. Ví dụ trên bảng employees: SELECT department_id, SUM(salary) FROM employees GROUP BY department_id trả 2 dòng (phòng 10: 19000, phòng 20: 8000). Còn SELECT name, salary, SUM(salary) OVER (PARTITION BY department_id) FROM employees trả đủ 7 dòng, mỗi dòng kèm tổng lương của phòng mình, nhờ đó tính được "lương người này chiếm bao nhiêu phần trăm của phòng" (Quân 4500/8000 khoảng 56%) mà không cần JOIN lại. Window function được tính sau WHERE, GROUP BY, HAVING, nên muốn lọc theo nó phải bọc query con.

Câu hỏi (Hiểu sâu): Bạn có index (customer_id, status, created_at). Những query nào dùng được index này?

Dùng được với điều kiện bắt đầu từ cột trái nhất: customer_id, customer_id + status, cả ba cột, và ORDER BY created_at khi hai cột đầu là so sánh bằng. Không dùng hiệu quả khi chỉ lọc status hoặc created_at. Điều kiện range trên status làm created_at không còn dùng để seek. Nếu SELECT chỉ lấy các cột trong index thì thành covering index.

Câu hỏi đào sâu

"Query có index mà vẫn chậm, bạn làm gì?" — Chạy EXPLAIN xem index nào được chọn, số dòng ước lượng, có filesort/temporary không; kiểm tra hàm bọc cột, ép kiểu ngầm, wildcard đầu; kiểm tra thống kê cũ (ANALYZE TABLE / ANALYZE); cân nhắc covering index hoặc viết lại query.

Câu hỏi (Hiểu sâu): Vì sao OFFSET lớn chậm? Giải pháp?

DB vẫn phải đọc và bỏ qua toàn bộ các dòng trước offset, nên trang 5000 chậm hơn trang 1 rất nhiều. Giải pháp là keyset pagination: nhớ giá trị sort của dòng cuối trang trước và dùng WHERE (created_at, id) < ... với index phù hợp. Nếu vẫn cần offset (màn hình admin), giới hạn số trang tối đa.

Câu hỏi (Cơ bản): Index là gì, vì sao làm query nhanh hơn, và cái giá phải trả là gì?

Index là một cấu trúc dữ liệu phụ (thường là B+Tree) giữ giá trị của cột đã sắp xếp kèm con trỏ tới dòng, giúp DB tìm dòng bằng vài lần đọc trang thay vì quét cả bảng. Bảng 10 triệu dòng không có index trên email thì WHERE email = ? phải đọc cả 10 triệu dòng; có index thì chỉ đi 3–4 tầng cây. Cái giá: mỗi INSERT, UPDATE cột được index, DELETE đều phải cập nhật thêm cây; index tốn dung lượng đĩa và bộ nhớ đệm; optimizer có nhiều lựa chọn hơn nên đôi khi chọn sai. Vì vậy chỉ index theo query thực tế: cột trong WHERE, JOIN, ORDER BY của các query chạy nhiều.

Câu hỏi (Hiểu sâu): Clustered index và secondary index trong InnoDB khác nhau thế nào? Vì sao PK dạng UUID v4 làm insert chậm?

Clustered index là cây PK chứa luôn cả dòng dữ liệu ở lá; secondary index chỉ chứa giá trị cột được index kèm PK. Tra theo secondary index là đi hai cây: tìm PK trong cây secondary rồi tra cây PK để lấy đủ cột; nếu mọi cột cần đã có trong secondary index (covering) thì bỏ được bước hai. UUID v4 là số ngẫu nhiên, nên dòng mới chèn vào vị trí bất kỳ giữa cây PK: trang đích thường đã đầy và phải tách đôi, cây phân mảnh, bộ đệm phải nạp nhiều trang khác nhau. PK tăng dần (BIGINT tự tăng, sequence, hoặc UUID v7 có thành phần thời gian) luôn chèn vào cuối cây. Ngoài ra UUID 16 byte (hoặc 36 byte nếu lưu dạng chuỗi) làm mọi secondary index phình to vì chúng đều chứa PK.

Câu hỏi đào sâu

"Vậy PostgreSQL có bị như vậy không?" — PostgreSQL không có clustered index theo nghĩa InnoDB: dữ liệu nằm trong heap, mọi index đều trỏ tới vị trí tuple. UUID ngẫu nhiên vẫn làm cây index PK phân mảnh và tốn bộ nhớ đệm, nhưng không ảnh hưởng tới vị trí dòng trong bảng như InnoDB.

Câu hỏi (Hiểu sâu): Vì sao index trên cột status chỉ có 3 giá trị thường không được dùng?

Vì độ chọn lọc thấp: một giá trị ứng với quá nhiều dòng, nên đi qua index rồi tra ngược từng dòng còn tốn hơn quét thẳng bảng. Nếu 90% đơn là PAID, WHERE status = 'PAID' qua index nghĩa là hàng triệu lần nhảy ngẫu nhiên về cây PK, trong khi full scan đọc tuần tự rất nhanh; optimizer ước lượng được điều này nên bỏ index. Index đó vẫn có ích khi: lọc giá trị hiếm (status = 'FAILED' chỉ 0,1%), khi nó là cột đầu của composite index kèm cột khác ((status, created_at) cho màn hình "đơn PAID mới nhất"), hoặc dùng partial index của PostgreSQL CREATE INDEX ... WHERE status = 'FAILED'.

Câu hỏi (Đọc code): Index (customer_id, status, created_at) có được dùng cho từng câu dưới không?

-- Q1
SELECT * FROM orders WHERE status = 'PAID' AND customer_id = 7;
-- Q2
SELECT * FROM orders WHERE created_at >= '2026-09-01';
-- Q3
SELECT * FROM orders WHERE customer_id = 7 AND created_at >= '2026-09-01';
-- Q4
SELECT id, status FROM orders WHERE customer_id = 7;

Đáp án:

• Q1: có, seek cả customer_id và status. Thứ tự điều kiện trong WHERE không quan trọng, chỉ cần tập điều kiện chứa tiền tố trái của index.
• Q2: không seek được. Thiếu cột trái nhất customer_id, giống tra danh bạ theo tên mà không biết họ; thường là full scan. Nếu query này quan trọng, tạo index riêng trên created_at.
• Q3: có, nhưng chỉ seek theo customer_id. Cột status ở giữa bị bỏ trống nên các dòng của khách 7 không được sắp theo created_at liên tục; điều kiện created_at chỉ được kiểm tra trên từng entry của khách 7 (MySQL hiện Using index condition).
• Q4: có, và là covering index. status nằm trong index, còn id là PK nên InnoDB lưu sẵn trong mọi secondary index; DB không cần đọc bảng, EXPLAIN hiện Using index.

Câu hỏi (Đọc code): Đọc EXPLAIN dưới đây, query có vấn đề gì và bạn sửa thế nào?

EXPLAIN SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
   type: ref
    key: idx_orders_customer
key_len: 8
   rows: 45210
  Extra: Using filesort

Đáp án:

Index idx_orders_customer (customer_id) giúp lọc đúng khách, nhưng DB phải lấy hết khoảng 45 nghìn đơn của khách 7 rồi sắp xếp (Using filesort) chỉ để trả 10 dòng. key_len: 8 khớp một cột BIGINT, xác nhận chỉ customer_id được dùng. Sửa bằng composite index (customer_id, created_at): trong cùng một khách, index đã sắp theo thời gian, DB đọc ngược 10 entry đầu rồi dừng, filesort biến mất và số dòng thực tế đọc chỉ còn 10. Index cũ (customer_id) trở thành thừa vì là tiền tố của index mới, có thể xóa.

Câu hỏi (Thực chiến): API danh sách đơn hàng từ 50ms tăng lên 3 giây sau vài tháng. Bạn điều tra thế nào?

Tôi đi từ đo đạc tới nguyên nhân: xác định câu SQL chậm, đọc EXPLAIN, rồi sửa index hoặc query, không đoán mò.

1. Xác định chậm ở đâu: APM hoặc log thời gian cho thấy phần lớn thời gian nằm ở DB hay ở code. Kiểm tra luôn số câu SQL mỗi request, vì N+1 cũng làm API chậm dần theo dữ liệu.
2. Tìm câu SQL cụ thể: slow query log của MySQL (long_query_time), pg_stat_statements của PostgreSQL, hoặc log SQL của Hibernate.
3. Chạy EXPLAIN (PostgreSQL: EXPLAIN ANALYZE) với tham số thật: tìm ALL/Seq Scan, filesort, số dòng ước lượng lớn, ước lượng lệch thực tế.
4. Sửa theo nguyên nhân: thêm composite index khớp WHERE và ORDER BY, viết lại điều kiện bọc hàm, đổi OFFSET lớn sang keyset, cập nhật thống kê bằng ANALYZE.
5. Kiểm chứng lại bằng EXPLAIN và số liệu p95 sau khi deploy.

Câu hỏi đào sâu

"Thêm index trên bảng 50 triệu dòng đang chạy production thế nào cho an toàn?" — MySQL 8 hỗ trợ online DDL (ALGORITHM=INPLACE, LOCK=NONE), bảng rất lớn thì dùng công cụ như gh-ost hoặc pt-online-schema-change; PostgreSQL dùng CREATE INDEX CONCURRENTLY (không chạy được trong transaction, nên tách thành migration riêng). Chạy giờ thấp điểm và theo dõi replication lag.

Câu hỏi (Đọc code): Tìm lỗi trong query keyset dưới đây

-- previous page ended at (created_at = '2026-09-02 08:00', id = 104)
SELECT id, created_at, total FROM orders
WHERE created_at < '2026-09-02 08:00'
ORDER BY created_at DESC, id DESC
LIMIT 2;

Đáp án:

Query bỏ sót các đơn trùng created_at với dòng cuối trang trước; trên dữ liệu mẫu nó trả 102, 101 và mất đơn 103. Đơn 103 có created_at = '2026-09-02 08:00' giống đơn 104, nên không thỏa <. Sửa bằng cách thêm cột phụ duy nhất: WHERE created_at < :ts OR (created_at = :ts AND id < :id) (PostgreSQL viết gọn (created_at, id) < (:ts, :id)), và index (created_at, id) để seek. Nguyên tắc: cursor phải gồm đủ các cột tạo thành thứ tự duy nhất.

Câu hỏi (Hiểu sâu): Isolation level mặc định của MySQL và PostgreSQL là gì? MVCC giúp gì?

MySQL InnoDB là REPEATABLE READ, PostgreSQL là READ COMMITTED. MVCC giữ nhiều phiên bản của dòng để người đọc thấy snapshot nhất quán mà không cần khóa: đọc không chặn ghi và ngược lại. READ COMMITTED lấy snapshot theo từng câu lệnh, REPEATABLE READ theo cả transaction.

Bẫy thường gặp

Nói "REPEATABLE READ chống được mọi vấn đề đồng thời". MySQL RR vẫn để lọt lost update khi ứng dụng đọc giá trị, tính toán rồi ghi đè.

Câu hỏi đào sâu

"Vì sao không dùng SERIALIZABLE cho an toàn?" — Throughput giảm mạnh do khóa nhiều hơn (MySQL) hoặc tỉ lệ abort cao (PostgreSQL SSI), ứng dụng phải retry. Thực tế dùng level mặc định và khóa có chủ đích ở đúng chỗ cần.

Câu hỏi (Thực chiến): Hai khách cùng mua món hàng cuối cùng. Bạn xử lý thế nào để không bán âm kho?

Tôi trình bày ba lựa chọn và trade-off. Atomic update UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock > 0 rồi kiểm tra số dòng ảnh hưởng: đơn giản, nhanh nhất, là lựa chọn mặc định của tôi. Pessimistic lock SELECT ... FOR UPDATE khi logic cần đọc rồi quyết định phức tạp trong transaction ngắn. Optimistic lock @Version khi xung đột hiếm, kèm retry có giới hạn. Flash sale tải cực cao thì đưa tồn kho lên Redis (DECR atomic) hoặc xếp hàng qua message queue.

Bẫy thường gặp

Dùng synchronized trong Java. Chỉ có tác dụng trong một JVM, chạy hai instance là hỏng, và cũng không bao trùm được ranh giới transaction.

Câu hỏi (Thực chiến): Deadlock trong DB xảy ra khi nào? Bạn phòng tránh ra sao?

Hai transaction giữ khóa mà bên kia cần và chờ lẫn nhau; DB tự phát hiện và rollback một bên. Phòng tránh: khóa theo thứ tự nhất quán, transaction ngắn, UPDATE/DELETE đi qua index, không gọi API ngoài khi giữ khóa, và retry khi gặp lỗi deadlock (Spring dịch sang nhóm PessimisticLockingFailureException, với Hibernate thường là CannotAcquireLockException).

Câu hỏi (Cơ bản): ACID là gì? Cho ví dụ khi thiếu từng tính chất.

ACID là bốn đảm bảo của transaction: Atomicity, Consistency, Isolation, Durability. Lấy ví dụ chuyển 100 từ tài khoản A sang B:

• Atomicity (nguyên tử): trừ A và cộng B cùng thành công hoặc cùng bị hủy. Thiếu nó, server sập giữa chừng thì A mất tiền mà B không nhận được.
• Consistency (nhất quán): sau transaction dữ liệu vẫn thỏa ràng buộc, ví dụ CHECK (balance >= 0). Thiếu nó, A có thể âm tiền.
• Isolation (cô lập): một báo cáo chạy song song không thấy trạng thái "A đã trừ, B chưa cộng".
• Durability (bền vững): đã báo thành công thì mất điện ngay sau đó tiền vẫn ở B, nhờ redo log/WAL đã ghi xuống đĩa.

Câu hỏi (Cơ bản): Dirty read, non-repeatable read và phantom read khác nhau thế nào?

Khác nhau ở thứ bị thay đổi: dirty read đọc dữ liệu chưa commit; non-repeatable read là một dòng đã đọc bị sửa; phantom read là tập dòng thỏa điều kiện bị thêm hoặc bớt. Ví dụ: đọc số dư 50 mà transaction kia sau đó rollback là dirty read; đọc số dư hai lần trong một transaction ra 100 rồi 70 là non-repeatable read; đếm đơn PAID của khách hai lần ra 1 rồi 2 vì có đơn mới chèn vào là phantom read. Bảng thời gian chi tiết ở mục 6.3.

Câu hỏi (Đọc code): Với mỗi isolation level, T2 đọc ra giá trị gì ở bước 3?

-- accounts(id = 1, balance = 100) at the start
-- step 1, T2: BEGIN; SELECT balance FROM accounts WHERE id = 1;
-- step 2, T1: UPDATE accounts SET balance = 70 WHERE id = 1; COMMIT;
-- step 3, T2: SELECT balance FROM accounts WHERE id = 1;

Đáp án:

• READ COMMITTED (mặc định PostgreSQL): bước 1 đọc 100, bước 3 đọc 70. Mỗi câu lệnh lấy snapshot mới, và lúc bước 3 chạy thì 70 đã commit. Đây là non-repeatable read.
• REPEATABLE READ (mặc định MySQL): bước 1 và bước 3 đều đọc 100. Snapshot được chụp ở lần đọc đầu (bước 1) và dùng cho cả transaction.
• Chi tiết thú vị ở MySQL: nếu T2 chạy SELECT ... FOR UPDATE ở bước 3, nó đọc bản mới nhất là 70, vì đọc khóa (locking read) luôn đọc dữ liệu hiện tại chứ không đọc snapshot. Hai câu SELECT trong cùng transaction có thể trả hai giá trị khác nhau chỉ vì một câu có FOR UPDATE.

Câu hỏi (Hiểu sâu): MVCC giúp gì? Vì sao transaction mở quá lâu gây hại cho DB?

MVCC giữ nhiều phiên bản của mỗi dòng để người đọc thấy snapshot nhất quán mà không cần khóa, nên đọc không chặn ghi và ghi không chặn đọc. Cái giá là phiên bản cũ phải được giữ cho tới khi không còn transaction nào cần. Một transaction mở suốt một giờ (ví dụ job báo cáo, hoặc code mở @Transactional rồi gọi API ngoài bị treo) buộc DB giữ mọi phiên bản cũ phát sinh trong một giờ đó: InnoDB có history list dài ra, undo log phình, đọc chậm dần vì phải lần qua chuỗi phiên bản; PostgreSQL không VACUUM được tuple chết nên bảng và index phình to. Theo dõi bằng information_schema.innodb_trx (MySQL) hoặc pg_stat_activity với cột xact_start (PostgreSQL).

Câu hỏi (Hiểu sâu): Khi nào chọn optimistic lock, khi nào chọn pessimistic lock?

Optimistic khi xung đột hiếm và thao tác kéo dài; pessimistic khi xung đột thường xuyên và transaction ngắn.

Tình huống	Chọn	Vì sao
Admin sửa thông tin sản phẩm qua form nhiều bước	Optimistic (@Version)	Hiếm khi hai người sửa cùng lúc; không thể giữ khóa DB trong lúc người dùng suy nghĩ
Trừ số dư ví khi thanh toán	Pessimistic hoặc atomic update	Nhiều request vào cùng một ví, retry liên tục sẽ tốn hơn chờ
Trừ tồn kho đơn giản	Atomic update	Một câu SQL, không cần đọc trước
Worker lấy job từ bảng hàng đợi	FOR UPDATE SKIP LOCKED	Không chờ, bỏ qua dòng người khác đang giữ

Với optimistic, xung đột cao nghĩa là phần lớn request thất bại và retry, throughput còn tệ hơn pessimistic. Với pessimistic, transaction dài hoặc gọi API ngoài khi đang giữ khóa sẽ làm hàng đợi chờ khóa dài ra và dễ deadlock.

Câu hỏi (Thực chiến): Đối soát cuối ngày thấy số dư ví của một số user lệch so với tổng giao dịch. Bạn điều tra và sửa thế nào?

Dấu hiệu điển hình của lost update: code đọc số dư lên Java, cộng trừ, rồi ghi đè cả giá trị. Tôi điều tra theo các bước:

1. Tìm code cập nhật số dư: nếu thấy mẫu wallet.setBalance(wallet.getBalance() - amount) sau findById mà không có @Version hay FOR UPDATE, gần như chắc chắn là nguyên nhân.
2. Đối chiếu log: các user bị lệch thường có hai giao dịch cách nhau vài mili giây (ví dụ user bấm hai lần, hoặc hoàn tiền và thanh toán cùng lúc).
3. Tái hiện bằng test đồng thời: 10 thread cùng trừ 1 từ số dư 100, kết quả khác 90 là xác nhận.

Sửa: dùng atomic update UPDATE wallet SET balance = balance - :amount WHERE id = :id AND balance >= :amount và kiểm tra số dòng ảnh hưởng, hoặc findByIdForUpdate nếu cần logic phức tạp, hoặc @Version kèm retry. Kèm theo đó lưu bảng giao dịch (ledger) bất biến để số dư luôn tính lại và đối soát được, rồi viết script sửa số liệu đã lệch.

Câu hỏi đào sâu

"Sao không đặt @Transactional(isolation = SERIALIZABLE) cho hàm trừ tiền?" — Được, nhưng MySQL sẽ biến mọi SELECT thành locking read, tăng khóa và deadlock; PostgreSQL sẽ abort một bên với lỗi serialization và bạn vẫn phải viết retry. Atomic update giải quyết đúng chỗ với chi phí thấp nhất.

Câu hỏi (Thực chiến): Log production mỗi ngày có vài chục lỗi "Deadlock found when trying to get lock". Bạn làm gì?

Đầu tiên tìm hai câu lệnh tham gia deadlock, rồi sửa thứ tự khóa hoặc index; song song thêm retry cho lỗi này.

1. Lấy chi tiết: SHOW ENGINE INNODB STATUS (mục LATEST DETECTED DEADLOCK) hoặc bật innodb_print_all_deadlocks để ghi mọi deadlock vào error log. Log cho biết hai transaction, câu lệnh đang chạy, khóa đang giữ và khóa đang chờ.
2. Tìm nguyên nhân phổ biến: hai luồng code cập nhật cùng tập dòng theo thứ tự ngược nhau (chuyển tiền A sang B và B sang A); UPDATE theo cột không có index nên khóa quá nhiều dòng; transaction làm quá nhiều việc.
3. Sửa: sắp xếp id trước khi khóa (luôn khóa id nhỏ trước), thêm index cho cột trong WHERE của UPDATE/DELETE, thu nhỏ transaction.
4. Retry: deadlock là lỗi tạm thời, retry toàn bộ transaction 2–3 lần với backoff là cách xử lý chuẩn; Spring ném CannotAcquireLockException hoặc DeadlockLoserDataAccessException tùy phiên bản.

Câu hỏi (Hiểu sâu): Controller gọi thẳng productRepository.findByIdForUpdate(id) rồi gọi save(), không có @Transactional ở service. Khóa có tác dụng không?

Không. Khóa chỉ sống trong transaction, mà ở đây không có transaction nào bao trùm cả "đọc, tính, ghi". Với query method tự khai báo trên interface, Spring Data mặc định không áp transaction; Hibernate thấy câu FOR UPDATE chạy ngoài transaction sẽ ném TransactionRequiredException ("no transaction is in progress"), và đó là trường hợp may mắn vì lỗi lộ ra ngay. Nếu ai đó "sửa" bằng cách gắn @Transactional lên method repository, lỗi biến mất nhưng khóa được nhả ngay khi findByIdForUpdate trả về; phần tính toán trong Java không được bảo vệ, còn save() chạy trong một transaction khác, nên hai request song song vẫn gây lost update. Sửa đúng: đặt toàn bộ "đọc có khóa, tính toán, ghi" trong một method @Transactional ở tầng service, và gọi method đó từ bean khác để tránh self-invocation (xem 4.5).

Câu hỏi (Thực chiến): Năm worker cùng đọc bảng jobs để lấy việc, nhưng nhiều job bị xử lý hai lần. Sửa thế nào?

Dùng SELECT ... FOR UPDATE SKIP LOCKED để mỗi worker khóa và nhận các dòng khác nhau, trong cùng transaction với việc đổi trạng thái job. Nguyên nhân: các worker cùng chạy SELECT * FROM jobs WHERE status = 'NEW' LIMIT 10, cùng nhận 10 dòng giống nhau. Nếu chỉ thêm FOR UPDATE, worker thứ hai phải chờ worker đầu xong, mất tính song song. Với SKIP LOCKED (MySQL 8+, PostgreSQL 9.5+), worker thứ hai bỏ qua các dòng đang bị khóa và lấy 10 dòng tiếp theo. Mẫu chuẩn: BEGIN; SELECT id FROM jobs WHERE status = 'NEW' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED; UPDATE jobs SET status = 'PROCESSING', picked_at = now() WHERE id IN (...); COMMIT; rồi xử lý ngoài transaction. Cần thêm job dọn các dòng kẹt ở PROCESSING quá lâu khi worker chết giữa chừng.

Câu hỏi (Hiểu sâu): Trong một method @Transactional bạn load entity, set vài field để hiển thị, không gọi save(). Chuyện gì xảy ra?

Entity đang managed, nên lúc commit dirty checking phát hiện thay đổi và tự UPDATE xuống DB. Đây là bug có thật: dữ liệu bị sửa "không ai gọi save". Muốn biến đổi dữ liệu để hiển thị thì map sang DTO, hoặc dùng readOnly = true cho luồng chỉ đọc.

Câu hỏi đào sâu

"Dirty checking tốn gì?" — Hibernate phải giữ snapshot của mọi entity managed và so sánh lúc flush; load hàng chục nghìn entity trong một transaction tốn bộ nhớ và CPU. Đó là lý do dùng projection và readOnly cho query lớn.

Câu hỏi (Hiểu sâu): N+1 là gì? Bạn phát hiện và sửa thế nào?

1 query lấy danh sách cha, rồi N query lazy cho association của từng phần tử. Phát hiện qua log SQL, thống kê Hibernate, hoặc test assert số query. Sửa bằng JOIN FETCH hoặc @EntityGraph cho use case cụ thể, batch fetching cho trường hợp tổng quát và có phân trang, DTO projection cho API chỉ đọc.

Bẫy thường gặp

"Chuyển sang EAGER là hết N+1." Sai: EAGER trên collection vẫn có thể sinh N+1 (khi load qua JPQL) và làm mọi query khác nặng thêm.

Câu hỏi đào sâu

"JOIN FETCH kết hợp Pageable được không?" — Với collection thì không đúng nghĩa: Hibernate cảnh báo và phân trang trong bộ nhớ sau khi tải toàn bộ. Tách hai query: lấy trang id trước, rồi fetch theo id.

Câu hỏi (Hiểu sâu): LazyInitializationException là gì? Có nên bật open-in-view cho xong?

Lỗi khi truy cập association lazy sau khi persistence context đã đóng, thường xảy ra lúc serialize entity ở controller. Sửa bằng cách fetch đủ trong service và trả DTO. Tôi tắt open-in-view vì nó giữ connection tới hết request, che giấu N+1 và cho phép query chạy ngầm từ tầng view.

Câu hỏi (Thực chiến): Import 1 triệu bản ghi bằng JPA rất chậm. Bạn tối ưu thế nào?

Bật hibernate.jdbc.batch_size, order_inserts; flush và clear persistence context theo lô; đổi id generation khỏi IDENTITY (dùng SEQUENCE pooled trên PostgreSQL) vì IDENTITY chặn batching; chia transaction theo lô để lỗi không phải làm lại từ đầu. Nếu vẫn chậm, bỏ qua JPA và dùng JdbcTemplate.batchUpdate (MySQL thêm rewriteBatchedStatements=true) hoặc công cụ native như COPY của PostgreSQL, LOAD DATA của MySQL.

Bẫy thường gặp

Gọi saveAll(list) với 1 triệu entity trong một transaction. saveAll chỉ lặp qua save(), persistence context giữ cả triệu object, dễ OutOfMemoryError.

Câu hỏi (Đọc code): Method dưới đây sinh ra bao nhiêu câu SQL?

@Transactional
public void rename(long id) {
    Customer c1 = customerRepository.findById(id).orElseThrow();
    Customer c2 = customerRepository.findById(id).orElseThrow();
    c2.setName(c2.getName().trim());
    log.info("same object: {}", c1 == c2);
}

Giả sử tên trong DB là " An " (có khoảng trắng hai đầu).

Đáp án:

Hai câu: một SELECT và một UPDATE, và log in same object: true. findById lần đầu SELECT từ DB và đưa entity vào persistence context; lần thứ hai trả luôn object đó từ context, nên c1 == c2. setName làm entity "bẩn"; lúc commit, dirty checking so với snapshot " An ", thấy khác và sinh update customers set name=? where id=? dù không ai gọi save(). Nếu tên trong DB vốn đã là "An", trim() trả chuỗi bằng giá trị cũ, dirty checking thấy không đổi và chỉ có một câu SELECT.

Câu hỏi (Đọc code): Có 50 đơn hàng thuộc 10 khách khác nhau. Đoạn code dưới sinh bao nhiêu câu SQL?

// Order: @ManyToOne(fetch = FetchType.LAZY) private Customer customer;
@Transactional(readOnly = true)
public void printCustomers() {
    List<Order> orders = orderRepository.findAll();
    for (Order o : orders) {
        System.out.println(o.getCustomer().getName()); // lazy proxy is initialized here
    }
}

Đáp án:

11 câu: 1 câu lấy 50 đơn, cộng 10 câu lấy khách, không phải 51. Các đơn cùng khách dùng chung một proxy trong persistence context; proxy của khách X được khởi tạo ở lần đầu getName(), những đơn sau của cùng khách X đọc từ context mà không query lại. Vẫn là N+1 với N là số khách khác nhau. Bật default_batch_fetch_size: 100 thì còn 2 câu (một câu where id in (...) cho cả 10 khách); dùng @EntityGraph(attributePaths = "customer") thì còn 1 câu có JOIN.

Câu hỏi đào sâu

"Nếu đổi thành FetchType.EAGER thì sao?" — Vẫn 11 câu, nhưng các câu lấy khách chạy ngay trong findAll() dù bạn có dùng tới khách hay không. EAGER không sửa N+1 khi load bằng JPQL; nó chỉ chuyển N+1 sang chỗ khác và áp lên mọi query của entity này.

Câu hỏi (Đọc code): Sau khi commit, tên của khách id 1 trong DB là gì?

@Transactional
public void update() {
    Customer detached = new Customer(1L, "Bình"); // id 1 exists in DB with name "An"
    Customer managed = em.merge(detached);
    detached.setName("Chi");
    System.out.println(managed == detached); // prints: false
}

Đáp án:

Tên là "Bình", không phải "Chi". merge không biến detached thành managed; nó SELECT dòng id 1 vào persistence context, chép trạng thái của detached (tên "Bình") sang instance managed đó, rồi trả về instance managed. Vì vậy managed == detached là false. Lệnh detached.setName("Chi") sửa một object mà Hibernate không theo dõi, nên bị bỏ qua. Lúc commit, dirty checking thấy managed đổi từ "An" sang "Bình" và sinh UPDATE. Bài học: luôn dùng giá trị trả về của merge (và của Spring Data save(), vì với entity đã có id nó gọi merge).

Câu hỏi (Cơ bản): Fetch type mặc định của các loại quan hệ là gì? Vì sao nên đặt mọi quan hệ là LAZY?

@ManyToOne, @OneToOne mặc định EAGER; @OneToMany, @ManyToMany mặc định LAZY. Nên đặt tất cả là LAZY vì EAGER là quyết định cố định trên entity, áp lên mọi query: màn hình chỉ cần mã đơn cũng bị kéo theo khách hàng, và khi load danh sách bằng JPQL, EAGER sinh thêm query cho từng dòng (N+1). Với LAZY, mỗi use case tự quyết định cần gì bằng join fetch, @EntityGraph hoặc DTO projection. Cái giá là phải cẩn thận với LazyInitializationException khi truy cập ngoài transaction.

Câu hỏi (Hiểu sâu): @Transactional(readOnly = true) thực sự làm gì?

Nó là gợi ý tối ưu cho cả Hibernate lẫn JDBC driver, không phải cơ chế chặn ghi tuyệt đối. Cụ thể trong Spring Boot 3 với Hibernate 6:

• Hibernate đặt session sang FlushMode.MANUAL và đánh dấu read-only: không flush, không giữ snapshot cho dirty checking, nên tiết kiệm bộ nhớ và CPU khi load nhiều entity. Thay đổi trên entity bị bỏ qua.
• Spring gọi Connection.setReadOnly(true); MySQL driver chuyển sang read-only transaction (bỏ qua một số chi phí ghi), PostgreSQL chặn câu ghi với lỗi cannot execute UPDATE in a read-only transaction.
• Có thể dùng làm tín hiệu định tuyến sang replica đọc (qua AbstractRoutingDataSource hoặc proxy).

Nối với 4.5: annotation này chỉ có tác dụng khi method được gọi qua proxy; nếu method readOnly được gọi bằng this từ method @Transactional thường thì nó chạy trong transaction ghi của method ngoài.

Câu hỏi (Hiểu sâu): Vì sao bật hibernate.jdbc.batch_size = 50 rồi mà INSERT vẫn đi từng câu?

Nguyên nhân phổ biến nhất là entity dùng GenerationType.IDENTITY. Với IDENTITY, id chỉ có sau khi DB thực hiện INSERT, mà Hibernate cần id ngay khi persist để đưa entity vào persistence context, nên nó phải gửi INSERT tức thì cho từng entity và không gom batch được. Các nguyên nhân khác: xen kẽ nhiều loại entity mà không bật order_inserts; MySQL chưa có rewriteBatchedStatements=true nên driver vẫn gửi từng câu trong một batch. Cách kiểm chứng: bật generate_statistics và xem số JDBC batches executed, hoặc bật log của driver. Sửa: dùng SEQUENCE với allocationSize (PostgreSQL), hoặc bỏ qua JPA và dùng JdbcTemplate.batchUpdate cho MySQL.

Câu hỏi (Thực chiến): Log báo "HikariPool-1 - Connection is not available, request timed out after 30000ms". Bạn điều tra thế nào?

Pool đã cạn: mọi connection đang bị giữ và request mới chờ quá 30 giây. Việc cần làm là tìm ai đang giữ connection và giữ lâu vì sao, không phải tăng pool size ngay.

1. Xem metrics của Hikari qua Actuator/Micrometer: hikaricp.connections.active chạm maximumPoolSize, pending tăng cao.
2. Tìm nguyên nhân giữ connection lâu: query chậm (kiểm tra slow query log); transaction bao quanh lời gọi HTTP ra ngoài hoặc sleep; open-in-view giữ connection tới hết request; connection leak do tự lấy DataSource.getConnection() mà không đóng. Bật leakDetectionThreshold (ví dụ 20 giây) để Hikari in stack trace nơi mượn connection quá lâu.
3. Thread dump lúc sự cố: nhiều thread đứng ở HikariPool.getConnection là đang chờ; thread đang giữ connection đứng ở đâu cho biết thủ phạm.
4. Sửa đúng gốc: tối ưu query, đưa lời gọi ngoài ra khỏi transaction, tắt open-in-view. Chỉ tăng pool khi đã chứng minh DB còn dư năng lực, và nhớ nhân với số instance.

Câu hỏi đào sâu

"Deadlock ở tầng pool là gì?" — Một request cần hai connection cùng lúc (ví dụ gọi method REQUIRES_NEW khi đang giữ connection của transaction ngoài). Khi mọi thread đều giữ một connection và chờ connection thứ hai, pool cạn vĩnh viễn. Tránh bằng cách không lồng REQUIRES_NEW trong luồng tải cao, hoặc bảo đảm pool size lớn hơn số thread đồng thời nhân với số connection mỗi thread cần.

6.6 Tóm tắt trọng tâm

• Với LEFT JOIN, điều kiện lên bảng bên phải đặt ở ON, vì đặt ở WHERE sẽ loại các dòng NULL và biến nó thành INNER JOIN.
• WHERE lọc dòng trước khi gom nhóm, HAVING lọc nhóm sau khi gom, vì thứ tự thực thi logic là FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
• Window function giữ nguyên từng dòng và gắn thêm giá trị tính trên nhóm; ROW_NUMBER, RANK, DENSE_RANK chỉ khác nhau khi có giá trị bằng nhau, nên phải hỏi rõ yêu cầu trước khi chọn.
• B+Tree giữ key đã sắp xếp và các lá nối nhau, nên tìm bằng, tìm khoảng và ORDER BY đều nhanh; bảng hàng chục triệu dòng chỉ cao 3–4 tầng.
• Composite index chỉ seek được theo tiền tố trái liên tục và dừng seek sau cột range đầu tiên, nên cột so sánh bằng đặt trước, cột range và cột sắp xếp đặt sau.
• Hàm bọc cột, wildcard ở đầu, ép kiểu ngầm làm index vô dụng vì cây được sắp theo giá trị gốc của cột.
• Đọc EXPLAIN bắt đầu từ type, key, rows và Extra; ALL trên bảng lớn và Using filesort trên nhiều dòng là hai tín hiệu cần sửa đầu tiên.
• Keyset pagination nhanh ở mọi trang và không lệch khi có dữ liệu mới, vì nó bám theo giá trị dòng cuối, nhưng cursor phải gồm đủ cột để thứ tự là duy nhất.
• MySQL mặc định REPEATABLE READ, PostgreSQL mặc định READ COMMITTED, và cả hai vẫn để lọt lost update kiểu đọc rồi ghi đè từ ứng dụng.
• MVCC cho đọc và ghi không chặn nhau nhờ giữ nhiều phiên bản dòng, nên transaction mở lâu làm phiên bản cũ không dọn được và DB phình to.
• Trừ kho, trừ số dư ưu tiên atomic update vì kiểm tra và ghi nằm trong một câu; optimistic lock cần retry ở ngoài transaction, pessimistic lock cần transaction bao quanh ở service.
• Deadlock không thể loại bỏ hoàn toàn, nên vừa khóa theo thứ tự nhất quán, vừa retry toàn bộ transaction khi gặp lỗi.
• Entity managed được dirty checking tự ghi xuống DB lúc flush, nên sửa entity để hiển thị mà không map sang DTO sẽ vô tình UPDATE dữ liệu.
• N+1 phát hiện bằng cách đếm query; sửa bằng JOIN FETCH hoặc EntityGraph cho use case cụ thể, batch fetching khi có phân trang, DTO projection cho API chỉ đọc.
• IDENTITY chặn insert batching vì Hibernate cần id ngay khi persist; import lớn thì dùng SEQUENCE pooled hoặc JdbcTemplate.

6.7 Checklist ôn tập

Viết được các loại JOIN, biết bẫy LEFT JOIN kèm điều kiện ở WHERE. Nêu thứ tự thực thi logic của câu SELECT, phân biệt WHERE và HAVING. Viết query tìm trùng, xóa trùng giữ lại một bản, top N mỗi nhóm. Phân biệt ROW_NUMBER, RANK, DENSE_RANK. So sánh DELETE/TRUNCATE/DROP, biết khác biệt MySQL và PostgreSQL. Giải thích B+Tree, clustered index InnoDB, composite index, leftmost prefix, covering index. Liệt kê các trường hợp index bị bỏ qua và đọc được EXPLAIN. So sánh offset và keyset pagination, viết được query keyset. Giải thích ACID, vai trò của WAL/redo log và undo log. Nêu 4 anomaly, 4 isolation level, mặc định của MySQL và PostgreSQL. Giải thích MVCC và thời điểm lấy snapshot theo isolation level. Chọn giữa atomic update, optimistic lock, pessimistic lock; biết SKIP LOCKED. Phòng tránh và xử lý deadlock. Nêu 4 trạng thái entity, persistence context, dirty checking, thời điểm flush. Phân biệt L1 và L2 cache, biết L2 mặc định tắt. Nhớ fetch type mặc định của từng loại quan hệ, xử lý LazyInitializationException, tắt open-in-view. Phát hiện và sửa N+1 bằng JOIN FETCH, EntityGraph, batch size, DTO projection. Phân biệt persist, merge, save, saveAndFlush. Tối ưu bulk insert, biết IDENTITY chặn batching. Dùng Flyway/Liquibase đúng cách, cấu hình HikariCP và điều tra pool cạn.