Re: Problem with transactions

[Richard Huxton <dev(at)archonet(dot)com>]

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