Re: LOCKing method in my situation?

From: Alexander Popkov <voodoo(at)wenet(dot)ru>
To: pgsql-php(at)postgresql(dot)org, Hans-Jrgen Schnig <postgres(at)cybertec(dot)at>
Subject: Re: LOCKing method in my situation?
Date: 2004-02-01 19:11:54
Message-ID: 106387628840.20040201221154@wenet.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-php

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(at)wenet(dot)ru

In response to

Browse pgsql-php by date

  From Date Subject
Next Message Paul & Natalie T 2004-02-01 19:58:25 Re: LOCKing method in my situation?
Previous Message Hans-Jürgen Schönig 2004-02-01 15:41:56 Re: LOCKing method in my situation?