PostgreSQLDBA(81)-Locks(FORUPDATESKIPLOCKED)

本节介绍了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