开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

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

作者

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特性,消除行锁冲突,提高并行度,从而提高更新效率,发挥机器的最大能力。

Count

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
RDS数据库和ECS自建数据库主从复制,数据同步
设置阿里云RDS和ECS上自建的数据库达成主从分离。
17177 0
MSSQL-最佳实践-实例级别数据库上云RDS SQL Server
--- title: MSSQL-最佳实践-实例级别数据库上云RDS SQL Server author: 风移 --- # 摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种
1598 0
免费享受同城双可用区高可用容错能力!阿里云云数据库RDS新增可用区6月汇总(内含福利)
6月份,阿里云云数据库 MySQL 版,云数据库 PPAS 版,云数据库 SQL Server 版,云数据库 PostgreSQL 版均宣布新增可用区,用户在控制台上按需求创建实例,即可享受同城双可用区高可用容错能力。接下来小编将为大家详细列出新增可用区。
2729 0
MSSQL · 最佳实践 · 实例级别数据库上云RDS SQL Server
摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种解决方案以及上个月分享的RDS SDK实现数据库迁移上阿里云,本期我们分享如何将用户线下或者ECS上自建实例级别数据库一键迁移上阿里云RDS SQL Server。
1675 0
阿里云云数据库RDS秒级监控功能解锁,通宵加班找故障将成为过去式
每一个奋斗在前线的数据库管理员和运维人员似乎运气都不太好,这些人都绝对经历过的诡异事件就是:逢年过节必出故障,明明眼看着要休假了,又接到故障通知,只好通宵加班找问题。没问题的时候可能大家都不会想到你,一出问题就先拿运维试问,于是每逢佳节便出现拜数据库的戏谑图片。
2716 0
重磅干货免费下载!阿里云RDS团队论文被数据库顶会SIGMOD 2018收录
来自阿里云RDS团队的论文“**TcpRT: Instrument and Diagnostic Analysis System for Service Quality of Cloud Databases at Massive Scale in Real-time” (TcpRT:面向大规模海量云数据库的服务质量实时采集与诊断系统)**被数据库顶会SIGMOD 2018收录。
10381 0
参与 API 创新应用大赛,体验RDS CloudDBA数据库性能优化 API
阿里云的RDS数据库,有开发者所需要的一系列的功能,但很多功能很多开发者可能并没有使用过。这里,介绍一个RDS比较有用的功能:CloudDBA数据库性能优化 API。
12948 0
从运维的角度分析使用阿里云数据库RDS的必要性--你不应该在阿里云上使用自建的MySQL/SQL Server/Oracle/PostgreSQL数据库
开宗明义,你不应该在阿里云上使用自建的MySQL or SQL Server数据库,对了,还有Oracle or PostgreSQL数据库。 云数据库 RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务。
4454 0
【元气云妹】数据库RDS的连接方式
如何通过DMS和MySQL-Front客户端连接RDS实例
2164 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
认识PostgreSQL中与众不同的索引
立即下载
PostgreSQL复制原理及高可用集群
立即下载
国产化浪潮之上的PostgreSQL-删时间
立即下载