Allow to set query transaction isolation level
Most of our queries' transaction isolation level is ReadUncommitted. This is very important on our production db to minimize the impact.
I find myself surrounding my queries with TransactionScope all the time.
In addition, some of the queries requries Snapshot isolation's, etc.
It would be very helpful to allow to set the isolation level in the advanced query properties or something similar.
Thanks.
Shlomi
shared this idea
3 comments
-
Jason DiOrio commented
It would also be nice to have support for "set deadlock_priority low;" since any ad hoc queries we do don't want to get in production's way. I'm currently working on a workaround for this, but it would be nice to have it build into the context in some way.
-
Shlomi
commented
Hi Bill,
If you want I've created a small snippet for TransactionScope - 'tscope':
(can used also as surround-with)<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>TransactionScope</Title>
<Shortcut>tscope</Shortcut>
<Description></Description>
<Author>Shlomi</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
<SnippetType>SurroundsWith</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Code Language="csharp"><![CDATA[using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
$selected$ $end$
}]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets> -
Bill Sorensen
commented
A big +3 here. We are required to use nolock hints when running ad hoc queries against production. This makes it difficult to use LINQPad as often as I'd like.
