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...
> 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...
information,[vbcol=seagreen]
> it
>|||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...
can[vbcol=seagreen]
> information,
>|||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...
Server[vbcol=seagreen]
SET[vbcol=seagreen]
> can
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment