pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
LOCKing 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>
  2004-02-01 15:41 ` Re: LOCKing method in my situation? Hans-Jürgen Schönig <postgres@cybertec.at>
  2004-02-01 19:58 ` Re: LOCKing method in my situation? Paul & Natalie T <pntil@shentel.net>
  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 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   ` Re: LOCKing method in my situation? 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 12:39 LOCKing method in my situation? Alexander Popkov <voodoo@wenet.ru>
  2004-02-01 15:41 ` Re: LOCKing method in my situation? Hans-Jürgen Schönig <postgres@cybertec.at>
@ 2004-02-01 19:11   ` Alexander Popkov <voodoo@wenet.ru>
  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 12:39 LOCKing method in my situation? Alexander Popkov <voodoo@wenet.ru>
@ 2004-02-01 19:58 ` Paul & Natalie T <pntil@shentel.net>
  2004-02-01 21:14   ` Re: LOCKing method in my situation? 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 12:39 LOCKing method in my situation? Alexander Popkov <voodoo@wenet.ru>
  2004-02-01 19:58 ` Re: LOCKing method in my situation? Paul & Natalie T <pntil@shentel.net>
@ 2004-02-01 21:14   ` Alexander Popkov <voodoo@wenet.ru>
  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