Thread: LOCKing method in my situation?

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

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

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

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.
>
>
>

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