Re: schema/db design wrt performance

On Thu, Jan 16, 2003 at 10:25:36AM -0600, Ron Johnson wrote:
>
> 2 points:
>
> 1. Don't you *want* TXN2 to wait on TXN1?

Not really.  You really just want a tag which prevents TXN2 from
committing when its reference data might go away.  So what you want
is a lock which says "don't delete, no matter what", until TXN2
commits.  Then TXN1 could fail or not, depending on what it's trying
to do.  The problem is that there isn't a lock of the right strength
to do that.

> 2. In an OLTP environment (heck, in *any* environment), the goal
>    is to minimize txn length, so TXN2 shouldn't be waiting on
>    TXN1 for more than a fraction of a second anyway.

Right.  But it's possible to have multiple REFERENCES constraints
to the same table; that's why I picked an account table, for
instance, because you might have a large number of different kinds of
things that the same account can do.  So while you're correct that
one wants to minimize txn length, it's also true that, when the
effects are followed across a large system, you can easily start
tripping over the FKs.  The real problem, then, only shows up on a
busy system with a table which gets referenced a lot.

I should note, by the way, that the tremendous performance
improvements available in 7.2.x have reduced the problem considerably
from 7.1.x, at least in my experience.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110