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.
