Should I use SQL transactions when reading records?

Question

SQL transactions are used to insert, update, but should they be used to read records?

Answer 1

If you request all records in one request and pull them back at a time, there is no need. Everything is enclosed in an implicit transaction. That is, even if you return a million records, and even if other processes change records, you will see how all millions of records looked at the same time.

The only time you really need a transaction (and often a specific hint of lock) in a read-only process:
- You read the "piece of food" notes and do not need anything else to change the meanings while you repeat. [For example, a related recordset in ADO, which you then cursor.]
- You read some data, do some calculations, then read some related data, but, on the assumption, nothing has changed on average.

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

Answer 2

Transactional packaging is not required for clean readings.

In your SQL statement, Lock Hints should take care of returning the correct data to you ( http://msdn.microsoft.com/en-us/library/aa213026%28SQL.80%29.aspx ).

At the server level, you can set transaction isolation levels ( http://msdn.microsoft.com/en-us/library/ms173763.aspx ).

Edit

Explanation of Net Readings

If all of your SQL statements have this kind of read, you don’t need to transfer the transaction

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

If you read results that may be affected by other transactions in parallel, you should wrap the transaction. For example,

 BEGIN TRANSACTION INSERT INTO AccountTransactions (Type, Amount) Values ('Credit', 43.21) UPDATE AccountSummary SET Balance = Balance + 43.21 SELECT @Balance = Balance FROM AccountSummary COMMIT TRANSACTION 

Indeed, you simply return the balance, but the entire money transaction should work in two places.

Answer 3

If you need the latest millisecond information, you can use a transaction built using TransactionOptions with IsolationLevel of Serializable .

This will affect performance, as it will lock the table (or parts of the table), so you need to find out if you really need it.

For most purposes, if you are reading, you do not need to wrap the transaction around it (if you are only reading in one operation).

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

For example, if you are reading and depending on the results you are writing or updating, but it is important that the data you just read is current, you probably should combine all the logic in one transaction.

Answer 4

No, transactions are usually not needed to read data, and this will slow down the reading of your data.

I would advise you to familiarize yourself with the term ATOMIC. This will help you understand what transactions are for.

Answer 5

Is it possible to make transactions, but what is the purpose?

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

This is the syntax from SQL Server Books Online:

 SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE } 

Lock in Microsoft SQL Server .

Answer 6

When you change something in a transaction, you can use the read statement to check if the action takes effect shortly before committing.

Answer 7

Transactions are designed to avoid concurrency problems when a single logical transaction actually matches multiple SQL queries. For example, for a bank account, if you transfer money from one account to another, you first subtract the amount from the account, and then add it to another (or vice versa). But, if any error occurs between your database, it will be in an invalid state (you could subtract the amount from one account, but not add it to another). So, if you read all your data in one request, you do not need a transaction.