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

This section introduces FOR UPDATE SKIP LOCKED in PostgreSQL, through which concurrency performance can be improved in some scenarios.

Session 1 wants to randomly select a row from a record with id < 100 in tbl:

[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 limit 1 for update Id | C1 | c2 | c3 | c4 | c5-+

The following is the lock information for the 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

If Session 2 also wants to randomly select a row from a record with id < 100, but at this time it will be blocked by a conflict:

[local]: 5432 pg12@testdb=# begin;BEGINTime: 0.962 ms [local]: 5432 pg12@testdb=#* select * from tbl where id in limit 1 for update

Related lock information:

[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 provides FOR UPDATE SKIP LOCKED to improve concurrency performance by skipping locked rows when Session 2 fetches a row

[local]: 5432 pg12@testdb=#* select * from tbl where id in limit 1 for update SKIP LOCKED; id | C1 | c2 | c3 | c4 | c5

As you can see, using the SKIP LOCKED option, Session 2 is not blocked but gets tuple without locked.

The lock information at this time is as follows:

[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

references

More concurrency: Improved locking in PostgreSQL