X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BBD49D1D3DC for ; Sun, 1 Feb 2004 19:13:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 75231-02 for ; Sun, 1 Feb 2004 15:13:56 -0400 (AST) Received: from icfed.net (relay.icfed.net [62.105.156.139]) by svr1.postgresql.org (Postfix) with ESMTP id 80F05D1BB95 for ; Sun, 1 Feb 2004 15:13:53 -0400 (AST) Received: from [10.0.20.160] (account voodoo@wenet.ru HELO voodoo-book.mshome.net) by icfed.net (CommuniGate Pro SMTP 4.1.8) with ESMTP id 34105; Sun, 01 Feb 2004 22:13:52 +0300 Date: Sun, 1 Feb 2004 22:11:54 +0300 From: Alexander Popkov X-Mailer: The Bat! (v1.62e) Reply-To: Alexander Popkov Organization: MCFR X-Priority: 3 (Normal) Message-ID: <106387628840.20040201221154@wenet.ru> To: pgsql-php@postgresql.org, Hans-Jürgen Schönig Subject: Re: LOCKing method in my situation? In-Reply-To: <401D1E44.9040408@cybertec.at> References: <62364058618.20040201153901@wenet.ru> <401D1E44.9040408@cybertec.at> MIME-Version: 1.0 Content-Type: text/plain; charset=Windows-1251 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char FC hex) in message header 'To' To: ...php@postgresql.org,\n\tHans-J\374rgen Sch\366nig 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