Thanks Martijn. What you missed is that there are many clients each with
thier own transaction trying to do simular things. What I was trying to
illistrate is that there are occasions where a SQL error may happen that can
be handled and would not need the transaction to be aborted.
In the example I gave, the record is already there but the second client
cannot see it yet (not commited) so it attempts an insert too. If the first
client is successful and commits then the second client will get an SQL error
on insert for duplicate key. In Postgre currently this required that the
second client rollback everything in the transaction when it would be a
simple matter to catch the duplicate key error, select back the record, and
update it.
On Tuesday 13 August 2002 09:16 pm, Martijn van Oosterhout wrote:
> On Tue, Aug 13, 2002 at 10:42:07AM -0700, Jon Swinth wrote:
> > Thanks Scott for your reply.
> >
> > I don't agree that an insert/update/delete error should automatically
> > abort the transaction. You have not provided for the fact that the error
> > may be handled. I will give you an example that makes my case.
> >
> > Lets say you have an inventory table. The inventory table has a primary
> > key of an integer and a unique key of location and product. The unique
> > key makes sure that there is only one record for each product against a
> > single location. Now imagine that you have a high volume database with
> > many clients and you have a process that attempts to put quantity of a
> > product into a location. That process would first select to see if the
> > record already existed so it could be update and then insert a row when
> > it wasn't found. Now imagine that this is just part of a long running
> > transaction and that multiple clients will want to put more of the same
> > product in the same location.
>
> Quick question (maybe I'm misunderstanding something) but why are all these
> unrelated queries all in the same transaction? If you commited between each
> update your problem goes away.
>
> What you are trying to do could be acheived using using LOCKs but you don't
> want that.