本文简单介绍了PostgreSQL Locks(Row-Level)的基础知识,原文详见[Postgres Locking Revealed](https://engineering.nordeus.com/postgres-locking-revealed/),有所删减和调整. PostgreSQL把锁分为三类,table-level, row-level and advisory locks.Table和Row级锁可以是显式或隐式锁,advisory locks通常是显式锁.显式锁在显式用户请求时获得,而隐式锁则通过标准SQL命令获得. **Row-level Locks** PG 9.4,存在4种类型的Row-level locks,分别是: *1.FOR UPDATE* 该模式对SELECT返回的结果集上锁,防止这些行被其他事务locked, modified or deleted.也就是说,其他事务如果希望UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE 这些行,那么会被阻塞. 该锁在DELETE/UPDATE某些列(作为外键的unique index列)时也会请求. session 1 ``` [local]:5432 pg12@testdb=# ---- session 1 [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.861 ms [local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE; [local]:5432 pg12@testdb=#* delete from t_lock where id = 1; DELETE 0 Time: 2.340 ms [local]:5432 pg12@testdb=#* end; COMMIT Time: 0.436 ms [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.617 ms [local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE; [local]:5432 pg12@testdb=#* delete from t_lock where id = 2; DELETE 4 Time: 2.521 ms [local]:5432 pg12@testdb=#* select pid,locktype,relation::regclass,mode,page,tuple from pg_locks where relation=295053; pid | locktype | relation | mode | page | tuple ------+----------+----------+------------------+------+------- 2097 | relation | t_lock | RowExclusiveLock | | (1 row) Time: 3.210 ms [local]:5432 pg12@testdb=# select pg_backend_pid(); pg_backend_pid ---------------- 2097 (1 row) ``` session 2 ``` [local]:5432 pg12@testdb=# ---- session 2 [local]:5432 pg12@testdb=#* select pg_backend_pid(); pg_backend_pid ---------------- 2515 (1 row) Time: 0.457 ms [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.875 ms [local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE; [local]:5432 pg12@testdb=#* delete from t_lock where id = 2; ##被阻塞 ``` session 3 查询locks ``` [local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple from pg_locks where relation=295053; pid | locktype | relation | mode | page | tuple ------+----------+----------+---------------------+------+------- 2515 | relation | t_lock | RowExclusiveLock | | 2097 | relation | t_lock | RowExclusiveLock | | 2515 | tuple | t_lock | AccessExclusiveLock | 0 | 2 (3 rows) Time: 3.098 ms ``` *2.FOR NO KEY UPDATE* 这种模式与FOR UPDATE类似,但强度要弱一些,该锁不会阻塞SELECT FOR KEY SHARE锁模式.在不需要请求FOR UPDATE锁执行UPDATE时获取该锁. session 1 ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 1.392 ms [local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE; [local]:5432 pg12@testdb=#* update t_lock set id = 3000 where id = 3; UPDATE 4 Time: 3.190 ms [local]:5432 pg12@testdb=#* select pid,locktype,relation::regclass,mode,page,tuple from pg_locks where relation=295053; pid | locktype | relation | mode | page | tuple ------+----------+----------+------------------+------+------- 2097 | relation | t_lock | RowExclusiveLock | | (1 row) Time: 3.007 ms ``` session 2 ``` [local]:5432 pg12@testdb=# ---- session 2 [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.885 ms [local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE; [local]:5432 pg12@testdb=#* update t_lock set id = 3000 where id = 3; -- 阻塞 ``` session 3 ``` [local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple from pg_locks where relation=295053; pid | locktype | relation | mode | page | tuple ------+----------+----------+------------------+------+------- 2515 | relation | t_lock | RowExclusiveLock | | 2097 | relation | t_lock | RowExclusiveLock | | 2515 | tuple | t_lock | ExclusiveLock | 0 | 3 (3 rows) Time: 3.104 ms ``` *3.FOR SHARE* 该模式与FOR NO KEY UPDATE类似,但该模式获取的是ShareLock而不是ExclusiveLock,这会阻塞其他事务在锁定的行上执行UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE,但不阻塞SELECT FOR SHARE or SELECT FOR KEY SHARE. *4.FOR KEY SHARE* 该模式行为与FOR SHARE类似,但该锁更弱一些,阻塞 SELECT FOR UPDATE但不会阻塞SELECT FOR NO KEY UPDATE.该锁会阻塞其他事务执行DELETE和其他UPDATE更新key values的操作,但不会阻塞另外的UPDATE, nor SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE. 下面是row-level lock的冲突表:  **参考资料** [Postgres Locking Revealed](https://engineering.nordeus.com/postgres-locking-revealed/)