Menu
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.
328
If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. Even selects or DDL statements have their own implicit transaction and so they have autocommit to. But usually a select has nothing to commit.
1 Answer. Show activity on this post. That's why transactions are unnecessary for sql-server functions. However, you can change transaction isolation level, for example, you may use NOLOCK hint to reach "read uncommitted" transaction isolation level and read uncommitted data from other transactions.
You use transactions when the set of database operations you are making needs to be atomic. That is - they all need to succeed or fail. Nothing in between. Transactions are to be used to ensure that the database is always in a consistent state.
The following are some frequent scenarios where use of transactions is recommended: In batch processing, where multiple rows must be inserted, updated, or deleted as a single unit. Whenever a change to one table requires that other tables be kept consistent. When modifying data in two or more databases concurrently.
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.
192
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.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
