Lock & concurrent transaction trong PostgreSQL

Phần "vì sao cần lock" và @Lock/@Version trong JPA đã có ở bài 3.5 và bài 4.3.10. Bài này nhìn từ phía PostgreSQL.

1. Bài toán

shop-app có 3 việc chạy đồng thời:

• Checkout: trừ wallets, trừ products.stock.
• Worker (3 pod): lấy order PENDING để gửi sang payment, mỗi order chỉ được xử lý một lần.
• Deploy: migration ALTER TABLE orders ADD COLUMN ....

2. Vấn đề

• 3 worker cùng SELECT ... WHERE status='PENDING' FOR UPDATE → worker 2, 3 xếp hàng chờ worker 1, không song song được.
• Migration chờ một transaction dài → mọi SELECT sau nó cũng xếp hàng → API treo toàn bộ.
• Chuyển tiền A→B và B→A cùng lúc → deadlock.

3. Giải pháp: các loại lock

Row lock (khóa từng row, lưu ngay trong tuple, không giới hạn số lượng):

SELECT thường không lấy row lock (MVCC, bài 6.3).

Tùy chọn khi row đang bị khóa: mặc định chờ; NOWAIT lỗi ngay; SKIP LOCKED bỏ qua row đó.

Table lock (8 mode). Những cái hay gặp:

4. Code từng bước

Bước 1: Hàng đợi job bằng SKIP LOCKED

public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query(value = """
        SELECT * FROM orders
        WHERE status = 'PENDING'
        ORDER BY created_at
        LIMIT :size
        FOR UPDATE SKIP LOCKED
        """, nativeQuery = true)
    List<Order> lockNextBatch(@Param("size") int size);
}

@Service
@RequiredArgsConstructor
public class PaymentDispatcher {
    private final OrderRepository orders;

    @Transactional
    public int dispatchBatch() {
        List<Order> batch = orders.lockNextBatch(50);   // mỗi pod lấy 50 order KHÁC nhau
        batch.forEach(o -> o.setStatus(OrderStatus.DISPATCHING));
        return batch.size();
    }   // commit → nhả lock; gọi payment ở bước sau, ngoài transaction
}

Bước 2: Timeout cho lock

spring:
  datasource:
    hikari:
      connection-init-sql: SET lock_timeout = '3s'; SET statement_timeout = '10s'

-- Trong migration: không để ALTER chờ vô hạn rồi chặn mọi người phía sau
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;    -- lỗi nhanh thì retry lúc vắng

Bước 3: Tránh deadlock khi chuyển tiền

@Transactional
public void transfer(long fromUser, long toUser, BigDecimal amount) {
    long first = Math.min(fromUser, toUser), second = Math.max(fromUser, toUser);
    walletRepository.lockByUserId(first);    // SELECT ... FOR UPDATE, luôn theo thứ tự id tăng
    walletRepository.lockByUserId(second);
    walletRepository.debit(fromUser, amount);
    walletRepository.credit(toUser, amount);
}

Bước 4: Advisory lock: lock theo một số tùy ý, không gắn với row. Ví dụ chỉ cho một pod chạy job tính thưởng mỗi đêm:

@Transactional
public void nightlyRewards() {
    Boolean got = jdbc.queryForObject(
        "SELECT pg_try_advisory_xact_lock(?)", Boolean.class, 20261014L);
    if (!Boolean.TRUE.equals(got)) return;     // pod khác đang chạy
    // ... tính thưởng; lock tự nhả khi transaction kết thúc
}

Bước 5: Khi bị treo: ai chặn ai

SELECT pid, state, wait_event_type, now() - xact_start AS tx_age,
       pg_blocking_pids(pid) AS blocked_by, left(query, 80)
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks WHERE NOT granted;

SELECT pg_cancel_backend(12345);     -- hủy câu đang chạy
SELECT pg_terminate_backend(12345);  -- đóng hẳn connection (cẩn thận)

Bước 6: Test

for i in $(seq 1 20); do
  curl -s -X POST localhost:8080/api/transfers -H 'Content-Type: application/json' \
    -d '{"from":1,"to":2,"amount":10}' &
  curl -s -X POST localhost:8080/api/transfers -H 'Content-Type: application/json' \
    -d '{"from":2,"to":1,"amount":10}' &
done; wait
# log không có "deadlock detected"; tổng balance hai ví không đổi

5. Cách hoạt động

Deadlock:
  T1: lock wallet 1 ──────── chờ wallet 2
  T2: lock wallet 2 ──────── chờ wallet 1
  Sau deadlock_timeout (1s) PostgreSQL kiểm tra đồ thị chờ, thấy vòng,
  hủy 1 transaction: ERROR 40P01 deadlock detected → app retry

Hàng đợi lock bảng (vì sao migration làm treo cả API):
  T_report (SELECT dài)     giữ ACCESS SHARE
  ALTER TABLE               xin ACCESS EXCLUSIVE → CHỜ
  SELECT của API #1..#500   xin ACCESS SHARE → xếp SAU ALTER → CHỜ
  ⇒ đặt lock_timeout cho migration

SKIP LOCKED:
  pod A: PENDING 1..50 (lock)   pod B: bỏ qua 1..50, lấy 51..100   pod C: 101..150

6. So sánh

7. Khi nào nên dùng / không nên

• Transaction ngắn; không gọi HTTP, Kafka khi đang giữ lock.
• Khóa nhiều row thì khóa theo thứ tự cố định; vẫn phải retry khi gặp 40P01.
• Luôn có lock_timeout cho app và migration.
• Không dùng LOCK TABLE trong code nghiệp vụ.
• Advisory lock session-level cẩn thận với connection pool: quên unlock thì lock đi theo connection về pool. Ưu tiên pg_try_advisory_xact_lock.

Có khóa. Mọi UPDATE/DELETE đều tự lấy row lock trên các row nó sửa (UPDATE thường là FOR NO KEY UPDATE) và giữ đến hết transaction. FOR UPDATE chỉ là cách khóa sớm hơn, từ lúc SELECT, để giữa lúc đọc và lúc ghi không ai chen vào.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 42;   -- row 42 bị khóa từ đây
-- ... làm thêm 5 giây ...
COMMIT;                                               -- đến đây mới nhả
-- trong 5 giây đó, mọi UPDATE khác vào row 42 phải chờ

Đó là lý do câu atomic UPDATE nên đặt cuối transaction và transaction phải ngắn: sản phẩm hot sẽ bị nghẽn ở đúng row đó.

Bạn nghĩ đúng một nửa: deadlock thường xuyên là dấu hiệu thứ tự lock lộn xộn, nên sửa. Nhưng tránh 100% thì khó: FK lấy FOR KEY SHARE ngầm, UPDATE nhiều row theo thứ tự index khác nhau, trigger... đều có thể tạo vòng chờ. Nên làm cả hai:

1. Giảm xác suất: khóa theo thứ tự id, transaction ngắn, UPDATE nhiều row thì ORDER BY id khi SELECT FOR UPDATE.
2. Chịu được: bắt 40P01 (Spring: CannotAcquireLockException/DeadlockLoserDataAccessException) và retry cả transaction vài lần, kèm idempotency.

Bật log_lock_waits = on để log ghi lại câu nào chờ lâu và chi tiết deadlock.

Q1. PostgreSQL có những loại lock nào?

🇻🇳 Tiếng Việt:

Có row lock và table lock. Row lock gồm FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE; UPDATE và DELETE tự lấy row lock, SELECT thường thì không nhờ MVCC. Table lock có 8 mode, ví dụ SELECT lấy ACCESS SHARE, DML lấy ROW EXCLUSIVE, còn phần lớn ALTER TABLE lấy ACCESS EXCLUSIVE chặn cả đọc. Ngoài ra có advisory lock để khóa theo một khóa ứng dụng tự định nghĩa.

🇬🇧 English:

There are row locks and table locks. Row locks are FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE; UPDATE and DELETE take row locks automatically, while plain SELECT does not because of MVCC. Table locks have eight modes: SELECT takes ACCESS SHARE, DML takes ROW EXCLUSIVE, and most ALTER TABLE statements take ACCESS EXCLUSIVE, which blocks even reads. There are also advisory locks keyed by an application-defined number.

Q2. Nhiều worker cùng lấy job từ một bảng, làm sao không trùng và không chờ nhau?

🇻🇳 Tiếng Việt:

Em dùng SELECT ... FOR UPDATE SKIP LOCKED kèm LIMIT. Mỗi worker khóa một batch row, các worker khác bỏ qua những row đang bị khóa và lấy row tiếp theo. Trong cùng transaction em đổi trạng thái sang đang xử lý rồi commit ngay; việc gọi hệ thống ngoài làm sau khi commit để không giữ lock lâu.

🇬🇧 English:

I use SELECT ... FOR UPDATE SKIP LOCKED with a LIMIT. Each worker locks a batch of rows, and the other workers skip the locked rows and take the next ones. In the same transaction I mark them as processing and commit right away; external calls happen after the commit so locks are not held for long.

Q3. Xử lý deadlock và lock chờ lâu trong database thế nào?

🇻🇳 Tiếng Việt:

PostgreSQL tự phát hiện deadlock sau deadlock_timeout và hủy một transaction với lỗi 40P01. Em giảm deadlock bằng cách khóa theo thứ tự cố định và giữ transaction ngắn, đồng thời retry transaction bị hủy. Để không treo, em đặt lock_timeout và statement_timeout, đặc biệt cho migration. Khi có sự cố em xem pg_stat_activity với pg_blocking_pids và pg_locks để tìm session đang chặn.

🇬🇧 English:

PostgreSQL detects deadlocks after deadlock_timeout and aborts one transaction with error 40P01. I reduce deadlocks by locking in a consistent order and keeping transactions short, and I retry the aborted transaction. To avoid hanging I set lock_timeout and statement_timeout, especially for migrations. During an incident I check pg_stat_activity with pg_blocking_pids and pg_locks to find the blocking session.

Cách nhớ ngắn gọn

• UPDATE tự khóa row; FOR UPDATE chỉ là khóa sớm từ lúc đọc.
• Hàng đợi job → FOR UPDATE SKIP LOCKED.
• ALTER TABLE = ACCESS EXCLUSIVE → luôn kèm lock_timeout.
• Deadlock: thứ tự cố định + retry 40P01.
• Treo → pg_stat_activity + pg_blocking_pids.