PostgreSQL读已提交隔离级别下插入操作的锁机制疑问

Great questions—these cut straight to how PostgreSQL manages concurrency and enforces data integrity. Let’s break down each scenario clearly:

Question 1: Why does a duplicate insert block immediately (instead of failing only on commit) in Read Committed isolation? #

Here’s the core of it: PostgreSQL enforces unique constraints (including primary keys) using unique indexes, and these indexes rely on locking to prevent concurrent conflicts.

When your first transaction runs insert into test(a) values(1), it doesn’t just add the row to the table—it also acquires an exclusive lock on the 1 key in the unique index. This lock prevents other transactions from modifying or even checking that key until the lock is released.

Your second transaction tries to insert the same 1 value. To execute this insert, it first needs to check the unique index for conflicts. But to do that check safely (and ensure no other transaction is modifying the same key), it needs to acquire the same exclusive lock on the 1 key. Since the first transaction already holds that lock, the second transaction has to wait (block) until the first transaction commits or rolls back (which releases the lock).

Why not just let the second transaction run and fail on commit? Two big reasons:

• Resource efficiency: If the second transaction ran through all its operations only to fail at commit, that’s wasted CPU, memory, and disk resources. Blocking early avoids this.
• Strict consistency: Unique constraints require global, immediate consistency—not just consistency at commit time. Allowing concurrent writes to the same unique key would risk race conditions that even MVCC (Multi-Version Concurrency Control) can’t resolve cleanly. Blocking ensures only one transaction can modify a unique key at a time.

Question 2: Why does the second transaction still block if the first transaction inserts then deletes the row? #

This boils down to when PostgreSQL releases locks: locks are held for the entire duration of the transaction, not just while the row exists.

When your first transaction runs insert into test(a) values(1), it grabs that exclusive lock on the 1 index key. Even when you run delete from test where a = 1 right after, the lock doesn’t get released. The delete removes the row from the table, but the transaction still owns the lock on the index key until it commits or rolls back.

So when the second transaction tries to insert 1, it still needs that exclusive lock on the index key—and since the first transaction is still holding it, the second transaction blocks. The fact that the row no longer exists doesn’t matter here; the lock is tied to the transaction, not the physical row.

内容的提问来源于stack exchange，提问作者TheTransactionQuests