Should there be a Transaction for Read Queries?

Question

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 1

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.

Answer 2

I would say that one of the main purposes of a transaction is to offer rollback potential if there are any problems- which is defunct when simply reading.

Answer 3

I have found that 'transactions' behave very differently on different SQL servers. In some cases, starting a transaction locks all other connections from being able to execute any SQL until the transaction is committed or rolled back (MS SQLServer 6.5). Others don't have any problems, and only lock when there is a modification (oracle). The locks can even expand to encompass only your changes - cell locks / row locks / page locks / table locks.

Typically I use transactions only when data-integrity between multiple insert / delete / update statements must be maintained. Even still, I prefer to implement this using DB-defined cascading deletes so that the database does it automatically and atomically.

Use a transaction if you can foresee a situation where you would want to rollback multiple modifications, but otherwise, the database will do it's atomic updates without the extra code to deal with it.

Answer 4

I've been checking this out the past few minutes, since it's something I should know more about. Here's what I've found.

Transactions would be useful around a select if you want to lock that row while a person is reading records and don't want it to modified or read. For example run these queries:

(in query window 1)

BEGIN TRAN SELECT * FROM MYTABLE WITH (ROWLOCK XLOCK) WHERE ID = 1

(in query window 2)

SELECT * FROM MYTABLE WHERE ID = 1

(query window 2 will not return results until you run this in window 1)

COMMIT TRAN

Useful links:

http://msdn.microsoft.com/en-us/library/aa213039.aspx

http://msdn.microsoft.com/en-us/library/aa213026.aspx

http://msdn.microsoft.com/en-us/library/ms190345.aspx

My goal was to get something to block - and it finally worked after adding the XLOCK in there. Simply using ROWLOCK was not working. I'm assuming it was issuing a shared lock(and the data had been read)..but I'm still exploring this.

Adding - WITH (UPDLOCK ROWLOCK) - will let you select and lock the rows to updates, which would help with concurrency.

Be careful with table hints. If you start applying them haphazardly, your system will slow to a crawl if you get even a small number of users on your app. That is the one thing I knew before looking into this ;)

Answer 5

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