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.

Gary 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