Thread: Atomic operations?
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.
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
On Tue, Mar 18, 2008 at 10:06 AM, Dave Cramer <pg@fastcrypt.com> wrote: > On 18-Mar-08, at 9:40 AM, Paul Tomblin wrote: > > 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 > > Can't you just check the return value from the delete to see if > anything was deleted ? I'm not sure how that would help. If I do the delete and it didn't delete anything, that doesn't tell me if some other process inserted it in the meantime. -- 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.
On 18-Mar-08, at 10:12 AM, Paul Tomblin wrote: > On Tue, Mar 18, 2008 at 10:06 AM, Dave Cramer <pg@fastcrypt.com> > wrote: >> On 18-Mar-08, at 9:40 AM, Paul Tomblin wrote: >>> 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 >> >> Can't you just check the return value from the delete to see if >> anything was deleted ? > > I'm not sure how that would help. If I do the delete and it didn't > delete anything, that doesn't tell me if some other process inserted > it in the meantime. > which ever process succeeds in deleting should do the insert. Dave > > > > -- > 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.
On Tue, Mar 18, 2008 at 10:17 AM, Dave Cramer <pg@fastcrypt.com> wrote: > >> On 18-Mar-08, at 9:40 AM, Paul Tomblin wrote: > >>> 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 > >> Can't you just check the return value from the delete to see if > >> anything was deleted ? > > > > I'm not sure how that would help. If I do the delete and it didn't > > delete anything, that doesn't tell me if some other process inserted > > it in the meantime. > > > which ever process succeeds in deleting should do the insert. Doesn't help in the case where the record wasn't in there in the first place. -- 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.
On 18-Mar-08, at 11:05 AM, Paul Tomblin wrote: > On Tue, Mar 18, 2008 at 10:17 AM, Dave Cramer <pg@fastcrypt.com> > wrote: >>>> On 18-Mar-08, at 9:40 AM, Paul Tomblin wrote: >>>>> 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 > >>>> Can't you just check the return value from the delete to see if >>>> anything was deleted ? >>> >>> I'm not sure how that would help. If I do the delete and it didn't >>> delete anything, that doesn't tell me if some other process inserted >>> it in the meantime. >>> >> which ever process succeeds in deleting should do the insert. > > Doesn't help in the case where the record wasn't in there in the > first place. > What you are looking for is upsert. It's on the todo list http://www.postgresql.org/docs/faqs.TODO.html In the meantime you can lock the table or create a function to do a select and update or insert. Dave > -- > 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. > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
Dave Cramer wrote: > > On 18-Mar-08, at 9:40 AM, Paul Tomblin wrote: >> 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, Actually it's fine the implicit transaction created by the server to support autocommit wraps both queries in a single transaction and does not commit/rollback until the end of the second query. -O
On 18-Mar-08, at 7:18 PM, Oliver Jowett wrote: > Dave Cramer wrote: >> On 18-Mar-08, at 9:40 AM, Paul Tomblin wrote: > >>> 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, > > Actually it's fine the implicit transaction created by the server to > support autocommit wraps both queries in a single transaction and > does not commit/rollback until the end of the second query. > Yeah, using v3 protocol this is presented as one prepare/execute. Thanks for catching that. > -O > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
On Wed, 19 Mar 2008, Dave Cramer wrote: > > On 18-Mar-08, at 7:18 PM, Oliver Jowett wrote: > >> Actually it's fine the implicit transaction created by the server to >> support autocommit wraps both queries in a single transaction and does not >> commit/rollback until the end of the second query. >> > Yeah, using v3 protocol this is presented as one prepare/execute. Thanks for > catching that. For the record, v2 sends both statements in a single query message grouping them in the same transaction. v3 sends a prepare and execute for each statement (as required by the extended query protocol), but the transaction is demarcated by the Sync message and there's only one of those. Kris Jurka