Re: Parallel transactions failing oddly

On 1 Aug 2003, Mauri Sahlberg wrote:

> On pe, 2003-08-01 at 08:51, Stephan Szabo wrote:
> > On 1 Aug 2003, Mauri Sahlberg wrote:
> >
> > > On pe, 2003-08-01 at 03:12, Stephan Szabo wrote:
> > > > > interface. If we run them one by one everything goes fine. But if I
> > > > > run them in parallel - in separate processes - all but the first one
> > > > > claiming the lock for "ryhmalaiset"-table will fail. And they will
> > > > > fail as soon as the first one is finished by trying to insert
> > > > > duplicate row in the shared table. Incidentally this row would also be
> > > > > the very first row they are trying to insert. They all run the same code
> > > > > but with different data.
> > > > >
> > > > The second transaction won't see the row inserted by the first transaction
> > > > until it commits (at best).  Both transactions can think there are no
> > > > matching rows.
> > >
> > > Umh, but as the "ryhmalaiset" table is locked until the transaction is
> > > commited? And what do you mean with "at best"? Is there any way ensuring
> > > that the other transactions won't access the table until the first one
> > > has finished updating it if the lock is not enough?
> >
> > I said at best because I dont think serializable mode transactions won't
> > see the row even after commit as long as its snapshot's been taken
> > already.  You are locking the table in access exclusive mode right?
>
> Yes.

Another possibility is if you locking the table inside the function?  If
so, I think you may not see the state of the other transaction's commits
even in read committed isolation.