SQL transactions is used for insert, update, but should it be used for reading records?

+3  A: 

Transaction wrapping is not needed for pure reads.

Within your SQL statement, Lock Hints should take care returning proper data to you (

On a server level, you can set Transaction Isolation levels (


Explaining pure reads

If all your SQL statement has these kinds of reads then you do not need to wrap in a transaction

SELECT Col1, Col2
From Table1
    INNER JOIN Table2
        ON Table1.Id = Table2.Table1Id

If you are reading results that can be affected by other transactions in parallel then you must wrap in a transaction. For eg:


INSERT INTO AccountTransactions (Type, Amount) Values ('Credit', 43.21)
UPDATE AccountSummary SET Balance = Balance + 43.21

SELECT @Balance = Balance FROM AccountSummary


Really, you are just returning the balance, but the entire monetary transaction has to work in two places.

Raj More
In the following example, any concurrent process could interfere with the tables you are reading. There is no writing of data, but there are calculations between related reads. As such a TRANSACTION would be need to protect the state of the data. INSERT INTO @temp SELECT <blah>; <SOME TSQL>; SELECT <blah>;
That's not a pure read though. Raj said "Transaction wrapping is not needed for pure reads."
Daniel Dyson
Define "pure read", the example I gave does no inserts, updates or deletes to anything other than a variable. [It could be an INT, a table variable, or some other data container to facilitate the calculations that will then define the behaviour of the final read]
You are modifying the structure calls @temp. That's an INSERT STATEMENT, not a pure read, especially if your @temp is used in the `SELET <blah>` statement that follows.
Raj More
But from a black box perspective nothing in the database has changed. For example, temp variables can be used in User Defined Functions. By your definition of "Pure Read" you can't even use INT variables, and as such I would contend you are limitted to only a single SQL statement. I would say that "Pure Read" actually describes a process that causes no (temporary or permanent) changes to the database or it's contents, be they visible or not to any other process. In my mind, requiring that not even variables are used describes an overly trivial scenario.
Your example of DELETE FROM <table> WHERE <field> IN (SUBQUERY) is still bound within an implicit transaction as it constitutes a single SQL statement. Bounding this in a transaction would have no effect.
@Dems, you are right in that there is no separation between the select and the delete. I will correct that.
Raj More
+2  A: 

If you need the most up to date to the millisecond information you can use a transaction that is constructed with a TransactionOptions having an IsolationLevel of Serializable.

This would effect performance as it will lock the table (or parts of the table), so you need to figure out if you really need this.

For most uses, if you are doing a read, you do not need to wrap a transaction around it (assuming you are only doing reads in the one operation).

It really depends on your application, what data it requires and how it uses it.

For example, if you do a read and depending on the results you do a write or update, but it is critical that the data you just read is current, you should probably wrap the whole logic into a single transaction.

+5  A: 

If you are querying all the records in a single query, and pulling them back in one go, there is no need. Everything is wrapped up in an implicit transaction. That is to say, even if you get back one million records, and even if other processes are changing the records, you'll see what all one million records looked like at the same point in time.

The only times you would really need a transaction (and, often, a specific locking hint) in a read only process are:
- You read the records "piece-meal" and need nothing else to alter the values while you itterate though. [Such as a connected recordset in ADO that you then cursor through.]
- You read some data, do some calculations, then read some related data, but on the assumption nothing changed in the mean time.

In short, you need transactions when you want other processes to be stopped from interfering with your data between SQL statements.

+1  A: 

No, transactions are not generally needed to read data and it will slow down your data reads as well.

I would suggest you read up on the term ATOMIC. This will help you understand what transactions are for.

Daniel Dyson
+1  A: 

It's posssible to to do transactions but what is purpose of it?

You can set the appropriate isolation level for an entire SQL Server session by using the SET TRANSACTION ISOLATION LEVEL statement.

This is the syntax from SQL Server Books Online:


Locking in Microsoft SQL Server.

Pranay Rana

When you modified something in a transaction, you can use read statement to check if the operation takes effect, just before you commit.


Transactions are meant to avoid concurrency issues when one logical transaction actually maps to several SQL queries. For example, for a bank account, if you are transferring money from one account to another, you will 1st subtract the amount from account and then add it to other(or vice versa). But, if some error occurs in between your database would be in a invalid state (You may have subtracted the amount from one account but not added it to other). So, if you are reading all your data in one query, you dont need a transaction.