Prev: Timeout expired
Next: SQL2005 Import
From: Eric on 24 Feb 2006 11:18
Hi,

I have a question that has to do with the safety of a NOLOCK SQL Query. The
situation includes a database that is hit very heavily by a workflow engine.
I have a request to build a app that will query the same tables as the
workflow engine and generate reports. I am worried about the reporting
application causing a deadlock, which in turn would crash the workflow
engine. I have read that NOLOCK will not issue a shared lock and not honor a
exclusive lock, I am also aware that there is a chance that a NOLOCK query
will not return accurate information. Can a select query from one process
like a reporting application cause a error in another process like the
workflow engine in this situation? Does using NOLOCK sufficiently
eliminate the risk of a error occurring? Is the NOLOCK needed at all? Thank
you for any guidance that you could give me in this matter as it is a little
over my head.

SELECT * FROM table_name WITH (NOLOCK)

From: Brian Selzer on 24 Feb 2006 14:00
NOLOCK will make your queries return incorrect results at lightning speed.
Here are a couple instances where NOLOCK is indicated. If you're computing
an average involving thousands of rows, and only a few rows may change
during the query, then it's probably OK to use NOLOCK. If you're
serializing updates to the tables involved in a query by using a mechanism
other than resource locks--such as an application lock--, then it's OK to
use NOLOCK.

What you should do is find out the order in which the workflow engine
obtains locks. Then you should use the appropriate transaction isolation
level to perform your query, but make sure that locks are obtained in the
same order. In addition, you can use SET DEADLOCK PRIORITY LOW to ensure
that the query will be the deadlock victim should one still occur.


"Eric" <Eric(a)discussions.microsoft.com> wrote in message
news:31804F59-E569-4C33-8B14-7697EBA526FC(a)microsoft.com...
> Hi,
>
> I have a question that has to do with the safety of a NOLOCK SQL Query.
> The
> situation includes a database that is hit very heavily by a workflow
> engine.
> I have a request to build a app that will query the same tables as the
> workflow engine and generate reports. I am worried about the reporting
> application causing a deadlock, which in turn would crash the workflow
> engine. I have read that NOLOCK will not issue a shared lock and not
> honor a
> exclusive lock, I am also aware that there is a chance that a NOLOCK query
> will not return accurate information. Can a select query from one process
> like a reporting application cause a error in another process like the
> workflow engine in this situation? Does using NOLOCK sufficiently
> eliminate the risk of a error occurring? Is the NOLOCK needed at all?
> Thank
> you for any guidance that you could give me in this matter as it is a
> little
> over my head.
>
> SELECT * FROM table_name WITH (NOLOCK)
>


 | 
Pages: 1
Prev: Timeout expired
Next: SQL2005 Import