Thread: Solution to UPDATE or INSERT Problem

I've seen a couple of questions here in the past about how to update an
existing row or insert a row if it doesn't exist without race conditions
that could cause you to have to retry a transaction. I didn't find any
answers to this question in the archives however, so I thought I'd post
my solution here for the edification of others.

    INSERT INTO my_table (key, value) SELECT 1, 'a value'
    WHERE NOT EXISTS (SELECT key FROM my_table WHERE key = 1);
    UPDATE my_table SET value = 'a value' WHERE key = 1;

This, as far as I can tell, will never fail, though it might invoke
triggers that would not otherwise be invoked if only the single
necessary statement (the INSERT or the UPDATE) were executed. I'd imagine
in most cases that this would not be a big problem.

IMHO, this is a little bit better than the MySQL REPLACE command, since
it doesn't cause deletion of an entire row and reinsertion, which can
play hell with things like my change logging triggers. (It would record
all the deleted values in the change log table and then an insert,
rather than recording just the changed values.)

If you have any comments you want me to see, please be sure that I'm in
the cc list on the message, as I'm not subscribed to this list.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.NetBSD.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Curt Sampson <cjs@cynic.net> writes:
> I've seen a couple of questions here in the past about how to update an
> existing row or insert a row if it doesn't exist without race conditions
> that could cause you to have to retry a transaction. I didn't find any
> answers to this question in the archives however, so I thought I'd post
> my solution here for the edification of others.

>     INSERT INTO my_table (key, value) SELECT 1, 'a value'
>     WHERE NOT EXISTS (SELECT key FROM my_table WHERE key = 1);
>     UPDATE my_table SET value = 'a value' WHERE key = 1;

> This, as far as I can tell, will never fail,

You're quite mistaken.  Have you made any effort to test it?

<< session 1 >>

regression=# create table my_table (key int unique, value text);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "my_table_key_key" for table "my_table"
CREATE TABLE
regression=# begin;
BEGIN
regression=# INSERT INTO my_table (key, value) SELECT 1, 'a value'
regression-# WHERE NOT EXISTS (SELECT key FROM my_table WHERE key = 1);
INSERT 429665 1

<< session 2 >>

regression=# begin;
BEGIN
regression=# INSERT INTO my_table (key, value) SELECT 1, 'a value'
regression-# WHERE NOT EXISTS (SELECT key FROM my_table WHERE key = 1);

<< session 2 hangs >>

<< back to session 1 >>

regression=#  UPDATE my_table SET value = 'a value' WHERE key = 1;
UPDATE 1
regression=# commit;
COMMIT
regression=#

<< now session 2 fails: >>

ERROR:  duplicate key violates unique constraint "my_table_key_key"
regression=#

            regards, tom lane

Curt Sampson <cjs@cynic.net> writes:
> On Mon, 19 Jan 2004, Tom Lane wrote:
>> You're quite mistaken.  Have you made any effort to test it?

> Yes. You appear to have changed my code somewhat:
> ...
> Take out that BEGIN.

The BEGIN was merely a convenient way of slowing down operations enough
so that a trivial manual test would expose the problem.  Concurrent
executions of that INSERT/SELECT *will* fail, it's just a matter of
getting them to actually overlap in time.  With BEGIN the window for
concurrency failures is wider than without --- but it's not zero without.

> Yup. You re-created the race condition that I'd gotten rid of when you
> put the INSERT and the UPDATE into the same transaction.

You need to go back and re-read the documentation... the UPDATE does not
actually have anything to do with the failure.

            regards, tom lane

Curt Sampson <cjs@cynic.net> writes:
> But why does session 2 hang
> in this instance?

Because the sub-SELECT sees a snapshot of transactions that were already
committed at the start of session 2's transaction.  If session 1 hasn't
committed yet, the EXISTS will return false.  The actual INSERT will
notice the inserted-but-not-yet-committed row, and will block waiting to
see whether it gets committed or not.

            regards, tom lane