本文简单介绍了PostgreSQL Locks(Row-Level)的基础知识,原文详见 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

PostgreSQL DBA(74) - Locks(Row-Level)：What You Should Know