To: "Tom Lane" <tgl@sss.pgh.pa.us>, mario.splivalo@mobart.hr
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT FOR UPDATE performance is bad 
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>
Message-ID: <op.s77qveghcigqcu@apollo13>
Date: Tue, 18 Apr 2006 19:00:40 +0200
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <13609.1145374386@sss.pgh.pa.us>
User-Agent: Opera M2/8.51 (Linux, build 1462)


Suppose you have a table codes :
(
	game_id	INT,
	code		TEXT,
	used		BOOL NOT NULL DEFAULT 'f',
	prize		...
	...
	PRIMARY KEY (game_id, code)
)

	Just UPDATE codes SET used='t' WHERE used='f' AND game_id=... AND code=...

	Then check the rowcount : if one row was updated, the code was not used  
yet. If no row was updated, the code either did not exist, or was already  
used.

Another option : create a table used_codes like this :

(
	game_id	INT,
	code		TEXT,
	...
	PRIMARY KEY (game_id, code)
)

	Then, when trying to use a code, INSERT into this table. If you get a  
constraint violation on the uniqueness of the primary key, your code has  
already been used.

	Both solutions have a big advantage : they don't require messing with  
locks and are extremely simple. The one with UPDATE is IMHO better,  
because it doesn't abort the current transaction (although you could use a  
savepoint in the INSERT case to intercept the error).


On Tue, 18 Apr 2006 17:33:06 +0200, Tom Lane <tgl@sss.pgh.pa.us> 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.
>
>> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings