PostgreSQLDBA(81)-Locks(FORUPDATESKIPLOCKED)

本節(jié)介紹了PostgreSQL中的FOR UPDATE SKIP LOCKED,通過該Option可以提高某些場景下的并發(fā)性能.

成都創(chuàng)新互聯(lián)十余年專注成都高端網(wǎng)站建設(shè)按需求定制設(shè)計服務(wù),為客戶提供專業(yè)的成都網(wǎng)站制作,成都網(wǎng)頁設(shè)計,成都網(wǎng)站設(shè)計服務(wù);成都創(chuàng)新互聯(lián)服務(wù)內(nèi)容包含成都網(wǎng)站建設(shè),成都微信小程序,軟件開發(fā),網(wǎng)絡(luò)營銷推廣,網(wǎng)絡(luò)運營服務(wù)及企業(yè)形象設(shè)計;成都創(chuàng)新互聯(lián)擁有眾多專業(yè)的高端網(wǎng)站制作開發(fā)團隊,資深的高端網(wǎng)頁設(shè)計團隊及經(jīng)驗豐富的架構(gòu)師高端網(wǎng)站策劃團隊;我們始終堅持從客戶的角度出發(fā),為客戶量身訂造網(wǎng)絡(luò)營銷方案，解決網(wǎng)絡(luò)營銷疑問。

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;

相關(guān)鎖信息:

[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的行,從而提高并發(fā)性能

[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并沒有被阻塞而是獲取了沒有l(wèi)ocked的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