Can the order of concurrent inserts be guaranteed with some column

Question

I have some services, which can execute insert to insert new elements into a table of my database (Postgresql). So in the view of the database, many inserts are executed concurrently.

My question is if it's possible to keep the insertion order with some column of the table.

For example, there are three inserts:

INSERT INTO Users (UserName, Id) VALUES ('Tom', 1);
INSERT INTO Users (UserName, Id) VALUES ('Jerry', 2);
INSERT INTO Users (UserName, Id) VALUES ('Joey', 3);

They are executing concurrently.

For some reason, the second insert is delayed. In this case, when the third insert is now executing, I'm expecting one of the two results as below:

1. the insert fails;
2. the insert succeed with a new Id 2.

Is there some mechanism to achieve this?

Why do I need this

There is a ID generator which generate IDs with snowflake algorithm. Concurrent inserts are executed in multi-threads. when reading data from the table, I want to make range queries like this:

select * from my_table where Id > a limit 5
select * from my_table where Id > b limit 5
select * from my_table where Id > c limit 5
...

a, b and c are the last Id coming from the previous select.

| --- a --- | --- b --- | --- c --- |

The problem is when the second select is executing, it still might be possible to make a insert in the a zone because of concurrent insert. If this happen, I will have no chance to read the delayed inserted data.

Answer 1

There is no way to do that unless you serialize the inserts rather than performing them concurrently. This can affect your performance quite a bit.

One way to do that is to use a table to generate the id:

CREATE TABLE counter (c bigint);
INSERT INTO counter VALUES (0);

Then your inserts could be:

WITH x AS (
   UPDATE counter SET c = c + 1 RETURNING c
)
INSERT INTO users (username, id)
SELECT 'Jerry', c FROM x;

The update on the counter table locks the row, and the lock is only released when the transaction is done. As a consequence, the order of insert becomes the order of commit, and the order of insert is the same as the order defined by the counter.