From: PFC <lists@peufeu.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: mario.splivalo@mobart.hr
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT FOR UPDATE performance is bad
Date: Tue, 18 Apr 2006 19:00:40 +0200
Message-ID: <op.s77qveghcigqcu@apollo13> (raw)
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>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, tgl@sss.pgh.pa.us, mario.splivalo@mobart.hr
Subject: Re: SELECT FOR UPDATE performance is bad
In-Reply-To: <op.s77qveghcigqcu@apollo13>
* 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