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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
