The Art of PostgreSQL/

...

/

Concurrent Updates and Isolation

Concurrent Updates and Isolation

Learn about concurrent updates and isolation in PostgreSQL.

We'll cover the following...

In our Tweet model of an application, we look at handling Retweets, which is a counter field in the tweet.message table. Here’s how to make a Retweet in our model:

PostgreSQL
update tweet.message
   set rts = rts + 1
 where messageid = 1;

What happens if two users are doing that at the same time?

Manual transaction

To better understand what “at the same time” means here, we can write the query extended with manual transaction control, as PostgreSQL will do when sent a single command without an explicit transaction:

PostgreSQL
begin;
   update tweet.message
      set rts = rts + 1
    where messageid = 1
returning messageid, rts;
commit;

Now, rather than doing this query, we open a psql prompt and send in:

PostgreSQL
begin;
   update tweet.message
      set rts = rts + 1
    where messageid = 1
returning messageid, rts;

The transaction remains open (it’s idle in transaction) and waits for us to do something else or maybe commit or rollback the transaction.

Now, open a second psql prompt and send in the exact same query. This time the update doesn’t return. ...