Lock Overview¶

Overview¶

PostgreSQL 基于 MVCC 机制实现了读写无阻塞，允许事务通过快照访问历史版本数据；然而针对写写冲突，系统仍需依赖锁机制进行协调，以确保同一行数据在并发修改时的原子性与一致性。

初始化数据

create table tb
insert into tb values

Txn 1

begin;
update tb set a = 1;

Txn 2

begin;
update tb set a = 2; -- blocked

Locks

#define AccessShareLock          1  /* SELECT */
#define RowShareLock             2  /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock         3  /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4  /* VACUUM (non-FULL), ANALYZE, CREATE INDEX CONCURRENTLY */
#define ShareLock                5  /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock    6  /* like EXCLUSIVE MODE, but allows ROW SHARE */
#define ExclusiveLock            7  /* blocks ROW SHARE/SELECT...FOR UPDATE */
#define AccessExclusiveLock      8  /* ALTER TABLE, DROP TABLE, VACUUM FULL, and unqualified LOCK TABLE */

Table-Level Locks

PostgreSQL Lock Conflicts

conflict matrix

	`ACCESS SHARE`	`ROW SHARE`	`ROW EXCL.`	`SHARE UPDATE EXCL.`	`SHARE`	`SHARE ROW EXCL.`	`EXCL.`	`ACCESS EXCL.`
`ACCESS SHARE`								X
`ROW SHARE`							X	X
`ROW EXCL.`					X	X	X	X
`SHARE UPDATE EXCL.`				X	X	X	X	X
`SHARE`			X	X		X	X	X
`SHARE ROW EXCL.`			X	X	X	X	X	X
`EXCL.`		X	X	X	X	X	X	X
`ACCESS EXCL.`	X	X	X	X	X	X	X	X

业务操作
│
├─ SELECT
│
├─ 写操作
│  INSERT / UPDATE / DELETE
│
├─ 维护操作
│  VACUUM / ANALYZE
│
├─ 弱DDL
│  CREATE INDEX
│  CREATE TRIGGER
│
└─ 强DDL
   ALTER TABLE
   DROP TABLE
   TRUNCATE