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

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