本节介绍了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](http://highscalability.com/blog/2015/10/13/more-concurrency-improved-locking-in-postgresql.html)