Menu
I've been reading that some devs/dbas recommend using transactions in all database calls, even read-only calls. While I understand inserting/updating within a transaction what is the benefit of reading within a transaction?
927
The "Q" in SQL stands for "Query": All SQL is a "query" even if it doesn't return anything (eg a create table query) - search for DDL, DML and DCL). All queries are run within a transaction.
You use transactions when you have a group of actions that must be atomic (either all succeed or none succeed) Wrapping these actions in a transaction allows you to rollback actions that have already succeeded when you encounter an error.
The answer is that you don't. Starting a transaction to perform a read-only operation adds to the overhead of the processing thread and can cause shared read locks on the database (depending on what type of database you are using and what the isolation level is set to).
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.
So you get a consistent view of the database. Imagine you have two tables that link to each other, but for some reason you do 2 selects... in pseuodocode:
myRows = query(SELECT * FROM A) moreRows = query(SELECT * FROM B WHERE a_id IN myRows[id]) If between the two queries, someone changes B to delete some rows, you're going to have a problem.
164
Similar to what RoBorg said, you'd do SELECTS w/i transactions to prevent the reading of phantom data between statements. BUT it's important to note that the default transaction isolation level in SQL Server is READ COMMITTED which will only prevents dirty reads; to prevent phantom data you'd have to use at least REPEATABLE READ. "Use this option only when necessary."
http://msdn.microsoft.com/en-us/library/ms173763.aspx
37
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
