On Tue, 31 Jul 2001, will trillich wrote:
> Stephan Szabo wrote:
> >
> > ISTM, in general, the above construct is not safe for general use. Say
> > you have two transactions:
> >
> > Transaction 1 start
> > Transaction 2 start
> > Transaction 1 selects on tbl, gets no rows
> > Transaction 2 selects on tbl, gets no rows
> > Transaction 1 inserts
> > Transaction 2 inserts
>
> aha. boom, integrity check failure. hmm.
>
> > Transaction 1 commits
> > Transaction 2 commits
> >
> > Both transactions would do an insert (not seeing the other) and you'd
> > have two lookup values for the same val. I think you'd need an explicit
> > lock on tbl to make it safe.
>
> is that something that the trigger method manages to
> circumvent somehow? (i presume 'explicit table lock'
> is covered on a page of documentation i haven't run
> across yet...)
I meant using a lock table statement explicitly at the
beginning of the trigger (LOCK TABLE tbl; -- possibly
would have to be through execute, I'm not sure) which
I would presume would mean that the "second" would
have to wait at that point until the first transaction
finished completely. Of course this cuts down your
concurrency as only one transaction calling this would
be able to run and the rest would have to wait.