Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4D63E47687A for ; Thu, 16 Jan 2003 11:27:47 -0500 (EST) Received: from lakemtao03.cox.net (lakemtao03.cox.net [68.1.17.242]) by postgresql.org (Postfix) with ESMTP id D65DA476CC6 for ; Thu, 16 Jan 2003 11:25:32 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao03.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030116162538.LDCY8666.lakemtao03.cox.net@[192.168.1.1]> for ; Thu, 16 Jan 2003 11:25:38 -0500 Subject: Re: schema/db design wrt performance From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <20030116080132.A4962-100000@megazone23.bigpanda.com> References: <20030116080132.A4962-100000@megazone23.bigpanda.com> Content-Type: text/plain Organization: Message-Id: <1042734336.892.42.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 16 Jan 2003 10:25:36 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/155 X-Sequence-Number: 802 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? 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. Am I missing something? -- +------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Basically, I got on the plane with a bomb. Basically, I | | tried to ignite it. Basically, yeah, I intended to damage | | the plane." | | RICHARD REID, who tried to blow up American Airlines | | Flight 63 | +------------------------------------------------------------+