Re: Changing the transaction isolation level within the stored

On Thu, Jan 26, 2006 at 01:51:27PM +0100, Markus Schaber wrote:
> Hmm, are you shure that this is correct? The delete will always delete 0
> rows.

Quite, and no it won't. The contrived example is actually a
simplification of a case one of our developers implemented. The
conflict is on the updates. Two concurrent transactions likely
wouldn't be enough to cause it on a fast system, but multiple ones
for sure will.

The problem is that the updates have to wait for one another to
complete in order to know what result they can use, but then the
_other_ contention on the other table causes them to have to wait for
one another there. I don't think anybody would have gone to the
trouble of putting in deadlock detection if the only way to deadlock
was to trip over yourself with manual locking: presumably, if you're
issuing locks by hand, you either know what you're doing or get what
you deserve.

> Depending on the transaction isolation level and exact timings,
> colliding queries may lead to different results or even one transaction
> aborted, but there is no deadlock under MVCC.
>
> Not needing such locks is the whole point in using MVCC at all.

I think you don't have a clear idea of what locks are necessary for
updates. Write operations on a row must block other write operations
on the same row. If more than one transaction needs the same kinds
of locks on two different tables, but attempts to get those locks in
the opposite order, you are all but guaranteed a deadlock. MVCC
helps, but it can't avoid locking the same data when that data is
being updated.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland