Re: Can SELECT block UPDATE?
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Thu, 27 Aug 2009 10:17:44 -0400
Yes, a select can block an update depending on the isolation level.
Here is a link to the SQL Server 2000 BOL topic:
http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx
Selecting in the READ UNCOMMITTED isolatino level, or using the NOLOCK hint, is the least restrictive locking, but as the notes explain, it has its downsides as well. You have to use your good judgment on whether those downsides are compatible with the purpose of the select.
Of course, things go wrong, but why was the select running for two hours? Was it a problem with the code? The connection? Etc?
RLF
"css" <css@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:7E1B3CAA-1A95-4986-BFE8-F23258A77A24@xxxxxxxxxxxxxxxx
We have a SQL 2000 database, this morning a job than run simple update on a
table with 18,000 rows kept running for two hours (normally it takes about
couple seconds)
I tried to find spid that was causing the block, Under SQL EM under current
activity, found that spid that was blocking to my surprise it was a select
statement. I killed that spid and update ran fine afterwords.
Based on my understanding SELECT takes a shared lock, not exclusive but can
it starv the update for two hours.
.
- Follow-Ups:
- Re: Can SELECT block UPDATE?
- From: Tibor Karaszi
- Re: Can SELECT block UPDATE?
- From: css
- Re: Can SELECT block UPDATE?
- References:
- Can SELECT block UPDATE?
- From: css
- Can SELECT block UPDATE?
- Prev by Date: Re: Can SELECT block UPDATE?
- Next by Date: Re: Servers were down - reason?
- Previous by thread: Re: Can SELECT block UPDATE?
- Next by thread: Re: Can SELECT block UPDATE?
- Index(es):
Relevant Pages
|
