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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: LOCKing method in my situation?

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: LOCKing method in my situation?

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 SERIALIZABL=
E=20
HJS> transaction to make sure that you can see the same snapshot during y=
our=20
HJS> transaction.

My problem is same as:
[quote from: http://www.postgresql.org/docs/7.2/interactive/xact-serializ =
able.html]
------------------------------------------------------------ -------------=
----------
I don't think serializable transactions quite give you this.

Consider the following transaction:

BEGIN
SELECT count(*) FROM bottles WHERE wall =3D 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 =3D 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 ;(

--=20
Best regards,
Alexander mailto:voodoo@wenet.ru


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if you=
r
joining column's datatypes do not match

Re: LOCKing method in my situation?

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

You want to obtain a ROW EXCLUSIVE lock, e.g.,

LOCK TABLE