Re: SELECT FOR UPDATE performance is bad

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mario Splivalo <mario(dot)splivalo(at)mobart(dot)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