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

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