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(dot)splivalo(at)mobart(dot)hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."