PG中文社区 / 使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率 __ PostgreSQL 使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率 作者 digoal
日期 2016-10-18
标签 PostgreSQL , advisory lock , 高并发更新
背景<
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率

原作者:digoal / 德哥  创作时间:2016-10-18 11:56:50+08  
doudou586 发布于2016-10-23 11:56:50           评论: 3   浏览: 6508   顶: 784  踩: 797 

使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率

2016 Postgres大象会官方报名通道: 点此报名



作者: digoal

日期: 2016-10-18

标签: PostgreSQL , advisory lock , 高并发更新

背景

通常在数据库中最小粒度的锁是行锁,当一个事务正在更新某条记录时,另一个事务如果要更新同一条记录(或者申请这一条记录的锁),则必须等待锁释放。

通常持锁的时间需要保持到事务结束,也就是说,如果一个长事务持有了某条记录的锁,其他会话要持有这条记录的锁,可能要等很久。

如果某张表的全表或者大部分记录要被更新的话,有几种做法。

1. 在一个事务中更新需要更新的记录,很显然时间可能很长,因为没有了并发。

2. 在多个事务中更新不同的记录,使用高并发来缩短更新的时间,但是就需要解决并发更新时存在的行锁冲突的问题。

本文将要给大家介绍两种解决并发更新行锁冲突问题的方法。

场景描述

测试表,单条记录越大,更新单条记录的时间越久(例如更新亿级别的超长BIT类型)。

每个人群都有一个唯一的ID,即parallel_update_test.id。

create unlogged table parallel_update_test(id int primary key, info int[]);

测试数据

insert into parallel_update_test select generate_series(1,10000), (select array_agg(id) 
                                            from generate_series(1,100000) t(id));

postgres=# \dt+ parallel_update_test 
                            List of relations
 Schema |         Name         | Type  |  Owner   |  Size   | Description 
--------+----------------------+-------+----------+---------+-------------
 public | parallel_update_test | table | postgres | 3961 MB | 
(1 row)

更新需求,每条记录都有更新

例如我存储的数组是USERID,每条记录代表某个属性的人群数据,这个属性的人群数据不断的在变化,因此会不断的需要更新。

update parallel_update_test set info=array_append(info,1);

单个事务更新耗时80秒

postgres=# begin;
postgres=# update parallel_update_test set info=array_append(info,1);
UPDATE 10000
Time: 80212.641 ms
postgres=# rollback;
ROLLBACK
Time: 0.131 ms
postgres=# vacuum parallel_update_test ;

使用并发的手段提高更新效率。

方法1 advisory lock

每个人群都有一个唯一的ID,即parallel_update_test.id。

所以只要保证并行的会话更新的是不同的ID对应的数据即可,同时需要避免单次重复更新。

如何避免更新同一个ID?

使用advisory lock可以避免并发更新同一条记录。

如何避免重复更新同一条记录。

使用扫描式的获取advisory lock,保证不会重复获取即可。

代码如下:

create or replace function update() returns void as $$
declare
  v_id int;
begin
  for v_id in select id from parallel_update_test  -- 扫描式
  loop  
    if pg_try_advisory_xact_lock(v_id) then -- 获取到ID的LOCK才会实施更新,否则继续扫描
      update parallel_update_test set info=array_append(info,1) where id=v_id;
    end if;
  end loop;
end;
$$ language plpgsql strict;

设计上尽量保证ID全局唯一,否则获取advisory lock的冲突可能性会增多。

测试,使用100个并行度

vi test.sql
select update();

pgbench -M prepared -n -r -f ./test.sql -c 100 -j 100 -t 1

并行更新耗时4秒

pgbench -M prepared -n -r -f ./test.sql -c 100 -j 100 -t 1
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 100
number of threads: 100
number of transactions per client: 1
number of transactions actually processed: 100/100
latency average = 4407.490 ms
tps = 22.688650 (including connections establishing)
tps = 22.708546 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
      3078.170  select update();

方法2 skip locked

这个方法需要9.5以及以上版本支持

代码如下:

create or replace function update() returns void as $$
declare
  v_id int;
begin
  select id into v_id from parallel_update_test order by id limit 1 for update skip locked;
  update parallel_update_test set info=array_append(info,1) where id=v_id;
  loop
    select id into v_id from parallel_update_test where id>v_id order by id limit 1 for update skip locked;
    if found then
      update parallel_update_test set info=array_append(info,1) where id=v_id;
    else
      return;
    end if;
  end loop;
end;
$$ language plpgsql strict;

使用100个并行度

并行更新耗时4秒

pgbench -M prepared -n -r -f ./test.sql -c 100 -j 100 -t 1
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 100
number of threads: 100
number of transactions per client: 1
number of transactions actually processed: 100/100
latency average = 4204.439 ms
tps = 23.784386 (including connections establishing)
tps = 23.813193 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
      3074.488  select update();

小结

在实时推荐系统中,通常可以使用数组或者比特位来标记人群,而每个人群都在不断的发生变化,也就是说,整张表都是热表。

为了提高更新的效率,本文给大家提供了两种并行消除行锁冲突更新的方法。

使用PostgreSQL提供的skip locked 或者advisory lock特性,消除行锁冲突,提高并行度,从而提高更新效率,发挥机器的最大能力。


2016 Postgres大象会官方报名通道:http://www.huodongxing.com/event/8352217821400

扫描报名


/images/news/2016/pgconf2016_qrcode.jpg


/images/news/2016/pgconf2016_plus_logo_cn.png


评论:3   浏览: 6508                   顶: 784  踩: 797 

请在登录后发表评论,否则无法保存。

1# __ xcvxcvsdf 回答于 2024-11-14 02:24:15+08
http://taiying.njtcbmw.cn/cqhc/ http://js.sytcxxw.cn/clgz/ http://yz.cqtcxxw.cn/xiangqin/ https://yichunzhoubian.tiancebbs.cn/ http://ouyu.hftcbmw.cn/lishui/ http://jinqiang.ahtcbmw.cn/huangshi/ http://gx.lztcxxw.cn/gzbjc/ http://yz.cqtcxxw.cn/jxgz/ http://cf.lstcxxw.cn/dingan/ http://tuiguang.hntcxxw.cn/hcq/ http://huaguang.jxtcbmw.cn/yzq/ https://fenlei.tiancebbs.cn/scnj/ https://fenlei.tiancebbs.cn/jsqczr/ http://km.lstcxxw.cn/dongguan/ http://ouyu.hftcbmw.cn/lldd/ https://fenlei.tiancebbs.cn/shhuangpu/ https://ypqgaoxin.tiancebbs.cn/

2# __ xcvxcvsdf 回答于 2024-10-21 14:58:28+08
https://zulin.tiancebbs.cn/sh/2528.html https://baishan.tiancebbs.cn/qths/465361.html https://zaozhuang.tiancebbs.cn/qths/455619.html https://su.tiancebbs.cn/hjzl/470187.html https://aihuishou.tiancebbs.cn/sh/2312.html https://taicang.tiancebbs.cn/hjzl/459785.html https://su.tiancebbs.cn/hjzl/466098.html https://zulin.tiancebbs.cn/sh/4077.html https://www.tiancebbs.cn/ershoufang/473001.html https://zulin.tiancebbs.cn/sh/3281.html https://aihuishou.tiancebbs.cn/sh/139.html https://aihuishou.tiancebbs.cn/sh/780.html https://zulin.tiancebbs.cn/sh/865.html https://zulin.tiancebbs.cn/sh/1868.html https://sh.tiancebbs.cn/hjzl/466438.html https://www.tiancebbs.cn/ershouwang/473491.html https://sys.tiancebbs.cn/qths/474047.html

3# __ xiaowu 回答于 2024-04-22 09:31:01+08
重新开始的网名:https://www.nanss.com/mingcheng/1553.html 装饰画的朋友圈广告词:https://www.nanss.com/wenan/2394.html 抖音文案短句干净治愈:https://www.nanss.com/wenan/1951.html 关于跑步的文案:https://www.nanss.com/wenan/2387.html 消防安全警示语:https://www.nanss.com/shenghuo/2440.html 对你绝情的人的说说:https://www.nanss.com/wenan/1538.html 女人把家收拾的干净心情说说:https://www.nanss.com/wenan/1626.html 我的生日:https://www.nanss.com/xuexi/2074.html 读后感:https://www.nanss.com/xuexi/2474.html 寓意重新开始的名字:https://www.nanss.com/mingcheng/2223.html 工作述职报告:https://www.nanss.com/gongzuo/2371.html 善意的谎言作文:https://www.nanss.com/xuexi/2171.html 读书的作文:https://www.nanss.com/xuexi/2225.html 研讨会主持词:https://www.nanss.com/gongzuo/2363.html 关于长城的作文:https://www.nanss.com/xuexi/2354.html 村干部工作总结:https://www.nanss.com/gongzuo/2087.html 传奇好看名字:https://www.nanss.com/mingcheng/1812.html 朝花夕拾读后感:https://www.nanss.com/xuexi/2120.html 爱错人的句子说说心情:https://www.nanss.com/wenan/2033.html 对老公心寒绝望一段话:https://www.nanss.com/yulu/1855.html 研究生入党申请书:https://www.nanss.com/xuexi/2305.html 表达亲人忌日思念心情的句子:https://www.nanss.com/yulu/2012.html 两个人的世界说说:https://www.nanss.com/wenan/2231.html 2022希望疫情结束的唯美句子:https://www.nanss.com/yulu/2280.html 美丽的秋天作文200字:https://www.nanss.com/xuexi/2071.html 激励上进正能量句子:https://www.nanss.com/wenan/2495.html 1000字读后感:https://www.nanss.com/xuexi/2128.html 可复制的漂亮特殊符号:https://www.nanss.com/mingcheng/2048.html 相思入骨的诗句:https://www.nanss.com/xuexi/2381.html 关于父亲的作文:https://www.nanss.com/xuexi/2484.html



发表评论:
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
© PostgreSQL中文社区 ... (自2010年起)