Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I don't understand what Craig Ringer meant when he commented:
This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected any rows.
on https://stackoverflow.com/a/8702291/14731. Please provide a sample sequence of events (e.g. Thread 1 does X, Thread 2 does Y) that demonstrates how lost updates could occur.
I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore.
The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version.
(Note that all of these solutions will only work well if you try to change exactly one row with each transaction. As soon as you try to do multiple changes in one transaction things get messy because of the need for retry loops on rollbacks. At minimum you'd need to use a savepoint between each change.)
The version that uses two separate statements is subject to lost updates unless the application checks the affected-row count from the UPDATE statement and the INSERT statement and retries if both are zero.
Imagine what happens if you have two transactions in READ COMMITTED isolation.
UPDATE (no effect)INSERT (inserts a row)UPDATE (no effect, row inserted by TX1 isn't visible yet)COMMITs. INSERT, *which gets a new snapshot that can see the row committed by TX1. The EXISTS clause returns true, because TX2 can now see the row inserted by TX1.So TX2 has no effect. Unless the app checks the rowcount from the update and the insert and retries if both report zero rows, it won't know that the transaction had no effect and will merrily carry on.
The only way it can check the affected rowcounts is to run it as two separate statements rather than a multi-statement, or use a procedure.
You can use SERIALIZABLE isolation, but you'll still need a retry loop to deal with serialization failures.
The wCTE version protects against the lost updates issue because the INSERT is conditional on whether the UPDATE affects any rows, rather than on a separate query.
The writeable CTE version still isn't a reliable upsert.
Consider two transactions that run this concurrently.
Both execute the VALUES clause.
Now both of them execute the UPDATE portion. Since there are no rows matching the UPDATEs where clause, both return an empty resultset from the update and make no changes.
Now both run the INSERT portion. Since the UPDATE returned zero rows for both queries, both attempt to INSERT the row.
One succeeds. One throws a unique violation and aborts.
This isn't cause for concern about data loss so long as the app checks for error results from its queries (i.e. any decently written app) and re-tries, but it makes the solution no better than the existing two-statement versions. It doesn't eliminate the need for a retry loop.
The advantage the wCTE offers over the existing two-statement version is that it uses the output of the UPDATE to decide whether to INSERT, instead of using a separate query against the table. That's partly an optimisation, but it partly protects against a problem with the two-statement version that causes lost updates; see below.
You can run the wCTE in SERIALIZABLE isolation, but then you'll just get serialization failures instead of unique violations. It won't change the need for a retry loop.
My comment suggested that this solution could result in lost updates, but upon reviewing that I think I may have been mistaken.
It's over a year ago, and I can't recall the exact circumstances, but I think I probably missed the fact that unique indexes have a partial exception from transaction visibility rules in order to allow one inserting transaction to wait for another to insert or roll back before proceeding.
Or perhaps I missed the fact that the INSERT in the wCTE is conditional on whether the UPDATE affected any rows, not on whether the candidate row exists in the table.
Conflicting INSERTs on a unique index wait for commit/rollback
Say that one copy of the query runs, inserting a row. The change is not yet committed. The new tuple exists in the heap and the unique index, but it isn't visible to other transactions yet, irrespective of isolation levels.
Now another copy of the query runs. The inserted row is not yet visible as the first copy hasn't committed, so the update doesn't match anything. The query will go on to attempt an insert, which will see that another in-progress transaction is inserting that same key and will block waiting for that transaction to commit or roll back.
If the first transaction commits, the second one will fail with a unique violation, per the above. If the first transaction rolls back then the second will proceed with its insert instead.
The INSERT being dependent on the UPDATE rowcount protects against lost updates
Unlike in the two-statement case, I don't think the wCTE is vulnerable to lost updates.
If the UPDATE has no effect, the INSERT will always run, because it's strictly conditional on whether the UPDATE did anything, not on the external table state. So it can still fail with a unique violation, but it can't silently fail to have any effect and lose the update entirely.
