|
Home > Archive > MS SQL Server > October 2006 > WITH NOLOCK and ROWLOCK
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
WITH NOLOCK and ROWLOCK
|
|
| mpaine@htxml.com 2006-10-24, 6:31 pm |
| I am a bit confused as how WITH (NOLOCK) and WITH (ROWLOCK) work. Is
it ok to use these statements often if I never use BEGIN TRANSACTION,
COMMIT TRANSACTION or other transaction related statements? Or, if
those are used, make sure they are unrelated to the same tables and
columns.
Thank you,
Michael
| |
| David Browne 2006-10-24, 6:31 pm |
|
<mpaine@htxml.com> wrote in message
news:1158970336.482355.126880@k70g2000cwa.googlegroups.com...
>I am a bit confused as how WITH (NOLOCK) and WITH (ROWLOCK) work. Is
> it ok to use these statements often if I never use BEGIN TRANSACTION,
> COMMIT TRANSACTION or other transaction related statements?
>. . .
No. ROWLOCK is pretty useless and harmless. But NOLOCK should be avoided
if possible. Using NOLOCK you can get results that are just plain wrong,
and it will happen unpredictably, depending on what else is going on.
Even if you never use explicit transactions, SQL Server runs each single
statement as a transaction. An UPDATE may delete one index key and add a
new one, an INSERT may write the index entries before updating the row data,
etc. NOLOCK reads this intermediate state and can return results that are
just plain wrong.
David
| |
| JXStern 2006-10-24, 6:31 pm |
| On Fri, 22 Sep 2006 19:32:39 -0500, "David Browne" <davidbaxterbrowne
no potted meat@hotmail.com> wrote:
>No. ROWLOCK is pretty useless and harmless. But NOLOCK should be avoided
>if possible. Using NOLOCK you can get results that are just plain wrong,
>and it will happen unpredictably, depending on what else is going on.
But it is used very widely anyway. Caveat programmer.
J.
|
|
|
|
|