Re: Locking several tables within one transaction

Thank you, Scott. I wondered about it, actually.

I've found perfect (I believe) solution. It uses pg_advisory_lock() as
I guessed before, but I thought wrong about it. For some reason I
thought session is some kind of scope for advisory lock, so one
program can't see locking made by another program from another
session, but it isn't true of course. Advisory locks are global, but
when session ends, all the locks made during the session unlocks. So,
it is exactly what I need.

In hope it will be useful for someone, I post my solution here.
We need two advisory locks: first one to "notify" all the programs
which read data not to start reading; and second one to ensure there
is no such programs reading data at the moment.
So, SQL code for the program which needs to have exclusive access for
long enough operations (C++ code in my example):
====
--Connect database
SELECT pg_advisory_lock(1);
SELECT pg_advisory_lock(2);
--All the SQL commands to operate (mostly to write) with data (they
don't need to be within single transaction)
SELECT pg_advisory_unlock(2);
SELECT pg_advisory_unlock(1);
--Disconnect database
====

And SQL code for programs which need not exclusive access and may
access to db at the same time (PHP code in my example):
====
--Connect database
SELECT pg_advisory_lock_shared(1);
SELECT pg_advisory_unlock_shared(1);
SELECT pg_advisory_lock_shared(2);
--All the SQL commands to operate (mostly to read) with data (they
don't need to be within single transaction)
SELECT pg_advisory_unlock_shared(2);
--Disconnect database
====

If anyone needs further explanation, fell free to mail me.
Ilia Lilov.