Lists: | pgsql-php |
---|
From: | Alexander Popkov <voodoo(at)wenet(dot)ru> |
---|---|
To: | pgsql-php(at)postgresql(dot)org |
Subject: | LOCKing method in my situation? |
Date: | 2004-02-01 12:39:01 |
Message-ID: | 62364058618.20040201153901@wenet.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-php |
Hello PostgreSQL gurus!
I am have question to you.
I am have table 'chall_item' in my database.
In many PHP scripts i am do 'SELECT * FROM chall_item....', but
in one script (join.php) i do
a) SELECT COUNT(*) FROM chall_item WHERE chall_no = XXX;
b) if returned number less then 10, then i do INSERT INTO chall_item (chall_no, ...) VALUES (XXX, ...);
But if beetwen a) and b) other instance of 'join.php' do part "a)" - then odd row are added ;(
I can do LOCK TABLE chall_item IN ACCESS EXCLUSIVE MODE,
but IMHO this is not true method in this situation, because i need lock only for 'join.php'
not for all scripts. Help me plz. Which other methods are exist in this situation.
--
Best regards,
Alexander mailto:voodoo(at)wenet(dot)ru
From: | Hans-Jürgen Schönig <postgres(at)cybertec(dot)at> |
---|---|
To: | Alexander Popkov <voodoo(at)wenet(dot)ru> |
Cc: | pgsql-php(at)postgresql(dot)org |
Subject: | Re: LOCKing method in my situation? |
Date: | 2004-02-01 15:41:56 |
Message-ID: | 401D1E44.9040408@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-php |
Alexander Popkov wrote:
> Hello PostgreSQL gurus!
>
> I am have question to you.
>
> I am have table 'chall_item' in my database.
> In many PHP scripts i am do 'SELECT * FROM chall_item....', but
> in one script (join.php) i do
> a) SELECT COUNT(*) FROM chall_item WHERE chall_no = XXX;
> b) if returned number less then 10, then i do INSERT INTO chall_item (chall_no, ...) VALUES (XXX, ...);
>
> But if beetwen a) and b) other instance of 'join.php' do part "a)" - then odd row are added ;(
>
> I can do LOCK TABLE chall_item IN ACCESS EXCLUSIVE MODE,
> but IMHO this is not true method in this situation, because i need lock only for 'join.php'
> not for all scripts. Help me plz. Which other methods are exist in this situation.
>
If I understand you question correctly you have to use a SERIALIZABLE
transaction to make sure that you can see the same snapshot during your
transaction.
Regards,
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
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 |
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
From: | Paul & Natalie T <pntil(at)shentel(dot)net> |
---|---|
To: | pgsql-php(at)postgresql(dot)org |
Subject: | Re: LOCKing method in my situation? |
Date: | 2004-02-01 19:58:25 |
Message-ID: | 401D5A61.9000509@shentel.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-php |
Alexander:
It sounds like a lock preventing concurrent writes would be your best
bet. This would mean that only one instance of your script can be
*changing* the table at one time, but it has no effect on transactions
that only read from that.
If I read the docs correctly:
http://www.postgresql.org/docs/7.4/static/explicit-locking.html
You want to obtain a ROW EXCLUSIVE lock, e.g.,
LOCK TABLE <table name> IN ROW EXCLUSIVE;
Regards,
Paul Tillotson
Alexander Popkov wrote:
>Hello PostgreSQL gurus!
>
>I am have question to you.
>
>I am have table 'chall_item' in my database.
>In many PHP scripts i am do 'SELECT * FROM chall_item....', but
>in one script (join.php) i do
> a) SELECT COUNT(*) FROM chall_item WHERE chall_no = XXX;
> b) if returned number less then 10, then i do INSERT INTO chall_item (chall_no, ...) VALUES (XXX, ...);
>
>But if beetwen a) and b) other instance of 'join.php' do part "a)" - then odd row are added ;(
>
>I can do LOCK TABLE chall_item IN ACCESS EXCLUSIVE MODE,
>but IMHO this is not true method in this situation, because i need lock only for 'join.php'
>not for all scripts. Help me plz. Which other methods are exist in this situation.
>
>
>
From: | Alexander Popkov <voodoo(at)wenet(dot)ru> |
---|---|
To: | pgsql-php(at)postgresql(dot)org, Paul & Natalie T <pntil(at)shentel(dot)net> |
Subject: | Re: LOCKing method in my situation? |
Date: | 2004-02-01 21:14:23 |
Message-ID: | 55394977757.20040202001423@wenet.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-php |
Hello Paul,
Sunday, February 1, 2004, 10:58:25 PM, you wrote:
PNT> Alexander:
PNT> It sounds like a lock preventing concurrent writes would be your best
PNT> bet. This would mean that only one instance of your script can be
PNT> *changing* the table at one time, but it has no effect on transactions
PNT> that only read from that.
PNT> If I read the docs correctly:
PNT> http://www.postgresql.org/docs/7.4/static/explicit-locking.html
PNT> You want to obtain a ROW EXCLUSIVE lock, e.g.,
PNT> LOCK TABLE <table name> IN ROW EXCLUSIVE;
Thanks! I am read this doc carefully. I am understand what i need:
i need _any_ lock method, which conflicts along, but not conflict with
ROW SHARE MODE (for working concurrent selects). I am choose
IN SHARE UPDATE EXCLUSIVE MODE and test it on 3 pgsql consoles - it
worked ;)
Big thanks!
--
Best regards,
Alexander mailto:voodoo(at)wenet(dot)ru