To: mario.splivalo@mobart.hr
cc: pgsql-performance@postgreSQL.org
Subject: Re: SELECT FOR UPDATE performance is bad 
In-reply-to: <1145371662.31555.10.camel@localhost.localdomain> 
References: <1145365720.18469.35.camel@localhost.localdomain>
	<13037.1145370658@sss.pgh.pa.us>
	<1145371662.31555.10.camel@localhost.localdomain>
Comments: In-reply-to Mario Splivalo <mario.splivalo@mobart.hr>
	message dated "Tue, 18 Apr 2006 16:47:42 +0200"
Date: Tue, 18 Apr 2006 11:33:06 -0400
Message-ID: <13609.1145374386@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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.

> 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.

			regards, tom lane