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@xxxxxxxxxxxxx> wrote:
Mario Splivalo <mario.splivalo@xxxxxxxxx> 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
