Should there be a Transaction for Read Queries?

sql-server transactions crud
Kyle West picture Kyle West · Nov 21, 2008 · Viewed 13.6k times · Source

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?

Answer

Greg picture Greg · Nov 21, 2008

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.

Related questions

Correct use of transactions in SQL Server

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly. What's the problem with the following script? BEGIN TRANSACTION […

Read More
SQL Server - transactions roll back on error?

We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; It …

Read More
SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file

I'm having a similar issue to The current transaction cannot be committed and cannot support operations that write to the log file, but I have a follow-up question. The answer there references Using TRY...CATCH in Transact-SQL, which I'll come …

Read More