Re: schema/db design wrt performance

On 16 Jan 2003, Ron Johnson wrote:

> On Thu, 2003-01-16 at 10:02, Stephan Szabo wrote:
> > On 16 Jan 2003, Ron Johnson wrote:
> >
> > > On Thu, 2003-01-16 at 09:39, Andrew Sullivan wrote:
> > > > On Thu, Jan 16, 2003 at 08:34:38AM -0600, Ron Johnson wrote:
> > > > > On Thu, 2003-01-16 at 08:20, Andrew Sullivan wrote:
> > > >
> > > > > > If a user has multiple connections and charges things to the same
> > > > > > account in more than one connection at the same time, the
> > > > > > transactions will have to be processed, effectively, in series: each
> > > > > > one will have to wait for another to commit in order to complete.
> > > > >
> > > > > This is true even though the default transaction mode is
> > > > > READ COMMITTED?
> > > >
> > > > Yes. Remember, _both_ of these are doing SELECT. . .FOR UPDATE.
> > > > Which means they both try to lock the corresponding record. But they
> > > > can't _both_ lock the same record; that's what the lock prevents.
> > >
> > > Could BEFORE INSERT|UPDATE|DELETE triggers perform the same
> > > functionality while touching only the desired records, thus
> > > decreasing conflict?
> >
> > It does limit it to the corresponding records, but if you
> > say insert a row pointing at customer 1, and in another transaction
> > insert a row pointing at customer 1, the second waits on the first.
>
> 2 points:
>
> 1. Don't you *want* TXN2 to wait on TXN1?

Not really. Maybe I was unclear though.

Given
create table pktable(a int primary key);
create table fktable(a int references pktable);
insert into pktable values (1);

The blocking would occur on:
T1: begin;
T2: begin;
T1: insert into fktable values (1);
T2: insert into fktable values (1);

This doesn't need to block. The reason for
the lock is to prevent someone from updating
or deleting the row out of pktable, but it
also prevents this kind of thing. This becomes
an issue if you say have tables that store mappings
and a table that has an fk to that. You'll
be inserting lots of rows with say
customertype=7 which points into a table with
types and they'll block. Worse, if you say
do inserts with different customertypes in
different orders in two transactions you
can deadlock yourself.