您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
本节介绍了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
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。