Question about transactions and concurrent access

Hello,

I have a question about transactions. I am creating a shell script that will use psql to execute 8 queries that will SELECT data from a database and INSERT the result of the SELECT into a temporary table.

My concern is concurrent access to the same tables while the script is running. While this is unlikely scenario in my case, I'd prefer to be safe about it.

Originally, I had the script executing each of the 8 queries separately, one after another. Then, I modified the psql command to execute all of the queries, as one transaction. For example:



However, it seems from the Postgres documentation that such a transaction will not guarantee a single snapshot of the database/tables while the query is executing. In other words, concurrent access could still affect the results.

Therefore, I don't really know where to proceed from here. What's the recommended option to ensure that concurrent access does not interfere with my 8 queries, every time the script is executed?

Thanks!

You need to make sure everything is run in a single transaction. Either by turning off autocommit and running all scripts with a single call or by putting everything into a single script that encloses everything in a BEGIN TRANSACTION ... END TRANSACTION block.

Before starting the transaction set the transaction to REPEATABLE READ and you should be fine.

Thank you, shammat. I have updated the psql command to:



Will the isolation level "end" after the "END TRANSACTION"? What if there was an error? Would I then need to perform an explicit ROLLBACK, or will that happen automatically?

Just out of curiosity, are there any disadvantages to using SERIALIZABLE instead of REPEATABLE READ?

Thanks!

Please see the manual. It's all explained there:

http://www.postgresql.org/docs/current/static/sql-set-transaction.html

You will also need to put the SET TRANSACTION after the BEGIN TRANSACTION!

Ah, got it. Thanks for your help!