From: Tom Lane <tgl@sss.pgh.pa.us>
To: mario.splivalo@mobart.hr
Cc: pgsql-performance@postgreSQL.org
Subject: Re: SELECT FOR UPDATE performance is bad
Date: Tue, 18 Apr 2006 11:33:06 -0400
Message-ID: <13609.1145374386@sss.pgh.pa.us> (raw)
In-Reply-To: <1145371662.31555.10.camel@localhost.localdomain>
References: <1145365720.18469.35.camel@localhost.localdomain>
<13037.1145370658@sss.pgh.pa.us>
<1145371662.31555.10.camel@localhost.localdomain>
Mario Splivalo <mario.splivalo@mobart.hr> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mario.splivalo@mobart.hr
Subject: Re: SELECT FOR UPDATE performance is bad
In-Reply-To: <13609.1145374386@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox