blocking caused from SQL with (nolock) hint

I am seeing blocking in a database that is caused by a SQL statement that
joins two tables with (nolock) hints. How is that possible? I thought
nolock would perform a dirty read and would not block readers. Is that not
the case?
Thanks,
JayJay,
No more information (environment, SQL code, etc) than the question, means
this question is hard to answer.
(1) If your SQL is doing an update, then (of course) it will lock the
resources being updated.
(2) I have also seen a repeated instance of an older version of Access
causing a lock (a SCH-M lock) even though it had no rights to make any
schema change.
Post some more details if you have them.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> I am seeing blocking in a database that is caused by a SQL statement that
> joins two tables with (nolock) hints. How is that possible? I thought
> nolock would perform a dirty read and would not block readers. Is that
not
> the case?
> Thanks,
> Jay|||Jay P wrote:
> I am seeing blocking in a database that is caused by a SQL statement
> that joins two tables with (nolock) hints. How is that possible? I
> thought nolock would perform a dirty read and would not block
> readers. Is that not the case?
> Thanks,
> Jay
I've seen undesirable results with NOLOCK on temp tables. Is this the
case? Post your SQL please.
--
David Gugick
Imceda Software
www.imceda.com|||The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b WHERE
a.col1='x' and b.col2='y' ' I am using a script to check for blocking that
generates a SQL Profiler trace and also using the sp_pss80 script to show
locks and input buffer contents but I'm having problems interpreting the
output. I do know that when I run this statement, I get some blocking going
on and I'm confused by the fact that it's just a SELECT (dirty read) type
operation albeit on a rather large table of appx. 13 million rows and is
doing a index range scan... Thanks for the reply.
"Russell Fields" wrote:
> Jay,
> No more information (environment, SQL code, etc) than the question, means
> this question is hard to answer.
> (1) If your SQL is doing an update, then (of course) it will lock the
> resources being updated.
> (2) I have also seen a repeated instance of an older version of Access
> causing a lock (a SCH-M lock) even though it had no rights to make any
> schema change.
> Post some more details if you have them.
> RLF
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > I am seeing blocking in a database that is caused by a SQL statement that
> > joins two tables with (nolock) hints. How is that possible? I thought
> > nolock would perform a dirty read and would not block readers. Is that
> not
> > the case?
> >
> > Thanks,
> > Jay
>
>|||Jay,
Hmmmm....
If the join and select is big enough, SQL Server may need to create
worktables in order to handle the whole operation. It is possible that (if
worktables are being created) that you are doing some blocking on tempdb
system tables. Is that possible?
Beyond that, I have no brilliant ideas, because (as you say) you should be
getting dirty reads without locking.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
WHERE
> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
that
> generates a SQL Profiler trace and also using the sp_pss80 script to show
> locks and input buffer contents but I'm having problems interpreting the
> output. I do know that when I run this statement, I get some blocking
going
> on and I'm confused by the fact that it's just a SELECT (dirty read) type
> operation albeit on a rather large table of appx. 13 million rows and is
> doing a index range scan... Thanks for the reply.
> "Russell Fields" wrote:
> > Jay,
> >
> > No more information (environment, SQL code, etc) than the question,
means
> > this question is hard to answer.
> >
> > (1) If your SQL is doing an update, then (of course) it will lock the
> > resources being updated.
> >
> > (2) I have also seen a repeated instance of an older version of Access
> > causing a lock (a SCH-M lock) even though it had no rights to make any
> > schema change.
> >
> > Post some more details if you have them.
> >
> > RLF
> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > > I am seeing blocking in a database that is caused by a SQL statement
that
> > > joins two tables with (nolock) hints. How is that possible? I
thought
> > > nolock would perform a dirty read and would not block readers. Is
that
> > not
> > > the case?
> > >
> > > Thanks,
> > > Jay
> >
> >
> >|||When worktables are created there should not be blocking.
You could use sp_lock to find out if blocking is caused by any lock
resource, and use select * from sysprocesses to find out the wait type and
wait time. The Books Online has more details on how to use the two.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23ErXqoW3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Jay,
> Hmmmm....
> If the join and select is big enough, SQL Server may need to create
> worktables in order to handle the whole operation. It is possible that
> (if
> worktables are being created) that you are doing some blocking on tempdb
> system tables. Is that possible?
> Beyond that, I have no brilliant ideas, because (as you say) you should be
> getting dirty reads without locking.
> RLF
>
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
>> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
> WHERE
>> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
> that
>> generates a SQL Profiler trace and also using the sp_pss80 script to show
>> locks and input buffer contents but I'm having problems interpreting the
>> output. I do know that when I run this statement, I get some blocking
> going
>> on and I'm confused by the fact that it's just a SELECT (dirty read) type
>> operation albeit on a rather large table of appx. 13 million rows and is
>> doing a index range scan... Thanks for the reply.
>> "Russell Fields" wrote:
>> > Jay,
>> >
>> > No more information (environment, SQL code, etc) than the question,
> means
>> > this question is hard to answer.
>> >
>> > (1) If your SQL is doing an update, then (of course) it will lock the
>> > resources being updated.
>> >
>> > (2) I have also seen a repeated instance of an older version of Access
>> > causing a lock (a SCH-M lock) even though it had no rights to make any
>> > schema change.
>> >
>> > Post some more details if you have them.
>> >
>> > RLF
>> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
>> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
>> > > I am seeing blocking in a database that is caused by a SQL statement
> that
>> > > joins two tables with (nolock) hints. How is that possible? I
> thought
>> > > nolock would perform a dirty read and would not block readers. Is
> that
>> > not
>> > > the case?
>> > >
>> > > Thanks,
>> > > Jay
>> >
>> >
>> >
>