Menu
SQL transactions is used for insert, update, but should it be used for reading records?
821
For any business, transactions that may be comprised of many individual operations and even other transactions, play a key role. Transactions are essential for maintaining data integrity, both for multiple related operations and when multiple users that update the database concurrently.
SQL Transaction gives you the “power to return back to a safe state if some error happens in the middle of your SQL Code”. For example, suppose in your Stored Procedure you are running an Insert statement followed by Update statement.
Transactions should be used when there is the possibility that either failure to complete or someone else reading or writing in the middle of your task could cause damage to the data. These include but are not limited to: Reading from a table for subsequent deletion. Writing related data to multiple tables.
In a highly concurrent application it could (theoretically) happen that data you've read in the first select is modified before the other selects are executed. If that is a situation that could occur in your application you should use a transaction to wrap your selects.
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.
91
Transaction wrapping is not needed for pure reads.
Within your SQL statement, Lock Hints should take care returning proper data to you (http://msdn.microsoft.com/en-us/library/aa213026%28SQL.80%29.aspx).
On a server level, you can set Transaction Isolation levels (http://msdn.microsoft.com/en-us/library/ms173763.aspx).
Edit
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:
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
Really, you are just returning the balance, but the entire monetary transaction has to work in two places.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
