CODEWITHSUNDEEP
multithreadingpostgresql
juk
juk

Reputation: 2329

PostgreSQL: Is using SELECT nextval generator thread safe, in harsh multiuser environments?

I mean like thousands users in time updating values in database?

Upvotes: 16

Views: 9749

Answers (4)

joop
joop

Reputation: 41

This poster asked a different question on the same flawed code. here Point is: he does not seem to know how foreign keys work, and has them reversed (a sequence functioning as a foreign key is kind of awkward IMHO)

BTW: This is should be a comment, not an answer; but I can't comment yet.

Upvotes: 1

Peter Wooster
Peter Wooster

Reputation: 6089

Yes: http://www.postgresql.org/docs/current/static/functions-sequence.html

It wouldn't be useful otherwise.

Edit: Here is how you use nextval and currval:

nextval returns a new sequence number, you use this for the id in an insert on the first table

currval returns the last sequence number obtained by this session, you use that in foreign keys to reference the first table

each call to nextval returns another value, don't call it twice in the same set of inserts.

And of course, you should use transactions in any multiuser code.

Upvotes: 2

Craig Ringer
Craig Ringer

Reputation: 324511

Yes, nextval is safe to use from multiple concurrently operating transactions. That is its purpose and its reason for existing.

That said, it is not actually "thread safe" as such, because PostgreSQL uses a multi-processing model not a multi-threading model, and because most client drivers (libpq, for example) do not permit more than one thread at a time to interact with a single connection.

You should also be aware that while nextval is guaranteed to return distinct and increasing values, it is not guaranteed to do so without "holes" or "gaps". Such gaps are created when a generated value is discarded without being committed (say, by a ROLLBACK) and when PostgreSQL recovers after a server crash.

While nextval will always return increasing numbers, this does not mean that your transactions will commit in the order they got IDs from a given sequence in. It's thus perfectly normal to have something like this happen:

Start IDs in table: [1 2 3 4]
1st tx gets ID 5 from nextval()
2nd tx gets ID 6 from nextval()
2nd tx commits:     [1 2 3 4 6]
1st tx commits:     [1 2 3 4 5 6]

In other words, holes can appear and disappear.

Both these anomalies are necessary and unavoidable consequences of making one nextval call not block another.

If you want a sequence without such ordering and gap anomalies, you need to use a gapless sequence design that permits only one transaction at a time to have an uncommitted generated ID, effectively eliminating all concurrency for inserts in that table. This is usually implemented using SELECT FOR UPDATE or UPDATE ... RETURNING on a counter table.

Search for "PostgreSQL gapless sequence" for more information.

Upvotes: 27

user330315
user330315

Reputation:

Yes it is threadsafe.

From the manual:

nextval

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.

(Emphasis mine)

Upvotes: 5

Related Questions