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;BEGINTime: 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                | 1591locktype           | relationrelation           | tblmode               | RowShareLockpage               | tuple              | virtualxid         | transactionid      | virtualtransaction | 3/2granted            | tfastpath           | tTime: 1.627 ms

假如Session 2也是希望从id < 100的记录中随机选择一行,但这时候会因为冲突而阻塞:

[local]:5432 pg12@testdb=# begin;BEGINTime: 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                | 1634locktype           | relationrelation           | tblmode               | RowShareLockpage               | tuple              | virtualxid         | transactionid      | virtualtransaction | 4/16granted            | tfastpath           | t-[ RECORD 2 ]------+--------------------pid                | 1591locktype           | relationrelation           | tblmode               | RowShareLockpage               | tuple              | virtualxid         | transactionid      | virtualtransaction | 3/4granted            | tfastpath           | t-[ RECORD 3 ]------+--------------------pid                | 1634locktype           | tuplerelation           | tblmode               | AccessExclusiveLockpage               | 0tuple              | 1virtualxid         | transactionid      | virtualtransaction | 4/16granted            | tfastpath           | fTime: 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                | 1634locktype           | relationrelation           | tblmode               | RowShareLockpage               | tuple              | virtualxid         | transactionid      | virtualtransaction | 4/17granted            | tfastpath           | t-[ RECORD 2 ]------+-------------pid                | 1591locktype           | relationrelation           | tblmode               | RowShareLockpage               | tuple              | virtualxid         | transactionid      | virtualtransaction | 3/4granted            | tfastpath           | tTime: 0.978 ms

参考资料
More concurrency: Improved locking in PostgreSQL