Skip site navigation (1) Skip section navigation (2)

Atomic operations?

From: "Paul Tomblin" <ptomblin(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Atomic operations?
Date: 2008-03-18 13:40:47
Message-ID: 8efd35820803180640vf1090dcx8dc1da8b340a1dac@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Ok, I've already written about how my app uses autocommit (although I
have a side project to fix that so you don't need to lecture me about
how bad that is).

I'm doing a simple delete/insert on a table, rather than trying to
figure out whether the row exists or not and then doing an update or
an insert.  But what I'm discovering is that every now and then I get
a duplicate primary key exception, so I figure that two processes are
doing the deletes and inserts and stomping on each other.  So my
question is about combining the delete and insert into one
PreparedStatement.  I've never combined two statements like that, but
somebody on this list mentioned it earlier.  If I say
   PreparedStatement ps = conn.prepareStatement(
      "DELETE FROM venue WHERE venueid = ? ; INSERT INTO VENUE
(venueid, .... ) VALUES(?, ?, ?, ?)");
Does that work?  Will that be atomic so that nobody else can insert
one with that venueid in between the delete and the insert?

-- 
For my assured failures and derelictions I ask pardon beforehand of my
betters and my equals in my Calling here assembled, praying that in
the hour of my temptations, weakness and weariness, the memory of this
my Obligation and of the company before whom it was entered into, may
return to me to aid, comfort and restrain.

Responses

pgsql-jdbc by date

Next:From: Guillaume CottenceauDate: 2008-03-18 13:44:31
Subject: Re: setQueryTimeout problem !?!?!
Previous:From: Woody WoodringDate: 2008-03-18 13:14:27
Subject: Re: JDBC rewriting a bad query?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group