table hosed: isolation

Showing posts with label isolation. Show all posts

Monday, March 26, 2012

Is there a known NOLOCK bug?

NOLOCK locking hint and SET TRANSACTION ISOLATION LEVEL UNCOMMITTED
should be both semantically the same - dirty read.
However, I have heard that because of a bug, SQL Server ignores SET command
and produces a high number of database locks. NOLOCK hint is supposed to
work all the time.
As a result of this bug, it is possible to reduce the number locks by
replacing SET to NOLOCK.
Has anybody seen this behavior?
Are there any cases when SET does not work but NOLOCK does? What are the
conditions when this occurs?
If this is a bug, was it fixed?
Can somebody point me to a KB article?
I really want to find an answer for this before I start changing hundreds of
stored procedures...
Thanks,
-Stan> However, I have heard that because of a bug, SQL Server ignores SET
command
Where did you "hear" this? Did they provide a repro?
I have not seen or heard this, but it's certainly possible... if you can
provide a repro, or give us the source of where you saw this information, it
can probably be refuted more easily. Tough to disprove something
hypethetical...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I actually found something:
PSS ID Number: 171322
Q171322
BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc
Has it been fixed?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4Jbdh$1EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > However, I have heard that because of a bug, SQL Server ignores SET
> command
> Where did you "hear" this? Did they provide a repro?
> I have not seen or heard this, but it's certainly possible... if you can
> provide a repro, or give us the source of where you saw this information,
it
> can probably be refuted more easily. Tough to disprove something
> hypethetical...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||This looks like it only affects SQL Server 6.5. Are you using SQL Server
6.5?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Stan" <nospam@.yahoo.com> wrote in message
news:emnpck$1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> I actually found something:
> PSS ID Number: 171322
> Q171322
> BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc
> Has it been fixed?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:e4Jbdh$1EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > However, I have heard that because of a bug, SQL Server ignores SET
> > command
> >
> > Where did you "hear" this? Did they provide a repro?
> >
> > I have not seen or heard this, but it's certainly possible... if you can
> > provide a repro, or give us the source of where you saw this
information,
> it
> > can probably be refuted more easily. Tough to disprove something
> > hypethetical...
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
>|||No, 2000
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uC4%23mn$1EHA.3120@.TK2MSFTNGP12.phx.gbl...
> This looks like it only affects SQL Server 6.5. Are you using SQL Server
> 6.5?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Stan" <nospam@.yahoo.com> wrote in message
> news:emnpck$1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> > I actually found something:
> >
> > PSS ID Number: 171322
> > Q171322
> >
> > BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc
> >
> > Has it been fixed?
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:e4Jbdh$1EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > > However, I have heard that because of a bug, SQL Server ignores SET
> > > command
> > >
> > > Where did you "hear" this? Did they provide a repro?
> > >
> > > I have not seen or heard this, but it's certainly possible... if you
can
> > > provide a repro, or give us the source of where you saw this
> information,
> > it
> > > can probably be refuted more easily. Tough to disprove something
> > > hypethetical...
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> >
> >
>|||Then you don't have to worry about it.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Stan" <nospam@.yahoo.com> wrote in message
news:OxXHE8G2EHA.1124@.tk2msftngp13.phx.gbl...
> No, 2000
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uC4%23mn$1EHA.3120@.TK2MSFTNGP12.phx.gbl...
> > This looks like it only affects SQL Server 6.5. Are you using SQL
Server
> > 6.5?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:emnpck$1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> > > I actually found something:
> > >
> > > PSS ID Number: 171322
> > > Q171322
> > >
> > > BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc
> > >
> > > Has it been fixed?
> > >
> > > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > > news:e4Jbdh$1EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > > > However, I have heard that because of a bug, SQL Server ignores
SET
> > > > command
> > > >
> > > > Where did you "hear" this? Did they provide a repro?
> > > >
> > > > I have not seen or heard this, but it's certainly possible... if you
> can
> > > > provide a repro, or give us the source of where you saw this
> > information,
> > > it
> > > > can probably be refuted more easily. Tough to disprove something
> > > > hypethetical...
> > > >
> > > > --
> > > > http://www.aspfaq.com/
> > > > (Reverse address to reply.)
> > > >
> > > >
> > >
> > >
> >
> >
>

Is there a difference between NOLOCK and SET TRANSACTION ISOLATION..?

Hello Stan,
Thanks for using MSDN Managed Newsgroup.
(NOLOCK) hint and the READ UNCOMMITED isolation have the same
locking behavior. While Selecting (reading) the
data, they do not acquire the Shared Locks. The only difference is
(NOLOCK) should be used with every SELECT query where as
READ UNCOMMITED isolation level is in effect for the whole connection.
Does that answer your question ?
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--

quote:

>Content-Class: urn:content-classes:message
>From: "Stan" <nospam@.yahoo.com>
>Sender: "Stan" <nospam@.yahoo.com>
>Subject: Is there a difference between NOLOCK and SET TRANSACTION

ISOLATION..?

quote:

>Date: Fri, 9 Jan 2004 06:04:10 -0800
>Lines: 16
>Message-ID: <0afe01c3d6b9$743092a0$a301280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>thread-index: AcPWuXQwGhpIfFQsS/qGSPe3cR+CaQ==
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:323548
>NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I always thought that NOLOCK locking hint and SET
>TRANSACTION ISOLATION LEVEL READ UNCOMMITTED are the same,
>but have heard an opinion that READ UNCOMMITED allows to
>read dirty data, but places the shared lock, an NOLOCK
>does not place the shared lock. So, supposedly NOLOCK is
>better in the cases where SELECT should not cause any
>locks.
>Is it true?
>If I select data with READ UNCOMMITED, do I put still put
>shared lock?
>Thanks,
>-Stan
>

>Does that answer your question ?
Yes, thanks!|||Since NOLOCK is a "Hint" isn't the Isolation Level Setting more reliable ?
Greg Jackson
PDX, Oregonsql

Is there a difference between NOLOCK and SET TRANSACTION ISOLATION..?

I always thought that NOLOCK locking hint and SET
TRANSACTION ISOLATION LEVEL READ UNCOMMITTED are the same,
but have heard an opinion that READ UNCOMMITED allows to
read dirty data, but places the shared lock, an NOLOCK
does not place the shared lock. So, supposedly NOLOCK is
better in the cases where SELECT should not cause any
locks.
Is it true?
If I select data with READ UNCOMMITED, do I put still put
shared lock?
Thanks,
-StanHello Stan,
Thanks for using MSDN Managed Newsgroup.
(NOLOCK) hint and the READ UNCOMMITED isolation have the same
locking behavior. While Selecting (reading) the
data, they do not acquire the Shared Locks. The only difference is
(NOLOCK) should be used with every SELECT query where as
READ UNCOMMITED isolation level is in effect for the whole connection.
Does that answer your question ?
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
>Content-Class: urn:content-classes:message
>From: "Stan" <nospam@.yahoo.com>
>Sender: "Stan" <nospam@.yahoo.com>
>Subject: Is there a difference between NOLOCK and SET TRANSACTION
ISOLATION..?
>Date: Fri, 9 Jan 2004 06:04:10 -0800
>Lines: 16
>Message-ID: <0afe01c3d6b9$743092a0$a301280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>thread-index: AcPWuXQwGhpIfFQsS/qGSPe3cR+CaQ==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:323548
>NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I always thought that NOLOCK locking hint and SET
>TRANSACTION ISOLATION LEVEL READ UNCOMMITTED are the same,
>but have heard an opinion that READ UNCOMMITED allows to
>read dirty data, but places the shared lock, an NOLOCK
>does not place the shared lock. So, supposedly NOLOCK is
>better in the cases where SELECT should not cause any
>locks.
>Is it true?
>If I select data with READ UNCOMMITED, do I put still put
>shared lock?
>Thanks,
>-Stan
>|||>Does that answer your question ?
Yes, thanks!|||Since NOLOCK is a "Hint" isn't the Isolation Level Setting more reliable ?
Greg Jackson
PDX, Oregon

Monday, March 26, 2012

Is there a known NOLOCK bug?

Is there a known NOLOCK bug?

Is there a known NOLOCK bug?

Is there a difference between NOLOCK and SET TRANSACTION ISOLATION..?

Is there a difference between NOLOCK and SET TRANSACTION ISOLATION..?

table hosed

Blog Archive

About Me