Re: Parallel transactions failing oddly

On pe, 2003-08-01 at 09:21, Stephan Szabo wrote:
> 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.
>
c++-code calling function that is calling several functions... Tried
locking the table at the very first function and locking the table just
before the culprit select and inserts.

I haven't touched the transaction isolation settings so the isolation
level is read committed.

I talked again with the database coder and reread the documentation
about the concurrency control and mvcc-snapshots. And thanks to your
responses, I have now somekind of clue what is happening.

I think it is time for the databse coder to rethink the application
logic and for me to do some more tests. If this won't help, I'll bug you
again with simplified case. Thank you very much for the prompt
assistance.