本文简单介绍了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: 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.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的冲突表:

![](http://img.blog.itpub.net/blog/2019/08/12/8b27ff169c7bb32d.png?x-oss-process=style/bb)

**参考资料**
[Postgres Locking Revealed](https://engineering.nordeus.com/postgres-locking-revealed/)

ITPub博客

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

最新文章