Re: Locking several tables within one transaction

On Fri, Jul 22, 2011 at 9:45 AM, Ilia Lilov <lilovil(at)gmail(dot)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.