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.

Where was the Select coming from? QA? Access? Enterprise Manager? they
take different types of locks....

--

Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

Blog posts for new DBAs:
http://kevin3nf.blogspot.com/search?q=sql+101

I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.

Twitter: Kevin3NF

"css" <css (AT) discussions (DOT) microsoft.com> wrote

SELECT is coming from a Legacy client application writtern in power builder.

I hit the wrong button which marked this post answerd. I will mark it
answered but if someone can clarify. Thanks in advance.

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/libr...6(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 (AT) discussions (DOT) microsoft.com> wrote

Russell, It looks like there are some processes that transfer data to SAP and
this select was part of it and one of those processes failed.



Just curious,
what isolation level can cause SELECT to block (repeatable read or
serializable?)

.... also READ COMMITTED will release shared locks as soon as the read
operation "moves on", while higher isolation levels will keep the
acquired locks until end of statement/transaction (or even more locks
that that, potentially, in the case of serializable).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Russell Fields" <russellfields (AT) nomail (DOT) com> wrote

If you read the link to the BOL, REPEATABLE READ or SERIALIZABLE are higher
isolation levels that hold locks for the duration of the query. Both are
documented as 'use only if necessary' due to the concurrency issues. Look
also at Tibor's comment on READ COMMITTED's behavior.

I suspect that the SAP load job was running at one of those higher isolation
levels because of the desire to insert data into SAP without phantom reads
and other issues. If that is necessary, then you may need to live with the
possibility and monitor for it.

However, running at a lower isolation level would reduce the risk of such a
block if that is feasable. For example, if it is possible to create a
transfer that would only select rows that cannot be in flux.

It seems that your transfer to SAP failed in such a way that the connection
/ transaction was still open. That is always an ugly problem to find.
However, you can also find long running transactions by using on each
database of interest by examing the oldest open transaction:
DBCC OPENTRAN(dbname)

RLF


"css" <css (AT) discussions (DOT) microsoft.com> wrote

Another thing to consider is that the SELECT statement may not be the root
cause. It in turn may be blocked by some uncommitted long running DML.

Linchi

"css" wrote: