Double Eleven Technology Series: Flash Sales Optimization on PostgreSQL

Flash Sales Typical Scenario

update tbl set xxx=xxx,upd_cnt=upd_cnt+1 where id=pk and upd_cnt+1<=5;   -- Assume that five iPhones are for flash sales.

Commonly Used Methods to Coping with Flash Sales

begin;
select 1 from tbl where id=pk for update nowait; -- If the customer does not obtain the lock, an error is returned, and the transaction is rolled back.
update tbl set xxx=xxx,upd_cnt=upd_cnt+1 where id=pk and upd_cnt+1<=5;
end;

What Is Ad Lock?

Purpose of Ad Lock

Performance of Ad Lock

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: 1
query mode: prepared
number of clients: 96
number of threads: 96
duration: 100 s
number of transactions actually processed: 131516823
latency 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);

Example of Using Ad Lock in Flash Sales

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: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 100 s
number of transactions actually processed: 39104368
latency 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);
top - 13:12:43 up 51 days, 18:41,  2 users,  load average: 1.12, 0.97, 0.78
Tasks: 1463 total, 28 running, 1435 sleeping, 0 stopped, 0 zombie
Cpu(s): 24.5%us, 9.3%sy, 0.0%ni, 66.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 529321832k total, 235226420k used, 294095412k free, 903076k buffers
Swap: 0k total, 0k used, 0k free, 62067636k cached

Comparison with Traditional Method

begin;
select 1 from tbl where id=pk for update nowait; -- If the customer does not obtain the lock, an error is returned, and the transaction is rolled back.
update tbl set xxx=xxx,upd_cnt=upd_cnt+1 where id=pk and upd_cnt+1<=5;
end;
vi test.sql
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; $$;pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100transaction 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; $$;
top - 13:13:48 up 51 days, 18:42,  2 users,  load average: 8.14, 2.69, 1.37
Tasks: 1464 total, 21 running, 1442 sleeping, 0 stopped, 1 zombie
Cpu(s): 41.7%us, 3.8%sy, 0.0%ni, 54.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 529321832k total, 235256052k used, 294065780k free, 903176k buffers
Swap: 0k total, 0k used, 0k free, 62068308k cached

Real Deduction Throughput of a Single Product

postgres=# create table upd(id int primary key, cnt int8);
postgres=# insert into upd values(1,0);
vi t0.sql
update upd set cnt=cnt-1 where id=1 and pg_try_advisory_xact_lock(1);
\sleep 10 us
....
vi t7.sql
update upd set cnt=cnt-1 where id=1 and pg_try_advisory_xact_lock(1);
\sleep 80 us
pgbench -M prepared -n -r -P 1 -f t0.sql -f t1.sql -f t2.sql -f t3.sql -f t4.sql -f t5.sql -f t6.sql -f t7.sql -c 64 -j 64 -T 100
postgres=# select * from upd;
id | cnt
----+---------
1 | -611249
(1 row)

Real Deduction Throughput of All Products on the Platform

postgres=# create table upd(id int primary key, cnt int8);
postgres=# insert into upd select generate_series(1,10000000), 0;
vi test.sql
\set id random(1,10000000)
update upd set cnt=cnt-1 where id=:id and pg_try_advisory_xact_lock(:id);
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100postgres=# select sum(cnt) from upd;
sum
-----------
-27233112
(1 row)

Advantages of Ad Lock Compared with Other Flash Sales Optimization Methods

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store