从PostgreSQL中获取唯一的并发序列号

Question

我们正在设计一个订单管理系统，使用Postgresql将订单id设计为bigint类型，位置结构实现如下:

以2015072201000010001为订单id为例，前8位视为日期，此处为20150722，后7位视为地区代码，此处为0100001，后4位为上述地区和日期下的序列号。

因此，每次创建一个新订单时，php逻辑应用层将使用下面的sql语句查询PostgreSQL:

select id from orders where id between 2015072201000010000 and 2015072201000019999 order by id desc limit 1 offset 0

然后增加新订单的id，然后将订单插入到PostgreSQL数据库中。

如果一次只有一个订单生成过程，这是可以的。但是，由于PostgreSQL的数据库读/写锁机制，在并发数百个订单生成请求的情况下，订单id很有可能发生冲突。

假设有两个订单请求A和B, A试图从数据库中读取最新的订单id，然后B也读取最新的订单id，然后A写入数据库，最后B写入数据库将失败，因为订单id主键冲突。

关于如何使这个订单生成操作并发可行，有什么想法吗?

Answer 1

在许多并发操作的情况下，您唯一的选择是使用序列。在这个场景中，您需要为每个日期和地区创建一个序列。听起来工作量很大，但大部分都可以自动化。

创建序列

您可以以日期和地区命名您的序列。所以像这样做:

CREATE SEQUENCE seq_201507220100001;

您应该为每一个日期和地区的组合创建一个序列。在函数中执行此操作以避免重复。每天运行此函数一次。您可以提前这样做，或者更好的是，在每天的计划工作中这样做，以创建明天的序列。假设您不需要将顺序回溯到前几天，您可以在同一个函数中删除昨天的序列。

CREATE FUNCTION make_and_drop_sequences() RETURNS void AS $$
DECLARE
  region    text;
  tomorrow  text;
  yesterday text;
BEGIN
  tomorrow  := to_char((CURRENT_DATE + 1)::date, 'YYYYMMDD');
  yesterday := to_char((CURRENT_DATE - 1)::date, 'YYYYMMDD');
  FOREACH region IN 
    SELECT DISTINCT region FROM table_with_regions
  LOOP
    EXECUTE format('CREATE SEQUENCE %I', 'seq_' || tomorrow || region);
    EXECUTE format('DROP SEQUENCE %I', 'seq_' || yesterday|| region);
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

使用序列

在PHP代码中，您显然知道需要输入新订单id的日期和地区。编写另一个函数，根据日期和地区的正确顺序生成新值:

CREATE FUNCTION new_date_region_id (region text) RETURN bigint AS $$
DECLARE
  dt_reg  text;
  new_id  bigint;
BEGIN
  dt_reg := tochar(CURRENT_DATE, 'YYYYMMDD') || region;
  SELECT dt_reg::bigint * 10000 + nextval(quote_literal(dt_reg)) INTO new_id;
  RETURN new_id;
END;
$$ LANGUAGE plpgsql STRICT;

在PHP中调用:

SELECT new_date_region_id('0100001');

，它将给出今天指定区域的下一个可用id

Answer 2

在Postgres中避免锁定id的通常方法是通过序列。

你可以在每个区域使用Postgresql序列。就像

create sequence seq_0100001;

，那么你可以使用:

select nextval('seq_'||regioncode) % 10000 as order_seq

这确实意味着订单号不会每天重置为0001，但您的订单号确实具有相同的0000 -> 9999范围。它将环绕。

所以你可能会得到:

2015072201000010001 -> 2015072201000017500 
2015072301000017501 -> 2015072301000019983
2015072401000019984 -> 2015072401000010293

或者，您可以为每个日期/区域组合生成一个序列，但您需要在第二天开始时删除前几天的序列。

Answer 3

尝试使用uidv1类型，它是时间戳和MAC地址的组合。如果插入的顺序对您很重要，您可以让它在服务器端自动生成。否则，可以在插入之前从任何客户机生成id(您可能需要同步它们的时钟)。请注意，使用uidv1可以公开生成UUID的主机的MAC地址。在这种情况下，您可能想要欺骗MAC地址。

对于您的情况，您可以这样做

CREATE TABLE orders (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v1(),
    created_at timestamp NOT NULL DEFAULT now(),
    region_code text NOT NULL REFERENCES...
    ...
);

在http://www.postgresql.org/docs/9.4/static/uuid-ossp.html阅读更多