CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3
user1224129
user1224129

Reputation: 2779

Is the assign operation atomic in PostgreSQL?

I would like to know if the assign operation is atomic in PostgreSQL. I have a table with counter that will be incremented from multiple threads like this:

UPDATE SomeTable SET Counter = Counter + 1 WHERE Id = 10

Is it possible that multiple postgresql-threads remember and update the same value causing data loss?

-- Counter = 111
THREAD #1: UPDATE SomeTable SET Counter = Counter + 1 WHERE Id = N
THREAD #2: UPDATE SomeTable SET Counter = Counter + 1 WHERE Id = N
-- Counter must be 113 even if two threads update it simultaneously

Do PostgreSQL guarantee the atomicy of increment operations?

Upvotes: 2

Views: 250

Answers (3)

hnh
hnh

Reputation: 14795

There is no simple answer to this, it depends on your transaction isolation level and what level of 'safe' you need (e.g. is it OK if the TX fails on a concurrent modification?). You can read up on this over here: http://www.postgresql.org/docs/9.3/static/transaction-iso.html. To achieve what you want here, you would likely need to run your code in a transaction with a serializable isolation level.

You don't say much on what you are actually trying to achieve, but maybe an approach using a Sequence is a better choice for your problem?:

http://www.postgresql.org/docs/9.3/static/sql-createsequence.html

Upvotes: 1

Tomasz Siorek
Tomasz Siorek

Reputation: 711

The way you provided is safe since the values aren't fixed. All the transactions are counting up the value from a snaphot visible only to them. There is a lot of information on concurrency in the documentation: http://www.postgresql.org/docs/current/static/mvcc-intro.html

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32170

Short answer: Yes. That's the point of a transactional RDBMS (i.e., not MySQL running MyISAM or MEMORY). A transaction either completes 100% successfully or fails 100% completely and rolls back. You may also want to read up on transactions, although this doc only touches on the subject. The tutorial there essentially uses the exact scenario you describe as an example.

You're not going to lose data, but you may run in to locking issues. See the doc on concurrency.

Upvotes: 0

Related Questions