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

相关资源：敏捷开发V1.0.pptx