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@xxxxxxxxx "I can do it quick, I can do it cheap, I can do it well. Pick any two."
