Subject: Re: SELECT FOR UPDATE performance is bad
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: Pgsql performance <pgsql-performance@postgresql.org>
In-Reply-To: <13609.1145374386@sss.pgh.pa.us>
References: <1145365720.18469.35.camel@localhost.localdomain>
	<13037.1145370658@sss.pgh.pa.us>
	<1145371662.31555.10.camel@localhost.localdomain>
	<13609.1145374386@sss.pgh.pa.us>
Content-Type: text/plain
Organization: Mob-Art
Date: Wed, 19 Apr 2006 10:20:43 +0200
Message-Id: <1145434844.4458.10.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2006-04-18 at 11:33 -0400, Tom Lane wrote:
> Mario Splivalo <mario.splivalo@mobart.hr> writes:
> >> If there is concurrent locking,
> >> you're also running a big risk of deadlock because two processes might
> >> try to lock the same rows in different orders.
> 
> > I think there is no risk of a deadlock, since that particular function
> > is called from the middleware (functions are used as interface to the
> > database), and the lock order is always the same.
> 
> No, you don't even know what the order is, let alone that it's always
> the same.

You got me confused here! :) If I have just only one function that acts
as a interface to the middleware, and all the operations on the database
are done trough that one function, and I carefuly design that function
so that I first grab the lock, and then do the stuff, aint I pretty sure
that I won't be having any deadlocks? 

> 
> > Now, I just need to have serialization, I need to have clients 'line up'
> > in order to perform something in the database. Actually, users are
> > sending codes from the newspaper, beer-cans, Cola-cans, and stuff, and
> > database needs to check has the code allready been played. Since the
> > system is designed so that it could run multiple code-games (and then
> > there similair code could exists for coke-game and beer-game), I'm using
> > messages table to see what code-game (i.e. service) that particular code
> > belongs.
> 
> I'd suggest using a table that has exactly one row per "code-game", and
> doing a SELECT FOR UPDATE on that row to establish the lock you need.
> This need not have anything to do with the tables/rows you are actually
> intending to update --- although obviously such a convention is pretty
> fragile if you have updates coming from a variety of code.  I think it's
> reasonably safe when you're funneling all the operations through a bit
> of middleware.

I tend to design my applications so I don't have "flying SQL" in my
java/python/c#/php/whereever code, all the database stuff is done trough
the functions which are designed as interfaces. Those functions are also
designed so they don't stop each other. So, since I need the
serialization, I'll do as you suggested, using a lock-table with
exactley one row per "code-game".

Just one more question here, it has to do with postgres internals, but
still I'd like to know why is postgres doing such huge i/o (in my log
file I see a lot of messages that say "LOG:  archived transaction log
file" when performing that big FOR UPDATE.

	Mario
-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."