Should there be a Transaction for Read Queries?

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 ;)

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.

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