Question about using Transactions in ADO.NET

In one of our Data Access Layers, it was suggested that we use transactions around all commands that are stored procedure types. Most of our stored procedures are simple (SELECT * FROM <sometable> ) My question is: Do we really need a "begin transaction" ... "end transaction" for a simple select What is the default isolation level if no transactions are specified

Thanks in advance.

Answer this question

Question about using Transactions in ADO.NET

Cha Chunchadatharn
(casperOne@discussions.microsoft.com) writes:
> Also, the comment that you do not need transactions around select
> statements is only partially correct. If you have a single select
> statement (or insert, or delete, or update, for that matter), then that
> in itself is considered a transaction by the server, and you don't have
> to worry about anything. However, if you perform more than one
> operation (selects included), then you need to wrap a transaction around
> it, because it is perceivable that data involved in the selects could
> change while the select (or selects) are being performed.

Actually, this is somewhat pointless: BEGIN TRANSACTION SELECT ... FROM tbl1 WHERE ... SELECT ... FROM tbl2 WHERE ... SELECT ... FROM tbl1 WHERE ... COMMIT TRANSACTION Assuming the default isolation level of READ COMMITTED is active.

In this isolation level, locks are released as soon as a row has been read, and reading the same table twice may yield different results, even if you are in the same transaction.

However, if you change the transaction isolation level to any of SNAPSHOT, REPEATABLE READ and SERIALIZABLE, the transaction is indeed meaningful. In case of SNAPSHOT - added in SQL 2005, beware that the database must be configured to permit SNAPSHOT - you read all data as they were at the time the transaction started.

The other rwo takes out locks to ensure that once you've read a row, this row will not change until you commit. The difference between the two is that SERIALIZABLE also precluts inserts of new rows in read ranges.

-- Erland Sommarskog, SQL Server MVP,
esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Mordy

JayC202 wrote:
You were given bad advice. Plus what you just described is not really ADO.NET transactions, but rather letting your stored procedure handle the transactions. A select stored procedure should not use a transaction. It's entirely pointless, when you commit the transaction you will not commit anything (since nothing changed) and therefore you will only incur the overhead of establishing the transaction.

transactions are simply used to maintain valid data. So that if you change one table and another table depends on it you will not update one table without updating the other. If the second update fails the first update would be rolled back to it's original state. If both are successful then you can commit both together and ensure valid data.

So in other words, you only need transactiosn when doing inserts/updates/deletes and only whne performing multiple operations.

Jay and Bill,

   This isn't exactly the best advise either. Generally speaking, handling the transaction state in a stored procedure is not a good idea. The reason for this is that applications, as they expand, might have to include more resources in the transaction, which then requires the use of a transaction manager (like COM+, or the System.Transactions namespace in .NET 2.0). Because of this, you should wrap your transactions around groups of actions, not stored procedures themselves.

   Also, the comment that you do not need transactions around select statements is only partially correct. If you have a single select statement (or insert, or delete, or update, for that matter), then that in itself is considered a transaction by the server, and you don't have to worry about anything. However, if you perform more than one operation (selects included), then you need to wrap a transaction around it, because it is perceivable that data involved in the selects could change while the select (or selects) are being performed.

   However, as a general guideline, I would say even in single operations, wrap in a transaction, only because it will aid with maintinence later if those operations expand.

   Hope this helps.

          - Nicholas Paldino [.NET/C# MVP]
          - mvp@spam.guard.caspershouse.com

Limmer
You were given bad advice. Plus what you just described is not really ADO.NET transactions, but rather letting your stored procedure handle the transactions. A select stored procedure should not use a transaction. It's entirely pointless, when you commit the transaction you will not commit anything (since nothing changed) and therefore you will only incur the overhead of establishing the transaction.

transactions are simply used to maintain valid data. So that if you change one table and another table depends on it you will not update one table without updating the other. If the second update fails the first update would be rolled back to it's original state. If both are successful then you can commit both together and ensure valid data.

So in other words, you only need transactiosn when doing inserts/updates/deletes and only whne performing multiple operations.
Question about using Transactions in ADO.NET

Answer this question