My database seems to be getting blocked but I don't understand why. This is
for a desktop application and my code looks something like this:

private void toolStripButton1_Click(object sender, EventArgs e)
{
try
{
string qry = String.Format("SELECT TOP {0} * FROM URLs WHERE Status=0
AND PollCount < 5 ORDER BY URL", total);
using (SqlDataReader rdr = DataHelper.ExecDataReader(qry))
{
while (rdr.Read())
{
try
{

// ...

if (<error condition>)
throw new Exception("Custom Error");

// ...

DataHelper.ExecNonQuery("UPDATE URLs SET Status=1,
Comment=@Comment WHERE ID=@ID",
"@Comment", "Success",
"@ID", (int)rdr["ID"]);
}
catch (Exception ex)
{
DataHelper.ExecNonQuery("UPDATE URLs SET
Status=2,Comment=@Comment,PollCount=PollCount+1 WHERE ID=@ID",
"@Comment", ex.Message,
"@ID", (int)rdr["ID"]);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Unexpected error : {0}", ex.Message));
}
finally
{
// Clean up
}
}

This code was working just fine all day. But, suddenly, the database query
in the catch block was timing out, causing the second, outter, catch
statement to run.

If I set CommandTimeout to 0, it blocks indefinitely. While it was blocked,
I ran sp_who2 and the results can be seen at
http://www.fileparade.com/DBBlock.jpg.

It shows the UPDATE is blocked by the SELECT. But why does a SELECT block?

Thanks for any tips. I'm stuck!

Jonathan

On Jan 9, 9:11*am, "Jonathan Wood" <jw...@softcircuits.com> wrote:
> It shows the UPDATE is blocked by the SELECT. But why does a SELECT block?

Updates need exclusive blocks, so they need to wait until shared
blocks acquired by selects are released.

Well, I'm still not clear why a read-only operation needs any lock at all. I
want to be able to update records as I'm looping through them. Any ideas on
how this is possible? (Or why it was working fine until now?)

Thanks.

Jonathan

"Alex Kuznetsov" <(E-Mail Removed)> wrote in message
news:a602f15b-fd5c-42b5-b7e2-(E-Mail Removed)...
On Jan 9, 9:11 am, "Jonathan Wood" <jw...@softcircuits.com> wrote:
> It shows the UPDATE is blocked by the SELECT. But why does a SELECT block?

Updates need exclusive blocks, so they need to wait until shared
blocks acquired by selects are released.

You have to understand that in SQL Server the default isolation level is
Read Committed. Meaning that if any row is being modified you can not read
it until it is done. That ensures you don't get dirty reads. It goes the
other way as well. You should not be able to modify a row that someone is in
the middle of reading either. If you want a dirty read then use NOLOCK with
the read but be aware of the consequences. But I think a bigger issue is
the way you are writing your code. Since you are embedding all the code into
your app and you have not parameterized any of the statements you may not
get plan reuse and worse is that you may not use indexes properly if the
datatypes are interpreted wrong. That could be one reason you are blocking.
You did parameterize the UPDATE but not the select and the select may be
scanning the table or taking a table level lock.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Jonathan Wood" <(E-Mail Removed)> wrote in message
news:%23Udu3$(E-Mail Removed)...
> Well, I'm still not clear why a read-only operation needs any lock at all.
> I want to be able to update records as I'm looping through them. Any ideas
> on how this is possible? (Or why it was working fine until now?)
>
> Thanks.
>
> Jonathan
>
> "Alex Kuznetsov" <(E-Mail Removed)> wrote in message
> news:a602f15b-fd5c-42b5-b7e2-(E-Mail Removed)...
> On Jan 9, 9:11 am, "Jonathan Wood" <jw...@softcircuits.com> wrote:
>> It shows the UPDATE is blocked by the SELECT. But why does a SELECT
>> block?
>
> Updates need exclusive blocks, so they need to wait until shared
> blocks acquired by selects are released.

On Jan 9, 9:35*am, "Jonathan Wood" <jw...@softcircuits.com> wrote:
> Well, I'm still not clear why a read-only operation needs any lock at all.. I
> want to be able to update records as I'm looping through them. Any ideas on
> how this is possible? (Or why it was working fine until now?)

Explanation here:

http://en.wikipedia.org/wiki/Isolation_(database_systems)

Also you can use snapshot isolation when readers do not block writers.

Hi Andrew,

> You have to understand that in SQL Server the default isolation level is
> Read Committed. Meaning that if any row is being modified you can not read
> it until it is done. That ensures you don't get dirty reads. It goes the
> other way as well. You should not be able to modify a row that someone is
> in the middle of reading either. If you want a dirty read then use NOLOCK
> with the read but be aware of the consequences. But I think a bigger
> issue is the way you are writing your code. Since you are embedding all
> the code into your app and you have not parameterized any of the
> statements you may not get plan reuse and worse is that you may not use
> indexes properly if the datatypes are interpreted wrong. That could be one
> reason you are blocking.

I can't see why that would cause this, but I did try changing my select as
follows.

string qry = String.Format("SELECT TOP {0} * FROM URLs WHERE Status=@Status
AND PollCount < @PollCount ORDER BY URL", total);
using (SqlDataReader rdr = DataHelper.ExecDataReader(qry, "@Status", 0,
"@PollCount", 5))

It didn't change it though. I even changed the UPDATE query so that every
argument was parameterized.

Note that this is not a performance issue. The database appears blocked
indefinitely.

> You did parameterize the UPDATE but not the select and the select may be
> scanning the table or taking a table level lock.

I feel I'm a bit over my head here.

Thanks.

Jonathan

Thanks. I'm reading about it.

Can you suggest a way to rework my code so this would work?

Jonathan

"Russell Fields" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Jonathan,
>
> Reads also use locks (depending on the isolation level) in order to
> preserve a consistent set of data for the life of the query. If you are
> unfamiliar with isolation levels, from the BOL read:
> http://msdn.microsoft.com/en-us/library/ms173763.aspx - isolation levels
> http://msdn.microsoft.com/en-us/library/ms187373.aspx - table hints
>
> There are other papers / blogs / etc that discuss this as well. E.g. this
> on row-versioning:
> http://msdn.microsoft.com/en-us/library/ms345124.aspx
>
> RLF
>
> "Jonathan Wood" <(E-Mail Removed)> wrote in message
> news:%23Udu3$(E-Mail Removed)...
>> Well, I'm still not clear why a read-only operation needs any lock at
>> all. I want to be able to update records as I'm looping through them. Any
>> ideas on how this is possible? (Or why it was working fine until now?)
>>
>> Thanks.
>>
>> Jonathan
>>
>> "Alex Kuznetsov" <(E-Mail Removed)> wrote in message
>> news:a602f15b-fd5c-42b5-b7e2-(E-Mail Removed)...
>> On Jan 9, 9:11 am, "Jonathan Wood" <jw...@softcircuits.com> wrote:
>>> It shows the UPDATE is blocked by the SELECT. But why does a SELECT
>>> block?
>>
>> Updates need exclusive blocks, so they need to wait until shared
>> blocks acquired by selects are released.
>

Thanks. I'm reading about it.

I read a few negative things about SNAPSHOT ISOLATION, and I'm still yet to
find a code example that shows how I'd use it.

Can you suggest any way to restructure my code so this would work?

Jonathan

"Alex Kuznetsov" <(E-Mail Removed)> wrote in message
news:fe6634dd-9317-4e34-8463-(E-Mail Removed)...
On Jan 9, 9:35 am, "Jonathan Wood" <jw...@softcircuits.com> wrote:
> Well, I'm still not clear why a read-only operation needs any lock at all.
> I
> want to be able to update records as I'm looping through them. Any ideas
> on
> how this is possible? (Or why it was working fine until now?)

Explanation here:

http://en.wikipedia.org/wiki/Isolation_(database_systems)

Also you can use snapshot isolation when readers do not block writers.

On Jan 9, 10:15*am, "Jonathan Wood" <jw...@softcircuits.com> wrote:
> Thanks. I'm reading about it.
>
> I read a few negative things about SNAPSHOT ISOLATION, and I'm still yet to
> find a code example that shows how I'd use it.
>
> Can you suggest any way to restructure my code so this would work?

My take at it:

http://www.devx.com/dbzone/Article/32957/1954

"TheSQLGuru" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

> If that is the case and your problem is of sufficient importance (or even
> if it isn't), I recommend you hire a professional to help address this
> problem and also give you some mentoring along the way so you can do
> things better and investigate better in the future.

Well, I'm juggling a lot of technologies and I can't become an expert in all
of them. But if I end up having to hire help then that's what I'll do.

I guess I was hoping someone here would be able to see what was wrong and
suggest a correction.

Jonathan