Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am no db expert, but I was very surprised when someone working at our client's company asked us to send all queries wrapped in a transaction like below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION;
    SELECT * FROM <table_name> WHERE ..etc
COMMIT TRANSACTION;

I have never had such a request before when all we're doing is reading data (random ad-hoc queries). Is there any sense in this? Can anyone tell me why this would be necessary?

Many thanks

share|improve this question
    
As readers block writers in SQL Server, a read uncomitted transaction will avoid the obnoxious (b)locking for those queries (it's essentially the same as using with nolock) –  a_horse_with_no_name Aug 6 '13 at 8:04
    
That makes sense, I suppose. I didn't realize that READ UNCOMMITTED wasn't the default. Thanks. –  Matt Aug 6 '13 at 8:19

2 Answers 2

The BEGIN TRANSACTION/COMMIT TRANSACTION statements are mostly superfluous in this specific case.

As for running all queries with READ UNCOMMITTED isolation level, I'd strongly advise against it, unless you have a specific need, and aren't worried about the implications of dirty reads:

  • You may end up querying data from a half-finished transaction (e.g. only one half of a double-entry accounting ledger entry, only some of an order's line items, etc.)
  • You may end up querying data from a transaction that is eventually rolled back.

If you're concerned about write queries blocking read queries, I would recommend either using SNAPSHOT isolation, or setting the READ_COMMITTED_SNAPSHOT database option, which effectively uses snapshot isolation for all queries. The net result is that instead of being blocked, read queries will see data as it existed prior to any currently running write transactions.

Be advised that this option increases tempdb usage, as that's where the snapshot data is stored (and it has to be stored for all write transactions in databases where it's enabled). If this overhead is unacceptable, then I would only use READ UNCOMMITTED after evaluating queries that need it on a case-by-case basis.

I know you aren't the one making the decision to use it, but you can use these points as ammo if you need to push back on it. :)

share|improve this answer
    
Just for clarity, SNAPSHOT isolation (transaction-level snapshot isolation) is not the same thing as READ_COMMITTED_SNAPSHOT (statement-level snapshot isolation). See this article on MSDN for further reading. –  Jon Seigel Aug 6 '13 at 16:46
    
Right, there are some subtle differences, but generally speaking, both work to prevent writers from blocking readers. Make sure you understand what they do before using them. –  db2 Aug 6 '13 at 17:20
    
I have had one and only one occasion in my career where I felt there was a need for READ UNCOMMITTED (which is what the above code is essentially doing). It was a database server with many inserts a second coming in from monitoring devices in a chemical factory. The frequent writes were blocking a reporting process. Setting the reporting process to READ UNCOMMITTED meant it ignored the write locks. Since the report aggregated a week of data, having dirty reads in the last few hundred milliseconds was an acceptable trade-off. –  Greenstone Walker Aug 7 '13 at 1:39
    
In that case we did not use the slightly slow and clunky code above, we used something like: SELECT … FROM dbo.table WITH NOLOCK WHERE … –  Greenstone Walker Aug 7 '13 at 1:41

There is no benefit in this case.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED applies to the session (or scope if set in a child scope)

Queries start an auto commit transaction anyway (except if IMPLICIT_TRANSACTIONS is on when they will participate in an implicit transaction instead) so this is the same as

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SELECT * FROM <table_name> WHERE ..etc

in fact there is a minor performance overhead to using the explicit transactions as looked at in my answer here.

If a batch simply comprises of SELECT queries without locking hints the only time an explicit transaction makes any difference is if there are multiple such statements involved and the isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.