CODEWITHSUNDEEP
postgresqlconcurrencyparallel-processingdatabase-concurrency
Jane.L
Jane.L

Reputation: 433

Can I rely on it that every single update statement in postgresql is atomic?

I read some materials before saying that each update statement in postgresql is atomic.

For example,

update set column_1 = column_1 + 100 where column_2 = 10;

Even though I have multiple processes calling the update simultaneously, I can rely on it that they will happen in sequence because each update is atomic behind the scene and the "read_modify_write" cycle is encapsulated in a bundle.

However, what if the update statement looks like the following:

update set column_1 = myFunction(column_1) where column_2 = 10;

Here, myFunction() is a stored procedure created by me.In this function, I will apply different math operations to column_1 depending on its amount. Something like:

    if(column_1 < 10):
            // do something
    else if (column_1 < 20):
            // do something
    else
            // do something

In this case, when the single update statement contains self-defined function, does it remain atomic?

Upvotes: 4

Views: 3997

Answers (2)

Schwern
Schwern

Reputation: 164639

The update, subqueries, and queries in function calls should all see a consistent view of the data.

From Chapter 13. Concurrency Control.

Internally, data consistency is maintained by using a multiversion model (Multiversion Concurrency Control, MVCC). This means each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data rows, providing transaction isolation for each database session.

What that means, I believe, is for each statement Postgres keeps a version of the data. Each statement sees a consistent version of the database for the entirety of its run. That includes sub-queries and function calls.

This doesn't mean you don't have to think about concurrency. It just means that update will see consistent data.

Upvotes: 0

Richard Huxton
Richard Huxton

Reputation: 22893

OK, @Schwern's knowledge of Perl may well be world class but as regards PostgreSQL transactions, I can correct him :-)

Every statement in PostgreSQL is executed within a transaction, either an explicit one you BEGIN/COMMIT yourself or an implicit one wrapping the statement. For the duration of a statement you will see a stable view of the entire database.

If you write myFunction as an in-database custom function in pl/pgsql or some such then it too will be in the same transaction as the statement that calls it. If it doesn't run its own queries, just operates on its parameters then you don't need to think any further.

If you are reading from tables within your function then you will need a passing familiarity with transaction isolation levels. In particular, make sure you understand what "read committed" implies about seeing other processes' activities.

The blog article you refer to is discussing performing operations outside of the database. The solution it proposes is exactly what you are asking about - an atomic update.

Upvotes: 4

Related Questions