PostgreSQL DBA(81) - Locks(FOR UPDATE SKIP LOCKED)

husthxd發表於2019-08-19

本節介紹了PostgreSQL中的FOR UPDATE SKIP LOCKED,透過該Option可以提高某些場景下的併發效能.

Session 1希望從tbl中id < 100的記錄中隨機選擇一行:
```
[local]:5432 pg12@testdb=# select pg_backend_pid();
 pg_backend_pid 
----------------
           1591
(1 row)

Time: 8.613 ms
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 4.527 ms
[local]:5432 pg12@testdb=#* select * from tbl where id in (1,2,3) limit 1 for update;
 id | c1  | c2  | c3  | c4 | c5 
----+-----+-----+-----+----+----
  1 | c11 | c21 | c31 |    | c3
(1 row)

Time: 1.450 ms
[local]:5432 pg12@testdb=#* 
```
下面是該SQL的鎖資訊
```
[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;
-[ RECORD 1 ]------+-------------
pid                | 1591
locktype           | relation
relation           | tbl
mode               | RowShareLock
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
virtualtransaction | 3/2
granted            | t
fastpath           | t

Time: 1.627 ms
```
假如Session 2也是希望從id < 100的記錄中隨機選擇一行,但這時候會因為衝突而阻塞:
```
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.962 ms
[local]:5432 pg12@testdb=#* select * from tbl where id in (1,2,3) limit 1 for update;

```
相關鎖資訊:
```
[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;
-[ RECORD 1 ]------+--------------------
pid                | 1634
locktype           | relation
relation           | tbl
mode               | RowShareLock
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
virtualtransaction | 4/16
granted            | t
fastpath           | t
-[ RECORD 2 ]------+--------------------
pid                | 1591
locktype           | relation
relation           | tbl
mode               | RowShareLock
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
virtualtransaction | 3/4
granted            | t
fastpath           | t
-[ RECORD 3 ]------+--------------------
pid                | 1634
locktype           | tuple
relation           | tbl
mode               | AccessExclusiveLock
page               | 0
tuple              | 1
virtualxid         | 
transactionid      | 
virtualtransaction | 4/16
granted            | t
fastpath           | f

Time: 1.276 ms
```
PostgreSQL提供FOR UPDATE SKIP LOCKED,在Session 2獲取一行時可跳過locked的行,從而提高併發效能
```
[local]:5432 pg12@testdb=#* select * from tbl where id in (1,2,3) limit 1 for update SKIP LOCKED;
 id | c1  | c2  | c3  | c4 | c5 
----+-----+-----+-----+----+----
  2 | c12 | c22 | c32 |    | c3
(1 row)

Time: 2.413 ms

```
可以看到,使用SKIP LOCKED選項,Session 2並沒有被阻塞而是獲取了沒有locked的tuple.

這時候的鎖資訊如下:
```
[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;
-[ RECORD 1 ]------+-------------
pid                | 1634
locktype           | relation
relation           | tbl
mode               | RowShareLock
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
virtualtransaction | 4/17
granted            | t
fastpath           | t
-[ RECORD 2 ]------+-------------
pid                | 1591
locktype           | relation
relation           | tbl
mode               | RowShareLock
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
virtualtransaction | 3/4
granted            | t
fastpath           | t

Time: 0.978 ms
```

**參考資料**
[More concurrency: Improved locking in PostgreSQL](http://highscalability.com/blog/2015/10/13/more-concurrency-improved-locking-in-postgresql.html)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2654113/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

SELECT ... FOR UPDATE SKIP LOCKED;
2014-10-24
PostgreSQL DBA(158) - Locks(tuple locks)
2020-01-14
SQL
PostgreSQL DBA(85) - Locks(lightweight locks)
2019-08-23
SQL
PostgreSQL DBA(77) - Locks(Lock Monitoring)
2019-08-14
SQL
1120 11g select for update skip locked
2017-11-20
PostgreSQL DBA(76) - Locks(Advisory Locks)：What You Should Know
2019-08-13
SQL
PostgreSQL DBA(78) - Locks(When Postgres blocks 7 tips for dealing with locks)
2019-08-14
SQLBloC
PostgreSQL DBA(79) - Locks(pg_blocking_pids)
2019-08-15
SQLBloC
PostgreSQL DBA(157) - pgAdmin（OOM & max_locks_per_transaction ）
2020-01-10
SQLOOM
PostgreSQL DBA(71) - Locks(Table-Level)：What You Should Know
2019-08-08
SQL
PostgreSQL DBA(74) - Locks(Row-Level)：What You Should Know
2019-08-12
SQL
SELECT FOR UPDATE SKIP LOCKED --- 一個未公開但十分有用的特性
2007-12-12
PostgreSQL DBA(75) - Locks(locktype：transactionid)：What You Should Know
2019-08-13
SQL
SELECT FOR UPDATE SKIP LOCKED --- 一個未公開但十分有用的特性 zt
2009-06-14
PostgreSQL 原始碼解讀（238）- Locks(OOM & max_locks_per_transaction)
2020-01-14
SQL原始碼OOM
PostgreSQL 原始碼解讀（239）- Locks(OOM & max_locks_per_transaction#2)
2020-01-15
SQL原始碼OOM
PG12中新增：VACUUM命令的SKIP_LOCKED選項
2021-07-02
PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQL
2019-06-27
SQLIndex
PostgreSQL 原始碼解讀（219）- Locks(Overview)
2019-08-28
SQL原始碼View
[20171123]Skip Locked and ITL slot 2.txt
2017-11-23
PostgreSQL DBA(35) - CTE
2019-06-03
SQL
PostgreSQL DBA(42) - locale
2019-06-25
SQL
PostgreSQL 原始碼解讀（221）- Locks(PROCLOCK Struct)
2019-09-03
SQL原始碼Struct
PostgreSQL DBA(133) - Extension(postgresql_anonymizer)
2019-11-19
SQL
PostgreSQL 原始碼解讀（220）- Locks(LOCK Struct)
2019-09-02
SQL原始碼Struct
PostgreSQL DBA(59) - Index(Bloom)
2019-07-18
SQLIndexOOM
PostgreSQL DBA(53) - Index(BRIN)
2019-07-11
SQLIndex
PostgreSQL DBA(58) - DBLink
2019-07-18
SQL
PostgreSQL DBA(48) - Index(GiST)
2019-07-04
SQLIndex
PostgreSQL DBA(51) - Index(GIN)
2019-07-09
SQLIndex
PostgreSQL DBA(52) - Index(RUM)
2019-07-10
SQLIndex
PostgreSQL DBA(47) - Index(Btree)
2019-07-02
SQLIndex
PostgreSQL DBA(43) - Index(Hash)
2019-06-26
SQLIndex
PostgreSQL DBA(191) - Collation
2022-04-30
SQL
PostgreSQL DBA(182) - HOT
2021-05-24
SQL
PostgreSQL DBA(185) - watch
2021-09-17
SQL
PostgreSQL DBA(181) - Using PostgreSQL as a Data Warehouse
2021-05-20
SQL
PostgreSQL 原始碼解讀（224）- Locks(The Deadlock Detection Algorithm)
2019-09-09
SQL原始碼Go