Asp Forum - SQL Server Error 1205, State 50 while reading a view

Chris Hough

3/21/2007 11:44:00 AM

Client is using SQL Server 2000 SP4.

They are getting SQL Error 1205, State 50 while reading results from a view
as follows:

string connStr = "Server=MyServer;" +
"IntegratedSecurity=SSPI;"
"Pooling=true;" +
"Initial Catalog=MyDatabase";

string cmdStr = "SELECT * FROM MyView";

SqlConnection sqlConn = new SqlConnection( connStr );
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand( cmdStr, sqlConn);
SqlDataReader sqlRdr = sqlCmd.ExecuteReader();

while( sqlRdr.Read() )
{
// get result columns for each record
}

sqlRdr.Close();
sqlConn.Close();

This view does get executed alot, especially when 5 users are pounding away
at the keyboards, and there are about 60 columns in each result row.

Can anyone please help me understand how deadlocks can occur during an
operation that as far as I can tell does nothing but read from a predefined
view?

Any suggestions for helping diagnose and fix the problem. It may be my
imagination, but I think this did not happen before SQL Server was upgraded
to SP4.

Thanks in advance.
Valerie Hough

9 Answers

Uri Dimant

3/21/2007 11:49:00 AM

Valerie

When you perfrom this statement (btw why do you use '*' instead of
specifying names of the columns and why don't you have a WHERE condition?)
SQL Server creates shared lock. This type of locks prevents the locked
resource from receiving an exclusive lock, so if another user runs a query
that need to UPDATE a row, they will not be able to until all of the shared
locks on the row are gone. It looks strange in your case because as you said
there is no another process that wants to UPDATE the table.
I'd run SQL Server profiler to identify what is going on.

"Valerie Hough" <sales@pcTrans.com> wrote in message
news:u98A816aHHA.4012@TK2MSFTNGP03.phx.gbl...
> Client is using SQL Server 2000 SP4.
>
> They are getting SQL Error 1205, State 50 while reading results from a
> view
> as follows:
>
> string connStr = "Server=MyServer;" +
> "IntegratedSecurity=SSPI;"
> "Pooling=true;" +
> "Initial Catalog=MyDatabase";
>
> string cmdStr = "SELECT * FROM MyView";
>
> SqlConnection sqlConn = new SqlConnection( connStr );
> sqlConn.Open();
> SqlCommand sqlCmd = new SqlCommand( cmdStr, sqlConn);
> SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
>
> while( sqlRdr.Read() )
> {
> // get result columns for each record
> }
>
> sqlRdr.Close();
> sqlConn.Close();
>
> This view does get executed alot, especially when 5 users are pounding
> away
> at the keyboards, and there are about 60 columns in each result row.
>
> Can anyone please help me understand how deadlocks can occur during an
> operation that as far as I can tell does nothing but read from a
> predefined
> view?
>
> Any suggestions for helping diagnose and fix the problem. It may be my
> imagination, but I think this did not happen before SQL Server was
> upgraded
> to SP4.
>
> Thanks in advance.
> Valerie Hough
>
>
>

Dan Guzman

3/21/2007 1:00:00 PM

> Can anyone please help me understand how deadlocks can occur during an
> operation that as far as I can tell does nothing but read from a
> predefined
> view?

One scenario that might result in a deadlock is that the SELECT gets blocked
by a concurrent UPDATE and the UPDATE then gets blocked by the SELECT. SQL
Server will probably choose the SELECT as the deadlock victim because that
query is least costly to rerun.

Application design and tuning greatly affect the likelihood of deadlocks. I
see that you have no WHERE clause so all data will be returned. You can
greatly improve performance and reduce the chance of deadlocks by adding
WHERE clauses to your queries and examining execution plans to ensure
indexes are used efficiently to access the minimal amount of data needed.
When you need to update multiple tables in a single transaction, make sure
this is done in a consistent order.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Valerie Hough" <sales@pcTrans.com> wrote in message
news:u98A816aHHA.4012@TK2MSFTNGP03.phx.gbl...
> Client is using SQL Server 2000 SP4.
>
> They are getting SQL Error 1205, State 50 while reading results from a
> view
> as follows:
>
> string connStr = "Server=MyServer;" +
> "IntegratedSecurity=SSPI;"
> "Pooling=true;" +
> "Initial Catalog=MyDatabase";
>
> string cmdStr = "SELECT * FROM MyView";
>
> SqlConnection sqlConn = new SqlConnection( connStr );
> sqlConn.Open();
> SqlCommand sqlCmd = new SqlCommand( cmdStr, sqlConn);
> SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
>
> while( sqlRdr.Read() )
> {
> // get result columns for each record
> }
>
> sqlRdr.Close();
> sqlConn.Close();
>
> This view does get executed alot, especially when 5 users are pounding
> away
> at the keyboards, and there are about 60 columns in each result row.
>
> Can anyone please help me understand how deadlocks can occur during an
> operation that as far as I can tell does nothing but read from a
> predefined
> view?
>
> Any suggestions for helping diagnose and fix the problem. It may be my
> imagination, but I think this did not happen before SQL Server was
> upgraded
> to SP4.
>
> Thanks in advance.
> Valerie Hough
>
>
>

Chris Hough

3/21/2007 2:01:00 PM

Thanks for your response.

Sorry, I forgot to mention that I do have both a WHERE and an ORDER BY
whenever I execute the view. Does that change your opinion of how I might
address the problem?

Thank you,
Valerie Hough

Chris Hough

3/21/2007 2:09:00 PM

Thanks for your response.

Sorry, but I forgot to mention that there is both a WHERE and an ORDER BY
specified whenever the view is executed.

Only a single table is updated per transaction.

This is a user interface where updates are frequently made to the data
underlying the view, and each user needs to see the most current state of
the data at all times.

Does this change the approach I should take to solving the problem?

Thanks again,
Valerie Hough

Chris Hough

3/21/2007 2:54:00 PM

Some additional information:
when running the query in the Query Analyzer the result set is returned in 1
second,
after the query is cached results are returned in 0 seconds.

Chris Hough

3/21/2007 2:55:00 PM

Additionally, when I run the query by hand in the Query Analyzer, the result
set returns in 1 second, 0 seconds after the view is cached.

Dan Guzman

3/22/2007 12:41:00 AM

> Does this change the approach I should take to solving the problem?

Not really. The first thing I usually do when I find deadlocks is to make
sure that both select and update queries are fully optimized. Not only will
this improve performance, efficient index utilization will reduce the amount
of data accessed and corresponding deadlocks. Check out Bart Duncan's blog
at
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-P....

Since your updates involve only a single table, the deadlock resources are
probably different rows in the same table. If you still encounter deadlocks
after optimizing your queries. you can mitigate deadlocks by adding a
TABLOCK hint to your updates. This will have a negative affect on
concurrency but shouldn't be noticeable if the queries run quickly.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Valerie Hough" <sales@pcTrans.com> wrote in message
news:OsDvBH8aHHA.2316@TK2MSFTNGP04.phx.gbl...
> Thanks for your response.
>
> Sorry, but I forgot to mention that there is both a WHERE and an ORDER BY
> specified whenever the view is executed.
>
> Only a single table is updated per transaction.
>
> This is a user interface where updates are frequently made to the data
> underlying the view, and each user needs to see the most current state of
> the data at all times.
>
> Does this change the approach I should take to solving the problem?
>
> Thanks again,
> Valerie Hough
>

Uri Dimant

3/22/2007 6:45:00 AM

Hi Dan
> after optimizing your queries. you can mitigate deadlocks by adding a
> TABLOCK hint to your updates. This will have a negative affect on
> concurrency but shouldn't be noticeable if the queries run quickly.

I think that he can add UPDLOCK instead of TABLOCK to mitigate and in some
cases prevent from deadlocks.
UPDLOCK does not prevent writes from manipulating with data and we don't
need to put an exclusive lock (which reduces concurrency)on every row it put
the shared lock on the requisite rows only that means that does not block
another users.
Just my two cents.

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:5D305B5D-D0CD-481D-8D03-68893D1EDA61@microsoft.com...
>> Does this change the approach I should take to solving the problem?
>
> Not really. The first thing I usually do when I find deadlocks is to make
> sure that both select and update queries are fully optimized. Not only
> will this improve performance, efficient index utilization will reduce the
> amount of data accessed and corresponding deadlocks. Check out Bart
> Duncan's blog at
> http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-P....
>
> Since your updates involve only a single table, the deadlock resources are
> probably different rows in the same table. If you still encounter
> deadlocks after optimizing your queries. you can mitigate deadlocks by
> adding a TABLOCK hint to your updates. This will have a negative affect
> on concurrency but shouldn't be noticeable if the queries run quickly.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Valerie Hough" <sales@pcTrans.com> wrote in message
> news:OsDvBH8aHHA.2316@TK2MSFTNGP04.phx.gbl...
>> Thanks for your response.
>>
>> Sorry, but I forgot to mention that there is both a WHERE and an ORDER BY
>> specified whenever the view is executed.
>>
>> Only a single table is updated per transaction.
>>
>> This is a user interface where updates are frequently made to the data
>> underlying the view, and each user needs to see the most current state of
>> the data at all times.
>>
>> Does this change the approach I should take to solving the problem?
>>
>> Thanks again,
>> Valerie Hough
>>
>

Dan Guzman

3/23/2007 3:49:00 AM

> I think that he can add UPDLOCK instead of TABLOCK to mitigate and in some
> cases prevent from deadlocks.

Uri, I don't believe specifying an UPDLOCK on an UPDATE statement will
mitigate deadlocks. I suggested TABLOCK to ensure UPDATE statements didn't
deadlock with concurrent SELECT queries, but only if other attempts to
resolve the deadlocks fail.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:uRWOZ3EbHHA.1400@TK2MSFTNGP06.phx.gbl...
> Hi Dan
>> after optimizing your queries. you can mitigate deadlocks by adding a
>> TABLOCK hint to your updates. This will have a negative affect on
>> concurrency but shouldn't be noticeable if the queries run quickly.
>
>
> I think that he can add UPDLOCK instead of TABLOCK to mitigate and in some
> cases prevent from deadlocks.
> UPDLOCK does not prevent writes from manipulating with data and we don't
> need to put an exclusive lock (which reduces concurrency)on every row it
> put the shared lock on the requisite rows only that means that does not
> block another users.
> Just my two cents.
>
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:5D305B5D-D0CD-481D-8D03-68893D1EDA61@microsoft.com...
>>> Does this change the approach I should take to solving the problem?
>>
>> Not really. The first thing I usually do when I find deadlocks is to
>> make sure that both select and update queries are fully optimized. Not
>> only will this improve performance, efficient index utilization will
>> reduce the amount of data accessed and corresponding deadlocks. Check
>> out Bart Duncan's blog at
>> http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-P....
>>
>> Since your updates involve only a single table, the deadlock resources
>> are probably different rows in the same table. If you still encounter
>> deadlocks after optimizing your queries. you can mitigate deadlocks by
>> adding a TABLOCK hint to your updates. This will have a negative affect
>> on concurrency but shouldn't be noticeable if the queries run quickly.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Valerie Hough" <sales@pcTrans.com> wrote in message
>> news:OsDvBH8aHHA.2316@TK2MSFTNGP04.phx.gbl...
>>> Thanks for your response.
>>>
>>> Sorry, but I forgot to mention that there is both a WHERE and an ORDER
>>> BY specified whenever the view is executed.
>>>
>>> Only a single table is updated per transaction.
>>>
>>> This is a user interface where updates are frequently made to the data
>>> underlying the view, and each user needs to see the most current state
>>> of the data at all times.
>>>
>>> Does this change the approach I should take to solving the problem?
>>>
>>> Thanks again,
>>> Valerie Hough
>>>
>>
>
>

microsoft.public.sqlserver.programming

SQL Server Error 1205, State 50 while reading a view

Chris Hough

Uri Dimant

Dan Guzman

Chris Hough

Chris Hough

Chris Hough

Chris Hough

Dan Guzman

Uri Dimant

Dan Guzman

x Login to ForumsZone