Re: Atomic operations?

On 18-Mar-08, at 9:40 AM, Paul Tomblin wrote:

> 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?
>
I don't think so, there are two statements there separated by a ; The
window will be much smaller and it will appear to be better, but the
opportunity is still there.

Can't you just check the return value from the delete to see if
anything was deleted ?

Dave