TransactionScope and SQL Server Locks

Question

Here's my scenario:

 

I have a need to grab a bunch of data from a fairly active database, so I'm only doing SELECTs. This database is not under my control at all, it is the customer's database and it is critical that I do not interfere with their transactions. I have wrapped up a series of SELECT commands in a TransactionScope (I can't use my own Stored Procs, since it's not my database, therefore I'm filling a DataSet with hard-coded SELECT statements, one DataTable at a time). My TransactionScope is instantiated with TransactionScopeOptions.RequiresNew.  Initially, not fully understanding the consequences, I had used IsolationLevel.ReadCommitted. What I didn't know at the time that, but realize now after trying to do some research on this, was that I think this makes my Transaction actually use what it seems they call "shared locks" ... is this correct? I do not want to have any kind of locks on any of those database tables! Somehow we got ourselves into a deadlock (no idea why really) ... and that is not a good thing!

 

I probably should change the IsolationLevel, and we don't mind dirty reads (we are gathering this data every x number of seconds, it's configurable and it's currently every 30 seconds), so you can see why a dirty read wouldn't matter in this case. Initially I thought I should change my queries to use the NOLOCK table hint on some of the tables in the queries (or maybe all of them) ... but I've read conflicting things about NOLOCK (some say it's bad, some say not). So, I'm leaning towards using either IsolationLevel.ReadUncommitted or Isolation.Snapshot. But I'm still a bit confused about Snapshot.

 

My partner says that we don't even need to use TransactionScope at all ... and he's probably correct about that. We don't really care about the SELECTs being in a Transaction at all. I think I may have done that initially since we've wrapped all our other DataAccess (against our own databases) within TransactionScopes, but perhaps we don't need it for SELECTs against our customer's database. However, even if I don't use TransactionScope in my C# code, SQL Server wraps up each of my SELECT calls in an implicit transaction anyway ... and I think the default Isolation Level there is Serializable when not specified in the SqlConnection, so if I'm going to need to change it anyway, I might as well just do it within a TransactionScope. I think Serializable will use shared locks too, right?

 

Complicating all this is the fact that most of our other Transactions to our own databases get elevated to Distributed Transactions at some point. I'm pretty sure that this would NOT apply to the above Transaction to our customer's database, since I've made it RequiresNew, but I don't know for sure as it would be called in the middle of a distributed transaction.

 

Any advice?

 

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 1

Hi BonnieB,

>> I think the default Isolation Level there is Serializable when not specified in the SqlConnection.

READ COMMITTED is the default isolation level for the Microsoft SQL Server Database Engine. When you use TransactionScope, there is a type "TransactionOptions" specifing an isolation level, the default isolation level is set to "Serializable", so I think your understanding isn't very accurate.

 Here is a document about "Transaction Isolation level". I think you can use "SNAPSHOT" in your scenario: "SNAPSHOT transactions do not request locks when reading data."

When you want to adjust Transaction Isolaton levels by Ado.net, you can use the System.Data.SqlClient managed namespace can call the SqlConnection.BeginTransaction method and set the IsolationLevel option to Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.

Have a nice day.

---

Alan Chen[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Thank you for your reply Alan, but I think you missed my point. I think I probably just confused the issue when I speculated about data access without using TransactionScope, so forget I said that. ;0)

First, I am already using TransactionScope, so I do know how it works and how to set it up and I'm going to continue using it. I had initially set the IsolationLevel to ReadCommitted, which I now realize that I don't want to use  because of the locking it does which will interfere with the customer's own work on their own database.

I've looked more at Snapshot and it requires a change to the database to allow Snapshots. As it is not our database, we can't make that change and I doubt if our customer will allow it, as it does create a performance hit.

Bottom line -- we decided to go ahead and use IsolationLevel.ReadUncommitted in our TransactionScope. As I mentioned in my OP, the dirty reads that result from that are ok in our scenario. We are gathering data to send to another app that displays that data on a map, updated every 30 seconds. Dirty doesn't really matter ...

My other question, about the Distributed Transactions as also been solved:

Complicating all this is the fact that most of our other Transactions to our own databases get elevated to Distributed Transactions at some point. I'm pretty sure that this would NOT apply to the above Transaction to our customer's database, since I've made it RequiresNew, but I don't know for sure as it would be called in the middle of a distributed transaction.

I did some testing yesterday and determined that the new Transaction did not enlist in the existing Distributed Transaction, which is what I hoped would be the case. So, all is good!

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com