Use transactions for select statements?

I don't use Stored procedures very often and was wondering if开发者_高级运维 it made sense to wrap my select queries in a transaction.

My procedure has three simple select queries, two of which use the returned value of the first.

---

In a highly concurrent application it could (theoretically) happen that data you've read in the first select is modified before the other selects are executed.

If that is a situation that could occur in your application you should use a transaction to wrap your selects. Make sure you pick the correct isolation level though, not all transaction types guarantee consistent reads.

Update : You may also find this article on concurrent update/insert solutions (aka upsert) interesting. It puts several common methods of upsert to the test to see what method actually guarantees data is not modified between a select and the next statement. The results are, well, shocking I'd say.

---

Transactions are usually used when you have CREATE, UPDATE or DELETE statements and you want to have the atomic behavior, that is, Either commit everything or commit nothing.

However, you could use a transaction for READ select statements to:
Make sure nobody else could update the table of interest while the bunch of your select query is executing.

Have a look at this msdn post.

---

Most databases run every single query in a transaction even if not specified it is implicitly wrapped. This includes select statements.

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

https://www.postgresql.org/docs/current/tutorial-transactions.html