Lỗi hay gặp với database và transaction (PostgreSQL, ORM) Trung cấp

Thời gian đọc ước tính: 15 phút

Cần đọc trước

Viết transaction trong code: giữ dữ liệu đúng khi nhiều request chạy cùng lúc

Trang này gom các lỗi database mà code review hay bỏ lọt: code chạy đúng trên máy dev với 100 row, rồi chậm, sai dữ liệu hoặc khoá bảng khi lên production.

Mỗi cặp chỉ nêu cách sửa mặc định. Phần giải thích sâu nằm ở Viết transaction trong code và các bài trong nhóm Database.

Nhìn nhanh: lỗi nào, lộ ra khi nào

Lỗi	Trên máy dev	Trên production
N+1, `SELECT *`, OFFSET sâu	Nhanh	Chậm dần theo số row
Check-then-insert, đọc-sửa-ghi	Đúng	Sai dữ liệu khi có request đồng thời
Transaction sai connection	Đúng (một request)	Mất lock, ghi nửa chừng
`NOT IN` gặp `NULL`	Đúng (chưa có `NULL`)	Trả rỗng, không báo lỗi
Thiếu index khoá ngoại, `lower(email)`	Nhanh	Seq scan mỗi lần
Migration khoá bảng	Vài ms	Chặn ghi vài phút

Số đo trong trang chạy trên PostgreSQL 17.11 (Docker của repo), bảng orders 2 triệu row, customer 100 nghìn row.

Truy vấn

N+1: query trong vòng lặp

Hay gặp

// Knex: 1 query lấy user + N query lấy post
const users = await knex('users').select('id', 'name');
for (const u of users) {
  u.posts = await knex('posts').where({ user_id: u.id });
}

// TypeORM: cùng lỗi, viết bằng repository
for (const u of users) {
  u.posts = await postRepo.find({ where: { user: { id: u.id } } });
}

Nên viết

// Knex: 2 query, gom theo user_id trong app
const users = await knex('users').select('id', 'name');
const posts = await knex('posts').whereIn('user_id', users.map((u) => u.id));
const byUser = Map.groupBy(posts, (p) => p.user_id);
for (const u of users) u.posts = byUser.get(u.id) ?? [];

// TypeORM: để ORM load relation
const usersWithPosts = await userRepo.find({ relations: { posts: true } });
// Prisma 7: prisma.user.findMany({ include: { posts: true } })

Vì sao

Mỗi vòng lặp là một round-trip mạng. 3 user tạo 4 query (đếm bằng event query của Knex), 500 user tạo 501 query.
Trên dev với DB local, round-trip gần như bằng 0 nên không ai thấy. Trên production DB ở máy khác, mỗi round-trip thêm vài trăm micro giây đến vài ms.
whereIn sinh where "user_id" in ($1, $2, ...): số query cố định, không phụ thuộc N.

SELECT * và load cả bảng để lọc trong app

Hay gặp

const all = await orderRepo.find();                  // SELECT mọi cột, mọi row
const recent = all
  .filter((o) => o.customerId === customerId)
  .slice(0, 20);

Nên viết

const recent = await orderRepo.find({
  select: { id: true, total: true, createdAt: true },
  where: { customerId },
  order: { createdAt: 'DESC' },
  take: 20,
});

Vì sao

Bảng 2 triệu row thì bản bad kéo 2 triệu object vào RAM của process Node cho mỗi request. Vài request đồng thời là OOM.
Lọc ở database dùng được index và chỉ gửi 20 row qua mạng.
Chọn cột cụ thể còn tránh lộ cột nhạy cảm (password_hash) khi object bị trả thẳng ra API.

NOT IN (subquery) khi subquery có NULL

Hay gặp

-- Khách chưa từng đặt hàng
SELECT id FROM customer
WHERE id NOT IN (SELECT customer_id FROM orders);
-- orders.customer_id nullable, có một đơn khách vãng lai (NULL)
-- → 0 row

Nên viết

SELECT c.id FROM customer c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- → 2 row, đúng

Vì sao

Tài liệu PostgreSQL: nếu không có giá trị bằng và subquery có ít nhất một NULL, NOT IN trả null, không phải true. WHERE coi null là false, nên mọi row bị loại. Chạy thử: 3 khách, 1 đơn của khách 1, 1 đơn NULL → NOT IN trả 0 row, NOT EXISTS trả 2. Bug chỉ xuất hiện từ ngày có row NULL đầu tiên.

OFFSET cho trang sâu trên bảng lớn

Hay gặp

// GET /orders?page=50001
const rows = await db.selectFrom('orders')
  .select(['id', 'created_at'])
  .orderBy('created_at', 'desc').orderBy('id', 'desc')
  .limit(20).offset((page - 1) * 20)
  .execute();

Nên viết

// GET /orders?after=<created_at>,<id> (cursor từ trang trước)
const rows = await db.selectFrom('orders')
  .select(['id', 'created_at'])
  .where(({ eb, refTuple, tuple }) =>
    eb(refTuple('created_at', 'id'), '<', tuple(cursor.createdAt, cursor.id)))
  .orderBy('created_at', 'desc').orderBy('id', 'desc')
  .limit(20)
  .execute();

Vì sao

Tài liệu PostgreSQL: row bị OFFSET bỏ qua vẫn phải được tính trong server. Đo với index (created_at DESC, id DESC): OFFSET 1000000 mất 99 ms (đọc 1.000.020 entry), keyset mất 0,06 ms (đọc 20).
Trang 1 nhanh như nhau, nên test và demo không bắt được. Crawler hoặc người bấm “trang cuối” mới bắt được.
Cursor phải là cặp cột duy nhất (created_at, id). Chi tiết và bẫy: OFFSET và keyset.

Đồng thời: hai request cùng lúc

Hai lỗi dưới đây có chung hình dạng: đọc, quyết định trong app, rồi ghi. Giữa đọc và ghi, request khác chen vào.

Đang tải diagram…

Check-then-insert để chống trùng

Hay gặp

const { rowCount } = await pool.query('SELECT 1 FROM subscriber WHERE email = $1', [email]);
if (rowCount > 0) return 'exists';
await pool.query('INSERT INTO subscriber (email) VALUES ($1)', [email]);
return 'created';

Nên viết

// Schema: email text NOT NULL UNIQUE
const res = await pool.query(
  `INSERT INTO subscriber (email) VALUES ($1)
   ON CONFLICT (email) DO NOTHING RETURNING id`,
  [email],
);
return res.rowCount === 1 ? 'created' : 'exists';

Vì sao

20 request đồng thời cùng một email: bản bad tạo 20 row (một lần khác: 15), bản good tạo đúng 1 row, 19 request nhận exists.
Chỉ constraint trong database mới cưỡng chế được khi có nhiều request, nhiều instance. Kiểm tra trong app chỉ để trả lỗi đẹp.
Với DO NOTHING, row bị bỏ qua không có trong RETURNING. Chi tiết: Pattern 4.

Đọc-sửa-ghi: lost update

Hay gặp

const { rows } = await pool.query('SELECT n FROM counter WHERE id = 1');
await pool.query('UPDATE counter SET n = $1 WHERE id = 1', [rows[0].n + 1]);

Nên viết

// Tính trong database, một câu
await pool.query('UPDATE counter SET n = n + 1 WHERE id = 1');

// Có điều kiện nghiệp vụ: đưa vào WHERE, kiểm tra rowCount
const r = await tx.query(
  'UPDATE product SET stock = stock - $2 WHERE id = $1 AND stock >= $2', [id, qty]);
if (r.rowCount === 0) throw new SoldOut();

Vì sao

50 request “+1” đồng thời: bản bad cho n = 1, bản good cho n = 50. Mọi request đọc cùng giá trị cũ rồi ghi đè hằng số.
Bọc bản bad trong BEGIN/COMMIT không sửa được: ở Read Committed vẫn mất update (demo flash sale bán vượt 40 đơn).
Cần logic app giữa đọc và ghi thì dùng SELECT ... FOR UPDATE. Xem Pattern 1 và Pattern 2.

Transaction

BEGIN/COMMIT bằng pool.query (node-postgres)

Hay gặp

await pool.query('BEGIN');
await pool.query('UPDATE account SET balance = balance - $2 WHERE id = $1', [from, amt]);
await pool.query('UPDATE account SET balance = balance + $2 WHERE id = $1', [to, amt]);
await pool.query('COMMIT');

Nên viết

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE account SET balance = balance - $2 WHERE id = $1', [from, amt]);
  await client.query('UPDATE account SET balance = balance + $2 WHERE id = $1', [to, amt]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK').catch(() => {});
  throw err;
} finally {
  client.release();
}

Vì sao

Mỗi pool.query mượn một connection bất kỳ, nên bốn câu có thể chạy trên bốn connection. Tài liệu node-postgres ghi thẳng: không dùng transaction với pool.query. Hậu quả đo được: lock nhả sớm, bán vượt, và BEGIN mồ côi giữ lock mãi. Xem Lỗi kinh điển của node-postgres.

Trong callback transaction lại dùng knex/db thay vì trx

Hay gặp

// Knex
await knex.transaction(async (trx) => {
  await trx('orders').insert(order);
  await knex('audit_log').insert(log);     // knex, không phải trx
});

// Kysely
await db.transaction().execute(async (trx) => {
  await trx.insertInto('orders').values(order).execute();
  await db.insertInto('audit_log').values(log).execute(); // db, không phải trx
});

Nên viết

await knex.transaction(async (trx) => {
  await trx('orders').insert(order);
  await trx('audit_log').insert(log);
});

await db.transaction().execute(async (trx) => {
  await trx.insertInto('orders').values(order).execute();
  await trx.insertInto('audit_log').values(log).execute();
});
// TypeORM: dùng manager được truyền vào; Prisma 7: dùng tx, không dùng prisma

Vì sao

Đã chạy thử với Knex và Kysely: callback ném lỗi sau hai câu insert. Câu qua trx bị rollback, câu qua knex/db vẫn còn trong bảng.
Câu qua knex/db chạy trên connection khác, ngoài transaction: không thấy dữ liệu chưa commit, không được bảo vệ bởi lock của transaction.
Hay gặp khi repository giữ knex/db làm field. Cho hàm repository nhận tham số executor. Bảng API từng ORM: ORM: cùng nguyên tắc.

Gọi HTTP bên trong transaction

Hay gặp

await prisma.$transaction(async (tx) => {
  await tx.product.update({ where: { id }, data: { stock: { decrement: 1 } } });
  await paymentGateway.charge(card, amount);       // HTTP 1-3 giây
  await tx.order.create({ data: { productId: id, status: 'paid' } });
});

Nên viết

const order = await prisma.$transaction(async (tx) => {
  await tx.product.update({ where: { id }, data: { stock: { decrement: 1 } } });
  const order = await tx.order.create({ data: { productId: id, status: 'pending_payment' } });
  await tx.outbox.create({ data: { type: 'payment.requested', payload: { orderId: order.id } } });
  return order;
});
// worker đọc outbox và gọi cổng thanh toán sau khi commit

Vì sao

Lock trên row product bị giữ suốt thời gian chờ bên thứ ba. Mọi request mua cùng sản phẩm xếp hàng theo.
Rollback không thu hồi được tiền đã charge. Prisma 7 còn có timeout mặc định 5 giây cho interactive transaction: gateway chậm là transaction bị huỷ sau khi đã charge.
Tài liệu Prisma 7 khuyên tránh network request trong transaction. Chi tiết: Giữ transaction ngắn, Transactional outbox.

Index và migration

Khoá ngoại không có index

Hay gặp

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customer(id) ON DELETE CASCADE
);
-- Nghĩ rằng REFERENCES tự tạo index. Không có.

Nên viết

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customer(id) ON DELETE CASCADE
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Vì sao

PostgreSQL tự index phía được tham chiếu (primary key), không tự index cột khoá ngoại. Tài liệu giải thích: vì không phải lúc nào cũng cần.
Thiếu index: WHERE customer_id = 42 là parallel seq scan 27 ms; xoá một customer tốn 40,7 ms trong trigger khoá ngoại. Có index: 1,2 ms và 0,09 ms.
Xoá hàng loạt customer thì mỗi row là một lần quét orders. Đây là nguồn của nhiều job “dọn dữ liệu” chạy cả giờ.

WHERE lower(email) = ... mà chỉ có index trên email

Hay gặp

CREATE UNIQUE INDEX customer_email_idx ON customer (email);

SELECT id FROM customer WHERE lower(email) = lower($1);
-- Seq Scan on customer, 11 ms trên 100 nghìn row

Nên viết

CREATE UNIQUE INDEX customer_email_lower_idx ON customer (lower(email));

SELECT id FROM customer WHERE lower(email) = lower($1);
-- Index Scan using customer_email_lower_idx, 0,03 ms

Vì sao

Index trên email lưu giá trị gốc; planner không dùng được nó cho biểu thức lower(email). Muốn dùng index thì biểu thức trong query phải khớp biểu thức của index.
UNIQUE trên lower(email) còn chặn trùng An@a.vn và an@a.vn, điều index thường không làm được.
Thêm cách chẩn đoán khi index không được dùng: Expression index, Index không được dùng.

Migration: thêm cột với default volatile

Hay gặp

ALTER TABLE orders ADD COLUMN public_id uuid DEFAULT gen_random_uuid();
-- 2 triệu row: 2,9 giây, rewrite cả bảng và index,
-- giữ ACCESS EXCLUSIVE suốt thời gian đó (chặn cả đọc)

Nên viết

-- 1. Thêm cột không default: chỉ sửa catalog (2,5 ms)
ALTER TABLE orders ADD COLUMN public_id uuid;
-- 2. Default chỉ áp cho row mới, không đụng row cũ (0,9 ms)
ALTER TABLE orders ALTER COLUMN public_id SET DEFAULT gen_random_uuid();
-- 3. Backfill theo lô nhỏ trong job riêng, rồi mới thêm NOT NULL nếu cần
UPDATE orders SET public_id = gen_random_uuid()
WHERE id IN (SELECT id FROM orders WHERE public_id IS NULL LIMIT 5000);

Vì sao

Từ PostgreSQL 11, default non-volatile (hằng số, now()) được tính một lần và lưu ở catalog: không rewrite. Đo trên PG 17: DEFAULT 'new' mất 3 ms, DEFAULT now() mất 0,5 ms, relfilenode không đổi.
Default volatile (gen_random_uuid(), random(), clock_timestamp()) phải tính riêng cho từng row nên PostgreSQL rewrite cả bảng: relfilenode đổi, 2,9 giây cho 2 triệu row.
ADD COLUMN luôn lấy ACCESS EXCLUSIVE. Bản good chỉ giữ nó vài ms, nhưng vẫn nên đặt lock_timeout cho migration để không xếp hàng sau một query dài.

Migration: CREATE INDEX trên bảng đang nhận ghi

Hay gặp

// Knex migration, mặc định chạy trong transaction
export async function up(knex) {
  await knex.raw('CREATE INDEX orders_total_idx ON orders (total)');
}

Nên viết

// CONCURRENTLY không chạy được trong transaction: tắt cho riêng file này
export const config = { transaction: false };

export async function up(knex) {
  await knex.raw('CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_total_idx ON orders (total)');
}

Vì sao

CREATE INDEX thường lấy ShareLock (đã kiểm tra trong pg_locks): đọc vẫn chạy, mọi INSERT/UPDATE/DELETE chờ đến khi build xong. Bảng lớn là vài phút đứng ghi.
Chạy CONCURRENTLY trong transaction báo lỗi cannot run inside a transaction block. Knex cho tắt transaction từng file qua config.transaction.
CONCURRENTLY lỗi giữa chừng để lại index INVALID vẫn tốn chi phí ghi. Kiểm tra \d, DROP INDEX rồi chạy lại.

Checklist review

Tiếp theo: API & bảo mật.

#engineering #postgresql #transaction #orm #knex #kysely #typeorm #prisma #performance