pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Ron Johnson <ron.l.johnson@cox.net>
Cc: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: schema/db design wrt performance
Date: Thu, 16 Jan 2003 08:38:08 -0800 (PST)
Message-ID: <20030116083403.X4962-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <1042734336.892.42.camel@haggis>

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.





view thread (12+ messages)  latest in thread

Message-ID: <20030116083403.X4962-100000@megazone23.bigpanda.com>
Permalink:  ../20030116083403.X4962-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20030116083403.X4962-100000@megazone23.bigpanda.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sszabo@megazone23.bigpanda.com, ron.l.johnson@cox.net
  Subject: Re: schema/db design wrt performance
  In-Reply-To: <20030116083403.X4962-100000@megazone23.bigpanda.com>
  References: <1042734336.892.42.camel@haggis> <20030116083403.X4962-100000@megazone23.bigpanda.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox