Postgresql – Database transaction consistency on PostgreSQL

acidbulk-insertconcurrencypostgresqltransaction

In a table with three columns (serial id, varchar name, varchar email) and having two different transactions executing concurrently as the ones bellow:

Transaction 1:

BEGIN;
INSERT INTO public.users (name, email) VALUES ('tart', 'tart@warm.com');
INSERT INTO public.users (name, email) VALUES ('tart123', 'tart123@warm123.com');
COMMIT;

Transaction2:

BEGIN;
INSERT INTO public.users (name, email) VALUES ('raspy', 'raspy@sun.com');
INSERT INTO public.users (name, email) VALUES ('raspy123', 'raspy123@sun123.com');
COMMIT;

Is it possible that the outcome becomes the following?

1 | tart     | tart@warm.com
2 | raspy    | raspy@sun.com
3 | tart123  | tart123@warm123.com
4 | raspy123 | raspy123@sun123.com

Best Answer

serial is defined as such in PostgreSQL documentation:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

They also provide this warning:

Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See nextval() in Section 9.16 for details.

And if you go over to nextval documentation you get:

Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value.

The sequence is a shared object, among all your content. Each call to INSERT will by default call the underlying sequence nextval and will provide one value, irrespective to what happened in the transaction just before. So the value will just depend exactly on the order of operations, and not which transaction started first or things like that.

You could as well get this result (depending on what else is happening elsewhere in your DB at the same time):

 1 | tart     | tart@warm.com
 7 | raspy    | raspy@sun.com
13 | tart123  | tart123@warm123.com
42 | raspy123 | raspy123@sun123.com

Or

 1 | tart     | tart@warm.com
 3 | raspy    | raspy@sun.com
 2 | tart123  | tart123@warm123.com
 4 | raspy123 | raspy123@sun123.com

(which is probably what you expected, but has no specific guarantee to happen).

A sequence just guarantees you to "never" get the same value in two separate places, it does not guarantee lack of holes or monotony in increments, especially accross multiple transactions.

Related Question