Thread: Problem with transactions

I want two users to execute the following queries:

1) delete from forum where id = 'A';
1) insert into forum (id, name) values ('A', 'testa');

2) delete from forum where id = 'A';
2) insert into forum (id, name) values ('A', 'testb');

id is a unique key. The numbers designate the user (1 and 2). The problem is
that I don't know in which order the queries are executed (I only know each
user executes its DELETE query before the INSERT query). I can't use UPDATE
because I don't know in advance that there exist a row with id A.

How do I prevent the queries from being executed in the wrong order and thus
causing an 'duplicate key violates unique constraint' error?

Matthijs Melissen wrote:
> I want two users to execute the following queries:
>
> 1) delete from forum where id = 'A';
> 1) insert into forum (id, name) values ('A', 'testa');
>
> 2) delete from forum where id = 'A';
> 2) insert into forum (id, name) values ('A', 'testb');
>
> id is a unique key. The numbers designate the user (1 and 2). The
> problem is that I don't know in which order the queries are executed (I
> only know each user executes its DELETE query before the INSERT query).
> I can't use UPDATE because I don't know in advance that there exist a
> row with id A.

So how are you picking "A"? If you don't know whether that key is
already in the database, why has user2 deleted user1's row?

If you want to know whether there is a row with that key in the database
why not use a SELECT?

> How do I prevent the queries from being executed in the wrong order and
> thus causing an 'duplicate key violates unique constraint' error?

You don't say what the "wrong" order is, and why.
You're trying to insert a duplicate key - you should get an error.

I think you're going to have to explain what it is you're trying to
achieve. You're not trying to re-invent the SERIAL type are you?

--
   Richard Huxton
   Archonet Ltd