Re: SELECT FOR UPDATE performance is bad

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: mario(dot)splivalo(at)mobart(dot)hr
Cc: pgsql-performance(at)postgreSQL(dot)org
Subject: Re: SELECT FOR UPDATE performance is bad
Date: 2006-04-18 15:33:06
Message-ID: 13609.1145374386@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Mario Splivalo <mario(dot)splivalo(at)mobart(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tarabas (Manuel Rorarius) 2006-04-18 15:34:59 Re: Problem with LIKE-Performance
Previous Message Tom Lane 2006-04-18 15:20:20 Re: Problem with LIKE-Performance