Re: SELECT FOR UPDATE performance is bad

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