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?

Manhunt

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.

Keratose

This is not always the case. Your client-side implementation may take a full snapshot of the table and show the expected results. ADO.NET allows for offline queries. – Multilateral

Actually it depends on a lit of things, including the configuration of your server. The question asked why you would put a SELECT inside a transaction... that's the reason why. – Keratose

+1 this very clear explanation. Yes, it depends on other things (when using Hibernate or NHibernate for example, objects are cached after reading) but isolation is one of the four principles of transactions and shouldn't be forgotten. – Delphinedelphinia

Answer 2

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

Turkmen

Answer 3

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

Schmidt

Answer 4

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.

Michiko

Answer 5

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.

Multilateral

Answer 6

Another good reason holding multiple transaction for read and for insert is the case you wish to insert record base on the data that you get from select query and you also want to commit every X row inserted.

Two transaction:

1. for the read\select.
2. for the insert and commit every X row.

will let you do this correctly while with one transaction not separate the read and write will not let you do the commit without losing the reader.

Cyr

Answer 7

I recommend you to read about the snapshot isolation as an example of the transaction usefullness for reading data.

Also, here is an excerpt from the Designing Data-Intensive Applications, by Martin Kleppmann book, which points out two cases when it is important to have some transaction like behavior even when you are only reading data from the database:

Backups
Taking a backup requires making a copy of the entire database, which may take hours on a large database. During the time that the backup process is running, writes will continue to be made to the database. Thus, you could end up with some parts of the backup containing an older version of the data, and other parts containing a newer version. If you need to restore from such a backup, the inconsistencies (such as disappearing money) become permanent.

Analytic queries and integrity checks
Sometimes, you may want to run a query that scans over large parts of the data‐ base. Such queries are common in analytics (see “Transaction Processing or Ana‐ lytics?” on page 90), or may be part of a periodic integrity check that everything is in order (monitoring for data corruption). These queries are likely to return nonsensical results if they observe parts of the database at different points in time.

Kessel