Thread: Locking several tables within one transaction

There are two places from which my database can be accessed:
1) PHP code, which only read data from db and sends it to users' browsers;
2) C++ code, which writes data to db one time per 15 minutes (one huge
transaction which affects all the tables in db);
Both pieces of code use local socket to access to Postgres db, more
over, they both use completely the same connection string (same
username etc).

Goal is: during C++ code's transaction (duration is up to ~20 seconds)
PHP code should not read ANY data from db. In other words, C++ code
must have exclusive access.
The solution I've found for a while (SQL commands, which C++ code should call):
====
BEGIN;
LOCK TABLE reports IN ACCESS EXCLUSIVE MODE;
LOCK TABLE region_reports IN ACCESS EXCLUSIVE MODE;
-- locking all the other tables here
INSERT INTO reports (user_id, data) VALUES ($1::integer, $2:varchar);
--now I get serial value 'id' from previous INSERT and use it as $1 below
INSERT INTO region_reports (report_id, data) VALUES ($1::integer, $2:varchar);
--inserting into all the other tables here
COMMIT;
====
So, my question is: is there guarantee no data will be read from
region_reports table by PHP code between two 'LOCK TABLE' commands
shown (i.e. before 'LOCK TABLE region_reports' command)?
In other words: is there guarantee all the LOCK TABLE commands will be
executed simultaneously (i.e. no other commands will be executed
between them)?

Actually, it is not so nice way to lock all the tables manually, so
using single pg_advisory_lock() would be better solution, but if I
understand correctly, advisory lock works within single session, but
two places from which my database can be accessed will use different
session. Am I right?
Is there more laconic solution for my goal?

Thank you very much.
Ilia Lilov.

On Fri, Jul 22, 2011 at 9:45 AM, Ilia Lilov <lilovil@gmail.com> wrote:
> There are two places from which my database can be accessed:
> 1) PHP code, which only read data from db and sends it to users' browsers;
> 2) C++ code, which writes data to db one time per 15 minutes (one huge
> transaction which affects all the tables in db);
> Both pieces of code use local socket to access to Postgres db, more
> over, they both use completely the same connection string (same
> username etc).
>
> Goal is: during C++ code's transaction (duration is up to ~20 seconds)
> PHP code should not read ANY data from db. In other words, C++ code
> must have exclusive access.
> The solution I've found for a while (SQL commands, which C++ code should call):
> ====
> BEGIN;
> LOCK TABLE reports IN ACCESS EXCLUSIVE MODE;
> LOCK TABLE region_reports IN ACCESS EXCLUSIVE MODE;
> -- locking all the other tables here
> INSERT INTO reports (user_id, data) VALUES ($1::integer, $2:varchar);
> --now I get serial value 'id' from previous INSERT and use it as $1 below
> INSERT INTO region_reports (report_id, data) VALUES ($1::integer, $2:varchar);
> --inserting into all the other tables here
> COMMIT;
> ====
> So, my question is: is there guarantee no data will be read from
> region_reports table by PHP code between two 'LOCK TABLE' commands
> shown (i.e. before 'LOCK TABLE region_reports' command)?
> In other words: is there guarantee all the LOCK TABLE commands will be
> executed simultaneously (i.e. no other commands will be executed
> between them)?

No, they are executed one after the other.  It's possible for another
connection to access the second table right before it's locked.

Is it possible that running ALL your transactions in serializable mode
would be a solution?  I think we need a better explanation of what
your business logic / case is here.

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.