Double Eleven Technology Series: Flash Sales Optimization on PostgreSQL

Flash Sales Typical Scenario

Although you may already be very familiar with flash sales, I still want to introduce some relevant ideas to make this article complete.

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

Generally, for update, nowait can be used to avoid waiting. That is, if a customer does not obtain the lock, the customer does not need to wait.

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?

Ad lock is a user-oriented lightweight lock. Its object is an integer. The lock types include transaction, session, shared, and exclusive.

Purpose of Ad Lock

In addition to supporting flash sales, ad lock has the following functions:

  1. Concurrent security check
  2. UPSERT in recursive call
  3. Ensuring atomic operation in service logic design

Performance of Ad Lock

Ad lock is lightweight. It does not need to access data or execute much code, so it has a high efficiency.

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

A product has a unique ID in the database. We can lock the ID (if the ID is repeated in different tables, add offset or use other measures to avoid conflict).

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

Typically, select for update nowait is used to avoid waiting.

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

When testing the deduction throughput of a product, you can find that the QPS is high. However, the actual deduction throughput is lower because an error is returned if no lock is obtained.

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

Assume 10 million products are available on the platform. Test the total deduction throughput using this method.

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
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com