pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedLOCKing method in my situation?
5+ messages / 3 participants
[nested] [flat]
* LOCKing method in my situation?
@ 2004-02-01 12:39 Alexander Popkov <voodoo@wenet.ru>
0 siblings, 2 replies; 5+ messages in thread
From: Alexander Popkov @ 2004-02-01 12:39 UTC (permalink / raw)
To: 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@wenet.ru
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LOCKing method in my situation?
@ 2004-02-01 15:41 Hans-Jürgen Schönig <postgres@cybertec.at>
parent: Alexander Popkov <voodoo@wenet.ru>
1 sibling, 1 reply; 5+ messages in thread
From: Hans-Jürgen Schönig @ 2004-02-01 15:41 UTC (permalink / raw)
To: Alexander Popkov <voodoo@wenet.ru>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LOCKing method in my situation?
@ 2004-02-01 19:11 Alexander Popkov <voodoo@wenet.ru>
parent: Hans-Jürgen Schönig <postgres@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Alexander Popkov @ 2004-02-01 19:11 UTC (permalink / raw)
To: pgsql-php@postgresql.org, Hans-Jürgen Schönig <postgres@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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LOCKing method in my situation?
@ 2004-02-01 19:58 Paul & Natalie T <pntil@shentel.net>
parent: Alexander Popkov <voodoo@wenet.ru>
1 sibling, 1 reply; 5+ messages in thread
From: Paul & Natalie T @ 2004-02-01 19:58 UTC (permalink / raw)
To: 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.
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LOCKing method in my situation?
@ 2004-02-01 21:14 Alexander Popkov <voodoo@wenet.ru>
parent: Paul & Natalie T <pntil@shentel.net>
0 siblings, 0 replies; 5+ messages in thread
From: Alexander Popkov @ 2004-02-01 21:14 UTC (permalink / raw)
To: pgsql-php@postgresql.org, Paul & Natalie T <pntil@shentel.net>
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@wenet.ru
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-02-01 21:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-01 12:39 LOCKing method in my situation? Alexander Popkov <voodoo@wenet.ru>
2004-02-01 15:41 ` Hans-Jürgen Schönig <postgres@cybertec.at>
2004-02-01 19:11 ` Alexander Popkov <voodoo@wenet.ru>
2004-02-01 19:58 ` Paul & Natalie T <pntil@shentel.net>
2004-02-01 21:14 ` Alexander Popkov <voodoo@wenet.ru>
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