Transaction & Concurrency ControlTransactions & Concurrency Control

Thuộc bộ kiến thức PostgreSQL DBA Roadmap.

Tổng quan

Mọi database đều phải trả lời một câu hỏi khó chịu như nhau: điều gì xảy ra khi hai thao tác cùng đụng vào một dữ liệu tại cùng một thời điểm? Câu trả lời của PostgreSQL xoay quanh hai trụ cột — transaction, gom nhiều statement thành một đơn vị công việc “tất cả hoặc không gì cả”, và MVCC (Multi-Version Concurrency Control), cơ chế cho phép reader và writer cùng tồn tại mà không giẫm chân nhau. Bài viết này giả định bạn đã nắm các khái niệm relational database cơ bản (table, row, SELECT/INSERT/UPDATE/DELETE) trong ../backend/en/08-relational-databases.md, và sẽ đi sâu vào cách PostgreSQL cụ thể triển khai transactional isolation và concurrency.

Hiểu rõ phần này mang lại lợi ích thực tế rất lớn. Phần lớn các sự cố kiểu “sao query của tôi bị treo”, “sao tôi bị lỗi serialization”, “sao table bị bloat” đều bắt nguồn từ việc hiểu sai về MVCC, isolation level, hoặc locking. Nắm đúng mental model một lần, và cả một lớp lớn các bất ngờ ở production sẽ không còn là bí ẩn nữa.

Kiến thức nền tảng

Transaction: BEGIN, COMMIT, ROLLBACK

Một transaction là một chuỗi một hoặc nhiều SQL statement mà PostgreSQL coi là một đơn vị duy nhất: hoặc hiệu ứng của tất cả statement trở thành vĩnh viễn (COMMIT), hoặc không statement nào có hiệu lực cả (ROLLBACK). Thực ra mọi statement trong PostgreSQL đều chạy bên trong một transaction — nếu bạn không chủ động mở transaction, PostgreSQL sẽ tự bọc từng statement riêng lẻ trong một transaction ngầm. Transaction tường minh (explicit) dùng khi bạn cần nhiều statement cùng thành công hoặc cùng thất bại.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Nếu có gì đó sai giữa BEGIN và COMMIT — vi phạm constraint, lỗi ứng dụng, hoặc chủ động quyết định huỷ — bạn dùng ROLLBACK thay vì COMMIT, và mọi thứ sẽ như thể các statement chưa từng chạy.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- có gì đó không ổn ở đây, huỷ giao dịch
ROLLBACK;

SAVEPOINT: rollback một phần bên trong transaction

Đôi khi bạn không muốn huỷ toàn bộ transaction chỉ vì một bước bị lỗi — bạn chỉ muốn undo bước đó và tiếp tục các bước còn lại. SAVEPOINT tạo một điểm đánh dấu đặt tên mà bạn có thể rollback về đó mà không phải huỷ cả transaction.

BEGIN;

INSERT INTO orders (id, customer_id, total) VALUES (501, 42, 199.99);

SAVEPOINT before_discount;

UPDATE orders SET total = total * 0.5 WHERE id = 501;  -- áp dụng giảm giá
-- kiểm tra business rule thất bại: customer tier này không được giảm giá
ROLLBACK TO SAVEPOINT before_discount;

-- order vẫn tồn tại với total ban đầu; ta chỉ undo phần giảm giá
UPDATE orders SET status = 'confirmed' WHERE id = 501;

COMMIT;

ROLLBACK TO SAVEPOINT undo mọi thứ kể từ savepoint đó nhưng vẫn giữ transaction mở, để bạn có thể thử cách khác và vẫn commit ở cuối. Đây chính là cơ chế hầu hết ORM dùng để giả lập “nested transaction” — thực chất PostgreSQL không có nested transaction thật, chỉ có savepoint bên trong một transaction ngoài cùng. Một chi tiết liên quan đáng lưu ý: nếu một statement bên trong transaction bị lỗi và bạn không rollback về savepoint, toàn bộ transaction sẽ trở nên không dùng được (“current transaction is aborted, commands ignored until end of transaction block”) cho đến khi bạn ROLLBACK toàn bộ hoặc rollback về một savepoint được tạo trước lỗi đó.

Khái niệm chính

MVCC: tại sao reader không bao giờ chặn writer và writer không bao giờ chặn reader

Các database dựa trên lock kiểu cổ điển xử lý truy cập đồng thời bằng cách bắt reader và writer tranh giành lock: writer giữ exclusive lock trong lúc sửa row, và bất kỳ reader nào muốn đọc row đó phải chờ. PostgreSQL đi theo một hướng hoàn toàn khác gọi là MVCC — Multi-Version Concurrency Control.

Ý tưởng cốt lõi: thay vì sửa row tại chỗ và khoá không cho ai đọc, PostgreSQL giữ lại nhiều phiên bản của một row. Khi một transaction update một row, PostgreSQL không ghi đè dữ liệu cũ — nó ghi một phiên bản row hoàn toàn mới và đánh dấu phiên bản cũ là đã bị thay thế. Mỗi transaction hoạt động dựa trên một snapshot nhất quán — góc nhìn riêng của nó về “những phiên bản row nào tồn tại tại thời điểm này” — nên một reader bắt đầu trước khi writer update đơn giản là chưa thấy phiên bản mới, và tiếp tục đọc phiên bản cũ, hoàn toàn không biết rằng đang có một write diễn ra.

Đây chính là đặc tính nổi bật của PostgreSQL: reader không bao giờ chặn writer, và writer không bao giờ chặn reader. Một query analytical chạy lâu có thể scan một table trong nhiều phút trong khi các transaction khác thoải mái insert, update, delete row trong chính table đó, và cả hai phía đều không phải chờ nhau khi chỉ có read và write thuần tuý. Đây là một lợi thế thực tế rất lớn so với concurrency dựa trên lock ngây thơ, nơi mà một SELECT chạy lâu có thể làm treo mọi writer, hoặc một writer bận rộn có thể khiến mọi reader đói. (Writer vẫn chặn writer khác nếu cùng đụng vào một row — MVCC giải quyết tranh chấp reader/writer, không phải writer/writer, đó là lúc row-level locking, sẽ nói bên dưới, phát huy tác dụng.)

Cái giá của siêu năng lực này là các phiên bản row cũ không tự nhiên biến mất — chúng trở thành dead tuple mà cuối cùng phải được dọn dẹp. Chừng nào còn một transaction nào đó có thể cần đến phiên bản cũ, PostgreSQL không thể xoá nó. Khi không transaction nào còn cần đến nữa, nó trở thành không gian có thể thu hồi, và tiến trình VACUUM chịu trách nhiệm thu hồi không gian đó. Vòng đời tuple này, cùng kỷ luật vận hành xoay quanh nó, được trình bày chi tiết trong ./11-storage-internals-and-vacuum.md — nhưng điều quan trọng cần khắc sâu ngay bây giờ là lợi ích concurrency của MVCC không miễn phí: cái giá phải trả là dead tuple tích luỹ mà VACUUM phải liên tục dọn dẹp.

MVCC thực sự hoạt động thế nào: xmin, xmax và snapshot

Mỗi row vật lý (gọi là tuple ở tầng storage của PostgreSQL) mang theo một vài cột hệ thống ẩn mà bạn không thấy trong SELECT * bình thường, nhưng có thể query trực tiếp:

Cột hệ thống	Ý nghĩa
xmin	Transaction ID (XID) của transaction đã tạo ra phiên bản tuple này
xmax	Transaction ID của transaction đã xoá hoặc update đi phiên bản tuple này (bằng 0/null nếu đây vẫn là phiên bản hiện hành)
ctid	Vị trí vật lý của phiên bản tuple này trên đĩa

Bạn có thể xem trực tiếp các cột này:

SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;

Khi một transaction chạy, PostgreSQL gán cho nó một snapshot, về cơ bản nói rằng: “các transaction có XID nằm trong danh sách này đã commit và hiển thị với tôi; các transaction có XID sau thời điểm này, hoặc đang chạy dở, thì không hiển thị với tôi.” Một tuple hiển thị với snapshot của một transaction nếu xmin của nó thuộc về một transaction đã commit mà snapshot coi là “đã xảy ra rồi”, và xmax của nó hoặc rỗng hoặc thuộc về một transaction mà snapshot không coi là đã xảy ra (tức việc xoá/update đó “chưa xảy ra” theo góc nhìn của snapshot này).

Ví dụ minh hoạ. Giả sử accounts.balance của id = 1 hiện là 500, được tạo bởi transaction XID 100 (nên xmin = 100, xmax rỗng).

1. Transaction B bắt đầu một lần đọc: BEGIN; SELECT balance FROM accounts WHERE id = 1; → thấy 500. Snapshot của B đã được chụp tại thời điểm này; từ giờ nó có một góc nhìn cố định về “cái gì đang tồn tại.”
2. Trước khi B kết thúc, transaction A chạy và commit: BEGIN; UPDATE accounts SET balance = 600 WHERE id = 1; COMMIT; (giả sử A là XID 105). Bên trong, PostgreSQL không ghi đè tuple cũ. Nó đánh dấu xmax = 105 cho tuple cũ và insert một tuple hoàn toàn mới với balance = 600, xmin = 105.
3. Transaction B, vẫn đang trong transaction ban đầu, đọc lại row đó: SELECT balance FROM accounts WHERE id = 1;. Tuỳ vào isolation level của B (xem bên dưới), B hoặc vẫn thấy 500 (nếu dùng một snapshot chung cho cả transaction) hoặc thấy giá trị mới 600 (nếu mỗi statement lấy một snapshot mới). Dù thế nào, tại không thời điểm nào B phải chờ A, và A cũng không phải chờ B đọc xong.

Đây chính là cơ chế đằng sau “reader không chặn writer, writer không chặn reader” — nó không phải một mẹo timeout của lock, mà là hệ quả trực tiếp của việc phiên bản cũ vẫn tiếp tục tồn tại về mặt vật lý và hiển thị với các snapshot đã bắt đầu trước khi phiên bản mới xuất hiện.

Isolation level

Chuẩn SQL định nghĩa bốn isolation level, và PostgreSQL hỗ trợ ba trong số đó như các hành vi khác biệt thực sự (nó coi yêu cầu READ UNCOMMITTED như READ COMMITTED, vì thiết kế MVCC của PostgreSQL khiến dirty read thật sự trở nên bất khả thi bất kể level nào được yêu cầu).

• Read Committed (mặc định của PostgreSQL): mỗi statement trong một transaction lấy một snapshot mới riêng. Nghĩa là hai SELECT trong cùng một transaction có thể thấy dữ liệu khác nhau nếu có transaction khác commit ở giữa — mỗi query chỉ thấy dữ liệu đã commit trước khi query đó bắt đầu.
• Repeatable Read: toàn bộ transaction dùng một snapshot duy nhất, chụp tại thời điểm câu query đầu tiên. Mọi statement trong transaction đó thấy chính xác cùng một dữ liệu, bất kể transaction khác commit gì trong lúc đó. Nếu một transaction đồng thời update một row mà bạn cũng đang cố update, PostgreSQL phát hiện xung đột và trả lỗi serialization thay vì âm thầm áp update của bạn lên trên dữ liệu đã cũ.
• Serializable: level nghiêm ngặt nhất của PostgreSQL. Nó dùng SSI (Serializable Snapshot Isolation), bắt đầu từ hành vi snapshot giống Repeatable Read nhưng thêm việc theo dõi các phụ thuộc read/write giữa các transaction đồng thời để phát hiện những mẫu hình có thể tạo ra kết quả không nhất quán với bất kỳ thứ tự thực thi tuần tự (serial, từng cái một) nào — kể cả những mẫu hình mà không transaction nào trực tiếp ghi đè dữ liệu của transaction kia (dị thường kinh điển write skew). Khi SSI phát hiện mẫu hình như vậy, nó huỷ một trong các transaction với lỗi serialization failure, và ứng dụng được kỳ vọng phải retry.

Bạn thiết lập isolation level cho từng transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

hoặc đặt mặc định cho session/database qua default_transaction_isolation.

Các dị thường (anomaly): mỗi level cho phép hay ngăn chặn gì

Isolation level	Dirty read	Non-repeatable read	Phantom read	Write skew
Read Committed	Ngăn chặn	Có thể xảy ra	Có thể xảy ra	Có thể xảy ra
Repeatable Read	Ngăn chặn	Ngăn chặn	Ngăn chặn*	Có thể xảy ra
Serializable	Ngăn chặn	Ngăn chặn	Ngăn chặn	Ngăn chặn

* Cách PostgreSQL triển khai Repeatable Read mạnh hơn yêu cầu tối thiểu của chuẩn SQL, và trên thực tế cũng ngăn chặn phantom read, vì nó dùng một snapshot MVCC duy nhất cho cả transaction thay vì lock lại từng khoảng (range).

Một điểm tinh tế đáng nói rõ: chuẩn SQL về mặt kỹ thuật cho phép dirty read ở isolation level lỏng nhất, nhưng PostgreSQL không bao giờ cho phép dirty read, ở bất kỳ isolation level nào — kể cả khi bạn yêu cầu READ UNCOMMITTED. Dirty read nghĩa là thấy được thay đổi chưa commit của transaction khác; vì cơ chế snapshot của PostgreSQL chỉ bao giờ coi tuple của các transaction đã commit là ứng viên hiển thị, nên một thay đổi chưa commit vốn dĩ là vô hình với mọi transaction khác, ngay từ thiết kế. Đây là lợi thế thực sự so với các engine khác nơi READ UNCOMMITTED thực sự nghĩa là “thấy bất kỳ thứ gì đang nằm trong bộ nhớ, dù đã commit hay chưa.”

Định nghĩa ngắn gọn:

• Dirty read: đọc dữ liệu được ghi bởi một transaction chưa commit (và có thể sau đó bị rollback).
• Non-repeatable read: đọc cùng một row hai lần trong một transaction và nhận giá trị khác nhau vì transaction khác đã commit thay đổi ở giữa.
• Phantom read: chạy lại cùng một query (ví dụ điều kiện range) và nhận được một tập row khác vì transaction khác đã insert hoặc delete row khớp điều kiện đó.
• Write skew: hai transaction cùng đọc dữ liệu chồng lấn nhau, mỗi bên kiểm tra một bất biến (invariant) nghiệp vụ và thấy nó đúng dựa trên những gì đã đọc, rồi mỗi bên ghi dựa trên kết quả kiểm tra đó — nhưng bất biến bị vi phạm sau khi cả hai write cùng xảy ra, vì không bên nào thấy được write của bên kia. Ví dụ kinh điển: một quy tắc bệnh viện yêu cầu luôn có ít nhất một bác sĩ trực; hai bác sĩ đang trực độc lập kiểm tra “còn ai khác đang trực không?”, cả hai đều thấy “có”, và cả hai đều tự rút khỏi ca trực — kết quả là không còn ai trực.

Trường hợp sử dụng điển hình theo từng level

Isolation level	Trường hợp sử dụng điển hình
Read Committed	Mặc định cho hầu hết workload OLTP — request của web app, CRUD đơn giản, các trường hợp mà mỗi statement được “làm mới” độc lập là ổn hoặc thậm chí mong muốn
Repeatable Read	Báo cáo hoặc business logic nhiều bước cần một góc nhìn nhất quán duy nhất về dữ liệu trong suốt thời gian chạy (ví dụ tạo báo cáo tài chính phải nhất quán nội bộ)
Serializable	Logic quan trọng về mặt đúng đắn với các bất biến kiểu read-rồi-write trên nhiều row (ví dụ đảm bảo “tổng số ghế đã đặt không vượt quá sức chứa” khi nhiều transaction cùng đặt ghế đồng thời) — chấp nhận rằng một số transaction sẽ cần retry tự động khi gặp serialization failure

Quản lý lock (lock management)

MVCC giải quyết tranh chấp read/write, nhưng writer cùng đụng vào một row vẫn cần phối hợp — đó là lý do có row-level lock và table-level lock.

Row-level lock

Một UPDATE hoặc DELETE ngầm giữ row-level lock trên mọi row nó đụng tới, giữ cho đến khi transaction commit hoặc rollback, ngăn transaction khác sửa (hoặc, ở chế độ nghiêm ngặt hơn, thậm chí lock) cùng row đó đồng thời. Bạn cũng có thể chủ động lấy row lock mà chưa sửa dữ liệu, bằng SELECT ... FOR UPDATE hoặc SELECT ... FOR SHARE:

• FOR UPDATE — lock các row được chọn như thể bạn sắp UPDATE chúng; transaction khác không thể lấy bất kỳ lock nào trên các row này (kể cả FOR UPDATE/FOR SHARE của chính họ) cho đến khi transaction của bạn kết thúc.
• FOR SHARE — một lock yếu hơn, cho phép transaction khác cũng read-lock cùng row (để đọc chia sẻ) nhưng ngăn họ sửa hoặc lock kiểu FOR UPDATE trên các row đó.

Mẫu hình kinh điển: lock một row trước khi update để tránh race condition. Giả sử bạn đang giảm tồn kho và không bao giờ được để số lượng âm, kể cả khi có nhiều đơn hàng đồng thời:

BEGIN;

SELECT quantity FROM inventory WHERE product_id = 77 FOR UPDATE;
-- ứng dụng kiểm tra: quantity >= số lượng yêu cầu hay không?

UPDATE inventory SET quantity = quantity - 3 WHERE product_id = 77;

COMMIT;

Nếu không có FOR UPDATE, hai transaction đồng thời có thể cùng đọc quantity = 5, cùng quyết định “được, còn đủ 3”, và cùng tiến hành trừ 3 — bán vượt tồn kho 3 đơn vị dù mỗi UPDATE riêng lẻ vẫn an toàn khi xét độc lập (race condition kinh điển kiểu đọc-rồi-ghi). FOR UPDATE buộc transaction thứ hai phải chờ cho đến khi transaction thứ nhất commit (hoặc rollback), lúc đó nó đọc lại giá trị hiện hành và ra quyết định dựa trên dữ liệu mới nhất.

Table-level lock mode (tổng quan ngắn gọn)

Hầu hết các statement SQL tự động lấy một table-level lock, ở mức được chọn sao cho chỉ xung đột với những thao tác thực sự không tương thích. Bạn hiếm khi tự yêu cầu các lock này trực tiếp, nhưng biết hai đầu cực trị giúp suy luận về việc chặn (blocking):

Lock mode	Thường được lấy bởi	Xung đột với
ACCESS SHARE	SELECT thông thường	Chỉ ACCESS EXCLUSIVE
ROW EXCLUSIVE	INSERT, UPDATE, DELETE	Các lock như SHARE, EXCLUSIVE, và mạnh hơn
SHARE	CREATE INDEX (không có CONCURRENTLY)	ROW EXCLUSIVE và mạnh hơn — chặn write
ACCESS EXCLUSIVE	ALTER TABLE, DROP TABLE, TRUNCATE	Mọi thứ, kể cả SELECT thông thường

Điểm thực tế cần nhớ: một SELECT gần như không bao giờ chặn thứ gì khác, nhưng một thay đổi schema như ALTER TABLE lấy lock mạnh nhất có thể và sẽ xếp hàng chờ (và chặn) gần như mọi hoạt động khác trên table đó — nguyên nhân phổ biến gây downtime bất ngờ khi chạy migration trên một production table đang bận rộn. Chủ đề này sẽ được nhìn lại dưới góc độ performance tuning ở ./10-query-planning-and-performance-tuning.md.

Advisory lock

Đôi khi bạn cần phối hợp logic ứng dụng không liên quan đến bất kỳ row cụ thể nào — ví dụ “chỉ một instance của batch job này được chạy tại một thời điểm” trên một cụm worker. Advisory lock của PostgreSQL là lock được định danh bởi một số nguyên tuỳ ý (hoặc một cặp số nguyên) do ứng dụng bạn chọn, hoàn toàn tách biệt khỏi bất kỳ table hay row nào.

-- Cố lấy lock mà không chờ; trả về true/false ngay lập tức
SELECT pg_try_advisory_lock(123456);

-- ... nếu true, chạy logic job độc quyền ...

-- Giải phóng khi xong
SELECT pg_advisory_unlock(123456);

hoặc, với một lock tự động giải phóng khi transaction kết thúc:

BEGIN;
SELECT pg_advisory_xact_lock(123456);
-- công việc độc quyền ở đây
COMMIT;  -- lock tự động được giải phóng

Đây là cách nhẹ nhàng, ít thủ tục để triển khai mutex và kiểu phối hợp “leader election” trực tiếp trong database, mà không phải tự dựng một table lock riêng.

Deadlock

Deadlock xảy ra khi transaction A giữ một lock mà transaction B đang chờ, trong khi transaction B lại giữ một lock mà transaction A đang chờ — không bên nào có thể tiến tiếp, và nếu không can thiệp, cả hai sẽ chờ mãi mãi.

Transaction A:                     Transaction B:
BEGIN;                             BEGIN;
UPDATE accounts SET ... WHERE id=1;  UPDATE accounts SET ... WHERE id=2;
-- A hiện đang giữ lock trên row 1     -- B hiện đang giữ lock trên row 2
UPDATE accounts SET ... WHERE id=2;  UPDATE accounts SET ... WHERE id=1;
-- A chờ lock của B trên row 2          -- B chờ lock của A trên row 1
--                    ** DEADLOCK **

PostgreSQL không để các transaction này chờ mãi mãi. Cơ chế phát hiện deadlock (deadlock detector) định kỳ kiểm tra đồ thị “ai đang chờ ai” để tìm chu trình (cycle) (được điều khiển bởi tham số deadlock_timeout, mặc định 1 giây — một process đang chờ chỉ bắt đầu kiểm tra deadlock sau khi đã chờ đủ khoảng thời gian đó, để giữ cho trường hợp chờ lock thông thường không tốn kém). Khi tìm thấy một chu trình, nó chọn một trong các transaction liên quan và huỷ nó với lỗi kiểu:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678.
         Process 5678 waits for ShareLock on transaction 1234; blocked by process 1234.

Transaction còn sống sót có thể tiếp tục và cuối cùng commit. Transaction bị huỷ phải được ứng dụng retry (lý tưởng nên có backoff, vì retry ngay lập tức vào cùng mẫu hình tranh chấp có thể lặp lại deadlock).

Lời khuyên thực tế để tránh deadlock ngay từ đầu: luôn lấy lock trên nhiều row/table theo một thứ tự nhất quán trên mọi code path trong ứng dụng. Ở ví dụ trên, nếu cả hai transaction đều update id=1 trước id=2 — dù đây là hai thao tác về mặt logic không liên quan — thì sẽ không có chu trình nào cả, vì transaction nào đến row 1 trước sẽ đơn giản khiến transaction kia chờ ở row 1, rồi tiến sang row 2 mà không bị cản trở sau khi commit. Sắp xếp thứ tự lock nhất quán (ví dụ luôn theo primary key tăng dần) là kỹ thuật phòng ngừa deadlock hiệu quả nhất, đáng tin cậy hơn nhiều so với việc cố rút ngắn thời gian transaction hay retry nhiều hơn.

Transaction chạy lâu và cái giá phải trả

Một transaction mở lâu — dù đang thực sự làm việc hay chỉ ngồi không (idle) sau statement cuối cùng (idle in transaction) — là một trong những nguồn gây đau đầu vận hành phổ biến nhất của PostgreSQL, vì hai lý do khác biệt:

1. VACUUM không thể dọn dẹp sau nó. VACUUM chỉ có thể xoá một dead tuple một khi không snapshot của transaction nào còn có thể cần đến nó. Nếu một transaction mở từ một giờ trước và vẫn đang mở, PostgreSQL phải giả định rằng nó có thể vẫn đọc dữ liệu theo snapshot cũ một giờ đó, nên mọi dead tuple được tạo ra kể từ lúc đó — trên toàn bộ database, không chỉ ở các table mà transaction đó đụng tới — đều được giữ lại, không thể thu hồi. Đây là một trong những nguyên nhân phổ biến nhất gây bloat table và index mất kiểm soát trong các hệ thống PostgreSQL production. Chủ đề này được khai thác sâu về mặt vận hành trong ./11-storage-internals-and-vacuum.md.
2. Nó có thể giữ lock chặn các thao tác khác. Nếu transaction đã lấy một row lock, một ACCESS EXCLUSIVE lock từ một thay đổi schema, hoặc chỉ đơn giản đang nằm trong một transaction đã đụng tới một table mà giờ đây một DROP/ALTER cần đến, mọi thao tác khác đang chờ lock đó sẽ xếp hàng phía sau nó — đôi khi kéo dài suốt thời gian transaction đó tình cờ vẫn còn mở, mà với một session idle bị ai đó quên đóng, có thể là hàng giờ đồng hồ.

Bạn có thể tìm các session gây vấn đề bằng:

SELECT pid, usename, state, xact_start, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start IS NOT NULL
ORDER BY xact_start ASC;

Các session nằm ở trạng thái idle in transaction lâu đặc biệt đáng để truy tìm — chúng đại diện cho snapshot đang bị giữ và có thể cả lock đang bị giữ mà không có query nào đang chạy để giải thích cho việc đó. Biện pháp phòng ngừa thực tế mạnh nhất là thiết lập idle_in_transaction_session_timeout để PostgreSQL tự động kill các session nằm ở trạng thái đó quá một ngưỡng bạn chọn, thay vì chỉ trông cậy vào kỷ luật của ứng dụng.

Best Practices

• Giữ transaction ngắn. Chỉ thực hiện bên trong khối BEGIN/COMMIT những công việc thực sự cần tính atomic; đừng bọc các lời gọi bên ngoài chậm (HTTP request, gửi email, chờ input người dùng) bên trong một transaction đang mở.
• Không bao giờ để transaction ở trạng thái idle. Thiết lập idle_in_transaction_session_timeout ở mức database hoặc role như một lưới an toàn chống lại việc quên gọi COMMIT/ROLLBACK trong code ứng dụng hoặc trong các phiên psql thủ công.
• Chọn isolation level lỏng nhất mà vẫn đúng đắn, chứ không phải level nghiêm ngặt nhất “cho chắc”. Read Committed là lựa chọn đúng cho phần lớn statement; chỉ dùng đến Repeatable Read hoặc Serializable khi bạn đã xác định được một dị thường cụ thể (non-repeatable read, phantom read, write skew) thực sự có thể gây bug.
• Thiết kế để xử lý serialization failure khi dùng Serializable/Repeatable Read. Nếu dùng isolation level nghiêm ngặt hơn, ứng dụng của bạn phải sẵn sàng bắt lỗi serialization failure (SQLSTATE 40001) và retry transaction — đây là hành vi đúng, được kỳ vọng, không phải một lỗi cần loại bỏ.
• Dùng SELECT ... FOR UPDATE cho các bất biến kiểu đọc-rồi-ghi, thay vì tin rằng một lần đọc và write sau đó sẽ giữ nhất quán khi có concurrency.
• Lấy lock theo thứ tự nhất quán ở mọi nơi trong codebase (ví dụ luôn theo primary key tăng dần) để ngăn deadlock về mặt cấu trúc thay vì xử lý phản ứng sau khi nó xảy ra.
• Ưu tiên advisory lock thay vì tự dựng table “lock row” khi thứ cần phối hợp không thực sự là dữ liệu — ví dụ chạy job đơn instance, leader election.
• Theo dõi pg_stat_activity để tìm session chạy lâu và idle-in-transaction như một kiểm tra vận hành định kỳ, không chỉ khi có sự cố.
• Coi sức khoẻ VACUUM và transaction chạy lâu là hai vấn đề liên kết với nhau: bloat table tăng đột biến rất thường được giải thích bởi một transaction mở bị quên duy nhất, chứ không phải do autovacuum cấu hình sai. Kiểm tra các transaction mở cũ trước khi đi tune tham số autovacuum.

Tài liệu tham khảo

• PostgreSQL Docs — Concurrency Control (MVCC overview)
• PostgreSQL Docs — Transaction Isolation
• PostgreSQL Docs — Explicit Locking
• PostgreSQL Docs — Data Consistency Checks at the Application Level (Serializable / SSI)
• PostgreSQL Docs — Advisory Locks
• PostgreSQL Docs — Preventing and Handling Deadlocks
• PostgreSQL Docs — System Columns (xmin, xmax, ctid)
• PostgreSQL Docs — Routine Vacuuming

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something looks wrong here, bail out
ROLLBACK;

BEGIN;

INSERT INTO orders (id, customer_id, total) VALUES (501, 42, 199.99);

SAVEPOINT before_discount;

UPDATE orders SET total = total * 0.5 WHERE id = 501;  -- apply a discount
-- business rule check fails: discount not allowed for this customer tier
ROLLBACK TO SAVEPOINT before_discount;

-- the order still exists with its original total; we just undid the discount
UPDATE orders SET status = 'confirmed' WHERE id = 501;

COMMIT;

SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

BEGIN;

SELECT quantity FROM inventory WHERE product_id = 77 FOR UPDATE;
-- application checks: is quantity >= requested amount?

UPDATE inventory SET quantity = quantity - 3 WHERE product_id = 77;

COMMIT;

-- Try to acquire the lock without blocking; returns true/false immediately
SELECT pg_try_advisory_lock(123456);

-- ... if true, run the exclusive job logic ...

-- Release it when done
SELECT pg_advisory_unlock(123456);

BEGIN;
SELECT pg_advisory_xact_lock(123456);
-- exclusive work here
COMMIT;  -- lock released automatically

Transaction A:                     Transaction B:
BEGIN;                             BEGIN;
UPDATE accounts SET ... WHERE id=1;  UPDATE accounts SET ... WHERE id=2;
-- A now holds lock on row 1          -- B now holds lock on row 2
UPDATE accounts SET ... WHERE id=2;  UPDATE accounts SET ... WHERE id=1;
-- A waits for B's lock on row 2      -- B waits for A's lock on row 1
--                    ** DEADLOCK **

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678.
         Process 5678 waits for ShareLock on transaction 1234; blocked by process 1234.

SELECT pid, usename, state, xact_start, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start IS NOT NULL
ORDER BY xact_start ASC;