#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0

    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:

    Code:
    psql -h localhost -p 5432 -U postgres -1 -c "$SQL1; $SQL2; $SQL3;" mydb
    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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Thank you, shammat. I have updated the psql command to:

    Code:
    psql -h localhost -p 5432 -U postgres -1 -c "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRANSACTION; $SQL1; $SQL2; $SQL3; END TRANSACTION" mydb
    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!
    Last edited by littleK; August 26th, 2012 at 03:37 PM. Reason: Additional Question
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by littleK
    Will the isolation level "end" after the "END TRANSACTION"?
    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!

    Comments on this post

    • littleK agrees
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Ah, got it. Thanks for your help!

IMN logo majestic logo threadwatch logo seochat tools logo