pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alexander Popkov <voodoo@wenet.ru>
To: pgsql-php@postgresql.org, Hans-Jürgen Schönig <postgres@cybertec.at>
Subject: Re: LOCKing method in my situation?
Date: Sun, 1 Feb 2004 22:11:54 +0300
Message-ID: <106387628840.20040201221154@wenet.ru> (raw)
In-Reply-To: <401D1E44.9040408@cybertec.at>
References: <62364058618.20040201153901@wenet.ru>
	<401D1E44.9040408@cybertec.at>

Hello Hans-Jьrgen,

Sunday, February 1, 2004, 6:41:56 PM, you wrote:


HJS> If I understand you question correctly you have to use a SERIALIZABLE 
HJS> transaction to make sure that you can see the same snapshot during your 
HJS> transaction.

My problem is same as:
[quote from: http://www.postgresql.org/docs/7.2/interactive/xact-serializable.html]
-----------------------------------------------------------------------------------
I don't think serializable transactions quite give you this.

Consider the following transaction:

BEGIN
SELECT count(*) FROM bottles WHERE wall = 3; [*]
[if count < 10]
INSERT INTO bottles (wall, colour) VALUES (3, 'green');
[end if]
END

If this were run several times sequentially, it could never increase the number of
bottles on wall 3 so that there were more than 10. But if it ran several times
simultaneously, even with serializable transaction isolation, it could do so.
-----------------------------------------------------------------------------------

Which methods are fix this trouble? Desirable not slow methods...

Very important detail:
SELECT query, marked by [*] is executed only from one function, for
example from
function bottle_add() {
  BEGIN
  SELECT count(*) FROM bottles WHERE wall = 3; [*]
  [if count < 10]
    INSERT INTO bottles (wall, colour) VALUES (3, 'green');
  [end if]
  END
}

but in all other (many many) functions exists selects from 'battles'
table too, and locking _all_ rows in table 'battles' for read/write
are disagree. May be i need to do locking not row's, but queries?
Locking on application layer? Help plz.

Sorry for bad english ;(

-- 
Best regards,
 Alexander                            mailto:voodoo@wenet.ru




view thread (5+ messages)  latest in thread

Message-ID: <106387628840.20040201221154@wenet.ru>
Permalink:  ../106387628840.20040201221154@wenet.ru/
Also on:    postgresql.org/message-id/106387628840.20040201221154@wenet.ru

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: voodoo@wenet.ru, postgres@cybertec.at
  Subject: Re: LOCKing method in my situation?
  In-Reply-To: <106387628840.20040201221154@wenet.ru>
  References: <62364058618.20040201153901@wenet.ru> <401D1E44.9040408@cybertec.at> <106387628840.20040201221154@wenet.ru>

* 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