PostgreSQL秒殺4種方法-增加批量流式加減庫存方法

德哥發表於2018-01-27

SQL

背景

秒殺，即對同一個商品，消減庫存。

帶來的資料庫問題是熱點行，由於資料庫最細粒度的鎖通常是行鎖，同一行同一時刻只能被一個事務更新，其他事務如果要更新同一行，會等待行級排它鎖。

PostgreSQL中有幾種方法來處理秒殺：

1、暴力，直接更新

2、skip locked，跳過被鎖的行，直接返回，防止會話長時間等待。可以發起重試。

3、advisory lock，跳過被鎖的行，直接返回，防止會話長時間等待。可以發起重試。

4、把更新轉成寫入，批量消費，可以在核心層面實現（批量消耗），也可以在業務層面實現。

看一下幾種方法的效能。

create table t(    
  id int primary key,  -- 商品ID    
  cnt int              -- 庫存    
);    
    
insert into t values (1,2000000000);

都使用100個併發連線。

1、暴力更新

100併發

transaction type: ./test.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 100    
number of threads: 100    
duration: 120 s    
number of transactions actually processed: 342042    
latency average = 35.083 ms    
latency stddev = 36.273 ms    
tps = 2849.507392 (including connections establishing)    
tps = 2849.837580 (excluding connections establishing)    
script statistics:    
 - statement latencies in milliseconds:    
        35.083  update t set cnt=cnt-1 where id=1;

2併發

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 2  
number of threads: 2  
duration: 120 s  
number of transactions actually processed: 2819491  
latency average = 0.085 ms  
latency stddev = 0.009 ms  
tps = 23495.740654 (including connections establishing)  
tps = 23496.241610 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.085  update t set cnt=cnt-1 where id=1;

2、skip locked row

skip locked是PG提供的一種語法，可以跳過被鎖的行。

update t set cnt=cnt-1 where ctid = any (array(select ctid from t where id=1 for update skip locked)) returning *;    
    
    
transaction type: ./test.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 100    
number of threads: 100    
duration: 120 s    
number of transactions actually processed: 6508322    
latency average = 1.844 ms    
latency stddev = 2.390 ms    
tps = 54226.911876 (including connections establishing)    
tps = 54233.143956 (excluding connections establishing)    
script statistics:    
 - statement latencies in milliseconds:    
         1.843  update t set cnt=cnt-1 where ctid = any (array(select ctid from t where id=1 for update skip locked)) returning *;

3、advisory lock

advisory lock，更新時，鎖住PK，而不是ROW本身，如果未獲得鎖，直接返回。與skip locked類似，但是更加高效，因為不需要SEARCH ROW。

transaction type: ./test.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 100    
number of threads: 100    
duration: 120 s    
number of transactions actually processed: 31690080    
latency average = 0.379 ms    
latency stddev = 0.671 ms    
tps = 264047.289635 (including connections establishing)    
tps = 264083.172081 (excluding connections establishing)    
script statistics:    
 - statement latencies in milliseconds:    
         0.379  update t set cnt=cnt-1 where id=1 and pg_try_advisory_xact_lock(1);

4、流式批量更新

流式批量處理，將更新轉換為寫入，避免熱點行鎖，然後批量合併到庫存表。

但是需要注意，這個屬於非同步的方法，也就是說，可能導致庫存負數。不過消費足夠快的話，不會有太大問題。

1、建立FEED表，儲存使用者扣減庫存的記錄。

create table stat(    
  uid int,   -- 使用者ID    
  id int,    -- 商品ID    
  cnt int,   -- 購買數量    
  crt_time timestamp default now()  -- 寫入時間    
);

2、建立使用者扣減庫存的函式，這裡面使用一個判斷，當庫存（也就是說，預設不關心還沒有合併到最終結果的那些消費記錄。）

create or replace function consume(int, int, int) returns int as $$    
  insert into stat (uid, id, cnt) select $1 as uid, $2 as id, $3 as cnt from t where id=$2 and cnt+$3>=0 returning cnt;    
$$ language sql strict;

3、排程，比如每100毫秒排程一次，非同步合併

with tmp as (    
delete from stat where ctid = any ( array (    
  select ctid from stat limit 1000000    
)) returning *    
),    
t1 as (select id, sum(cnt) as cnt from tmp group by id)    
update t set cnt=t.cnt+t1.cnt from t1 where t.id=t1.id;

4、排程（可以使用autovacuum自動排程），垃圾回收。

vacuum stat;

5、壓測

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 100 -j 100 -T 120    
    
transaction type: ./test.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 100    
number of threads: 100    
duration: 120 s    
number of transactions actually processed: 17155235    
latency average = 0.699 ms    
latency stddev = 0.546 ms    
tps = 142929.999871 (including connections establishing)    
tps = 142949.652076 (excluding connections establishing)    
script statistics:    
 - statement latencies in milliseconds:    
         0.702  select consume(1,1,1);

如果我們需要按先後順序合併，可以加個索引

create index idx_stat_2 on stat(crt_time);

合併SQL如下：

with tmp as (    
delete from stat where ctid = any ( array (    
  select ctid from stat order by crt_time limit 1000000    
)) returning *    
),    
t1 as (select id, sum(cnt) as cnt from tmp group by id)    
update t set cnt=t.cnt+t1.cnt from t1 where t.id=t1.id;

效能如下:

transaction type: ./test.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 100    
number of threads: 100    
duration: 120 s    
number of transactions actually processed: 10394002    
latency average = 1.154 ms    
latency stddev = 0.951 ms    
tps = 86585.839187 (including connections establishing)    
tps = 86597.281593 (excluding connections establishing)    
script statistics:    
 - statement latencies in milliseconds:    
         1.155  select consume(1,1);

消費速度與寫入速度幾乎一致。只有排程延遲。

如果通過核心層面來實現的話，可以避免庫存負數這個問題，提高一定的效能，但是：為了不破壞原有的一致性和可靠性，同樣不能避免批量提交前，會話佔用資料庫連線的問題。

所以是有利有弊的。

另一方面，如果我們在內部實現同一個ID最多分配給兩個SERVER PROCESS執行，也能很好的解決這個問題。類似oracle的shared server mode，同時對id進行路由分配，至多給兩個SHARED PROCESS，從而每個ID保證2萬多的TPS。