Can an INSERT-SELECT query be subject to race conditions?

Question

I have this query that attempts to add rows to the balances table if no corresponding row exists in the totals table. The query is run in a transaction using the default isolation level on PostgreSQL.

INSERT INTO balances (account_id, currency, amount)
SELECT t.account_id, t.currency, 0
FROM balances AS b
RIGHT OUTER JOIN totals USING (account_id, currency) AS t
WHERE b.id IS NULL

I have a UNIQUE constraint on balances (accountId, currency). I'm worried that I will get into a race condition situation that will lead to duplicate key errors if multiple sessions execute this query concurrently. I've seen many questions on that subject, but they all seem to involve either subqueries, multiple queries or pgSQL functions.

Since I'm not using any of those in my query, is it free from race conditions? If it isn't how can I fix it?

Answer 1

Yes it will fail with a duplicate key value violates unique constraint error. What I do is to place the insertion code in a try/except block and when the exception is thrown I catch it and retry. That simple. Unless the application has a huge amount of users it will work flawlessly.

In your query the default isolation level is enough since it is a single insert statement and there is no risk of phantom reads.

Notice that even when setting the isolation level to serializable the try/except block is not avoidable. From the manual about serializable:

like the Repeatable Read level, applications using this level must be prepared to retry transactions due to serialization failures

Answer 2

The default transaction level is Read Committed. Phantom reads are possible in this level (see Table 13.1). While you are protected from seeing any weird effects in the totals table were you to update the totals, you are not protected from phantom reads in the balances table.

What this means can be explained when looking at a single query similar to yours that attempts the outer join twice (and only queries, does not insert anything). The fact that a balance is missing is not guaranteed to stay the same between the two "peeks" at the balances table. The sudden appearance of a balance that wasn't there when the same transaction looked for the first time, is called a "phantom read".

In your case, several concurrent statements can see that a balance is missing and nothing prevents them trying to insert it and error out.

To rule out phantom reads (and to fix your query), you need to execute in in the SERIALIZABLE isolation level prior to running your query:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE