双十一怎么才能爆单(聊一聊双十一背后的技术)

作者

digoal

日期

2016-11-17

标签

PostgreSQL , 秒杀 , 裸秒 , ad lock

双十一背后的技术系列文章

《聊一聊双十一背后的技术 - 物流, 动态路径规划》

《聊一聊双十一背后的技术 - 分词和搜索》

《聊一聊双十一背后的技术 - 不一样的秒杀技术, 裸秒》

背景

秒杀在商品交易中是一个永恒的话题,从双十一,到一票难求,比的仅仅是手快吗?

其实对于交易平台来说,面对的不仅仅是人肉,还有很多脚本,外挂自动化的抢购系统,压力可想而知。

秒杀的优化手段很多,就拿数据库来说,有用排队机制的,有用异步消息的,有用交易合并的。

今天,我要给大家介绍一种更极端的秒杀应对方法,裸秒。

NameReturn TypeDescriptionpg_advisory_lock(key bigint)voidObtain exclusive session level advisory lockpg_advisory_lock(key1 int, key2 int)voidObtain exclusive session level advisory lockpg_advisory_lock_shared(key bigint)voidObtain shared session level advisory lockpg_advisory_lock_shared(key1 int, key2 int)voidObtain shared session level advisory lockpg_advisory_unlock(key bigint)booleanRelease an exclusive session level advisory lockpg_advisory_unlock(key1 int, key2 int)booleanRelease an exclusive session level advisory lockpg_advisory_unlock_all()void Releaseall session level advisory locks held by the current sessionpg_advisory_unlock_shared(key bigint)booleanRelease a shared session level advisory lockpg_advisory_unlock_shared(key1 int, key2 int)booleanRelease a shared session level advisory lockpg_advisory_xact_lock(key bigint)voidObtain exclusive transaction level advisory lockpg_advisory_xact_lock(key1 int, key2 int)voidObtain exclusive transaction level advisory lockpg_advisory_xact_lock_shared(key bigint)voidObtain shared transaction level advisory lockpg_advisory_xact_lock_shared(key1 int, key2 int)voidObtain shared transaction level advisory lockpg_try_advisory_lock(key bigint)booleanObtain exclusive session level advisory lock if availablepg_try_advisory_lock(key1 int, key2 int)booleanObtain exclusive session level advisory lock if availablepg_try_advisory_lock_shared(key bigint)booleanObtain shared session level advisory lock if availablepg_try_advisory_lock_shared(key1 int, key2 int)booleanObtain shared session level advisory lock if availablepg_try_advisory_xact_lock(key bigint)booleanObtain exclusive transaction level advisory lock if availablepg_try_advisory_xact_lock(key1 int, key2 int)booleanObtain exclusive transaction level advisory lock if availablepg_try_advisory_xact_lock_shared(key bigint)booleanObtain shared transaction level advisory lock if availablepg_try_advisory_xact_lock_shared(key1 int, key2 int)booleanObtain shared transaction level advisory lock if available

通常数据库支持的最小粒度的锁(指开放给用户的)是行锁,行锁相比LWLOCK,SPINLOCK等是非常重的,所以传统的行锁在秒杀中会成为非常大的瓶颈,包括锁的等待。

ad lock的用途

ad lock的用途,除了我接下来要说的秒杀,其实还有很多用途,例如

并发的安全性检查,

递归调用中用于UPSERT的场景,

业务逻辑设计中用来确保原子操作等。

ad lock的性能指标

因为AD LOCK很轻量化,不需要访问数据,不需要执行冗长的代码,所以很高效。

32核64线程机器测试可以达到131万次/s的锁请求。

vi test.sql

\set id random(1,100000000)

select pg_try_advisory_xact_lock(:id);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 96 -j 96 -T 100transaction type: ./test.sql

scaling factor: 1query mode: preparednumber of clients: 96number of threads: 96duration: 100 snumber of transactions actually processed: 131516823latency average = 0.072 ms

latency stddev = 0.070 ms

tps = 1314529.211060 (including connections establishing)

tps = 1315395.309707 (excluding connections establishing)script statistics:

- statement latencies in milliseconds: 0.001 \set id random(1,100000000) 0.074 select pg_try_advisory_xact_lock(:id);

ad lock用于秒杀的例子

在数据库中,商品通常有唯一ID,我们可以对这个ID加锁,(当然,如果对不同的表这个ID有重叠的可能,我们可以加偏移量或者其他的手段来达到无冲突)。

加锁成功才会去对行加锁,执行更新,这样就能规避掉无效的行锁等待,以及冗长的查询代码。

使用 AD LOCK 对单条记录的并发更新处理QPS可以达到39.1万/s,被秒杀的商品很快就会变成售罄状态,不会再浪费数据库的资源。

create table test(id int primary key, crt_time timestamp);insert into test values (1);vi test.sql

update test set crt_time=now() where id=1 and pg_try_advisory_xact_lock(1);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100transaction type: ./test.sql

scaling factor: 1query mode: preparednumber of clients: 64number of threads: 64duration: 100 snumber of transactions actually processed: 39104368latency average = 0.163 ms

latency stddev = 0.216 ms

tps = 391012.743072 (including connections establishing)

tps = 391175.983419 (excluding connections establishing)script statistics:

- statement latencies in milliseconds: 0.163 update test set crt_time=now() where id=1 and pg_try_advisory_xact_lock(1);

此时数据库主机还有66.2%的空闲CPU资源可用使用。

top - 13:12:43 up 51 days, 18:41, 2 users, load average: 1.12, 0.97, 0.78Tasks: 1463 total, 28 running, 1435 sleeping, 0 stopped, 0 zombieCpu(s): 24.5%us, 9.3%sy, 0.0%ni, 66.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stMem: 529321832k total, 235226420k used, 294095412k free, 903076k buffersSwap: 0k total, 0k used, 0k free, 62067636k cached

对比传统的例子

传统的消除等待的做法是这样的,通过select for update nowait。

begin;select 1 from tbl where id=pk for update nowait; -- 如果用户无法即刻获得锁,则返回错误。从而这个事务回滚。update tbl set xxx=xxx,upd_cnt=upd_cnt 1 where id=pk and upd_cnt 1<=5;end;

在PG中,可以使用do语句,把以上合成到一个块里面操作。

使用传统的方法,每秒可以处理8.6万。

vi test.sqldo language plpgsql $$ declare begin with t as (select * from test where id=1 for update nowait) update test set crt_time=now() from t where t.id=test.id; exception when others then return; end; $$;

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100

transaction type: ./test.sql

scaling factor: 1

query mode: prepared

number of clients: 64

number of threads: 64

duration: 100 s

number of transactions actually processed: 8591222

latency average = 0.744 ms

latency stddev = 0.713 ms

tps = 85888.823884 (including connections establishing)

tps = 85924.666940 (excluding connections establishing)

script statistics:

- statement latencies in milliseconds:

0.744 do language plpgsql $$ declare begin with t as (select * from test where id=1 for update nowait) update test set crt_time=now() from t where t.id=test.id; exception when others then return; end; $$;

CPU剩余54.5%

top - 13:13:48 up 51 days, 18:42, 2 users, load average: 8.14, 2.69, 1.37Tasks: 1464 total, 21 running, 1442 sleeping, 0 stopped, 1 zombieCpu(s): 41.7%us, 3.8%sy, 0.0%ni, 54.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stMem: 529321832k total, 235256052k used, 294065780k free, 903176k buffersSwap: 0k total, 0k used, 0k free, 62068308k cached

ad lock相比其他秒杀优化的优势

双十一怎么才能爆单(聊一聊双十一背后的技术)(1)

使用AD LOCK可以使得CPU开销最小化,等待最小化,从本文的测试CASE来看,单条记录的更新可以达到39.1万/s。

传统的手段只能达到8.6万/s。

使用AD LOCK不破坏ACID,单个请求单个事务,不影响其他的事务。

合并优化,本质上是破坏了ACID的,如果合并失败,会导致所有相关的请求失败。

如果你对PG感兴趣,可以再了解一下

沟通和互动以及更多干货,欢迎关注新浪微博:@阿里云云栖社区

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页