本文簡單介紹了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/)

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

相關文章