Re: SELECT FOR UPDATE performance is bad - Mailing list pgsql-performance

From Mario Splivalo
Subject Re: SELECT FOR UPDATE performance is bad
Date
Msg-id 1145434854.4458.12.camel@localhost.localdomain
Whole thread Raw
In response to Re: SELECT FOR UPDATE performance is bad  (PFC <lists@peufeu.com>)
Responses Re: SELECT FOR UPDATE performance is bad
List pgsql-performance
On Tue, 2006-04-18 at 19:00 +0200, PFC wrote:
> 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).
>
>

This works perfectly, but sometimes the game has no codes, and I still
need to know exactley who came first, who was second, and so on... So a
locking table as Tom suggested is, I guess, a perfect solution for my
situation...

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



pgsql-performance by date:

Previous
From: Mario Splivalo
Date:
Subject: Re: SELECT FOR UPDATE performance is bad
Next
From: Theo Kramer
Date:
Subject: Re: Multicolumn order by