[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Setting transaction isolation level when reading from a view

Chris Hough

3/21/2007 7:53:00 PM

I would like to set Isolation Level to READ UNCOMMITTED for reading from a
view.

This is in an effort to prevent SQLError 1205, State 50.

Can I use an SqlTransaction object as follows? (using C# - I have tested
this code
and it does not crash and it does return the correct result set)


string myCmdStr = "SELECT * from MyView WHERE myWhereCondition";

SqlConnection myConn = new SqlConnection( myConnectionString);
myConn.Open();

SqlCommand myCommand = new SqlCommand( myCmdStr, myConn );

SqlTransaction t = myConn.BeginTransaction(
IsolationLevel.ReadUncommitted );

myCommand.Transaction = t;

SqlDataReader sqlRdr = myCommand.ExecuteReader();

while ( sqlRdr.Read() )
{
// Retrieve my result columns
}

sqlRdr.Close();
t.Commit();
myConnection.Close();


Will this result in no locking going on during the reading of my view?
Do I need to use the Commit at the end even though it is only a read?
Is it a stupid idea?
Is it possible to set Isolation Level with the connection string ?

Thanks in advance,
Valerie Hough


3 Answers

AlterEgo

3/21/2007 8:58:00 PM

0

Valerie,

Read uncommitted is not a bad idea for performance reasons as long as your
query is not going to be part of a transaction that will eventually do
updates.

Deadlocks are typically caused by two processes accessing two (or more)
objects in different sequences:
process1 locks objectA
process2 locks objectB
process1 needs objectB to complete its transaction, but is blocked by
process2
process2 needs objectA to complete its transaction, but is blocked by
process1
Deadlock

The real solution is to get these two processes tuned so you don't run into
the problem.

-- Bill

"Valerie Hough" <sales@pcTrans.com> wrote in message
news:%23S$XUH$aHHA.5044@TK2MSFTNGP05.phx.gbl...
>I would like to set Isolation Level to READ UNCOMMITTED for reading from a
>view.
>
> This is in an effort to prevent SQLError 1205, State 50.
>
> Can I use an SqlTransaction object as follows? (using C# - I have tested
> this code
> and it does not crash and it does return the correct result set)
>
>
> string myCmdStr = "SELECT * from MyView WHERE myWhereCondition";
>
> SqlConnection myConn = new SqlConnection( myConnectionString);
> myConn.Open();
>
> SqlCommand myCommand = new SqlCommand( myCmdStr, myConn );
>
> SqlTransaction t = myConn.BeginTransaction(
> IsolationLevel.ReadUncommitted );
>
> myCommand.Transaction = t;
>
> SqlDataReader sqlRdr = myCommand.ExecuteReader();
>
> while ( sqlRdr.Read() )
> {
> // Retrieve my result columns
> }
>
> sqlRdr.Close();
> t.Commit();
> myConnection.Close();
>
>
> Will this result in no locking going on during the reading of my view?
> Do I need to use the Commit at the end even though it is only a read?
> Is it a stupid idea?
> Is it possible to set Isolation Level with the connection string ?
>
> Thanks in advance,
> Valerie Hough
>


Chris Hough

3/21/2007 9:47:00 PM

0

Thanks for the response.

I'm not sure how I could resolve the conditions that result in the deadlock.

The deadlock, as far as I can tell, is caused by updates being made to data
tables underlying the view and execution of the view itself. The view is
called extremely frequently as it is used as a real time monitor (used by
several people at a time) for client operations. Each time an update is made
to an underlying table, a message is sent to each user, and his/her
workstation executes the reading of the view in order to refresh the data.
This means that depending on the timing of things, several users at once
might all issue the command to read the view at almost exactly the same
time.

Data tables underlying the view are updated frequently as well, and the
users need to see the new data as it is modified. The updates to the
underlying tables are always made dynamically using ExecuteNonQuery() and
are never part of a transaction. It is always the execution of the view that
is chosen as the deadlock victim.

I'm not sure if I have provided any extra useful information!

Regards,
Valerie Hough


AlterEgo

3/21/2007 10:48:00 PM

0

Valerie,

I forgot about the possibility that the deadlock is caused by parallellism.
If this is the case, then the read uncommitted isolation level will help.
There are a number of techniques for troubleshooting deadlocks on line to
find out. Search Google. BTW, you should also notice an improvement in the
performance of your query as well as it doesn't have to bother with locking
and lock checking.

I like using read uncommitted for most queries that are not going to affect
data integrity. What is the real consequence if every nth time, a user runs
a query and a transaction it read is rolled back. How often is this really
going to happen?

Now, if you're summarizing sales to create a monthly summary in another
table - that is a different story and you should use a higher isolation
level.

All updates are always run within transaction, even if you don't explicitly
define it. That is why you can get blocking in the first place. SQL Server
always chooses the query because it considers it the least expensive process
to cancel - no rollbacks, etc.



"Valerie Hough" <sales@pcTrans.com> wrote in message
news:OPaXLHAbHHA.1300@TK2MSFTNGP02.phx.gbl...
> Thanks for the response.
>
> I'm not sure how I could resolve the conditions that result in the
> deadlock.
>
> The deadlock, as far as I can tell, is caused by updates being made to
> data tables underlying the view and execution of the view itself. The view
> is called extremely frequently as it is used as a real time monitor (used
> by several people at a time) for client operations. Each time an update is
> made to an underlying table, a message is sent to each user, and his/her
> workstation executes the reading of the view in order to refresh the data.
> This means that depending on the timing of things, several users at once
> might all issue the command to read the view at almost exactly the same
> time.
>
> Data tables underlying the view are updated frequently as well, and the
> users need to see the new data as it is modified. The updates to the
> underlying tables are always made dynamically using ExecuteNonQuery() and
> are never part of a transaction. It is always the execution of the view
> that is chosen as the deadlock victim.
>
> I'm not sure if I have provided any extra useful information!
>
> Regards,
> Valerie Hough
>