SELECT WITH NOLOCKS

Question

SELECT WITH NOLOCKS

degrem_m

SSCommitted

Points: 1519
More actions
May 24, 2006 at 2:27 am

#172579

All,
I have regularly this message : Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
If I analyze my code, I can see that issue provide from query 'SELECT'.
I would like to know if I use 'SELECT ... FROM ... WITH (nolock)' is a good idea for to fix this errors ?
Thanks for you help.

Kindest Regards,
degrem_m
Degremont

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246948 More actions · Answer 1

It should fix the problem, provided you are prepared to live with the fact that your SELECT might read data from uncommitted transactions that may be rolled back.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Alkesh Khedle Right there with Babe Points: 780 More actions · Answer 2

Hi I am getting same error even using with nolock in select query???

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

Are you sure its the right "select" statement that you have put nolock on. Better check things once again.

"Keep Trying"

Patrick Duflot SSChasing Mays Points: 660 More actions · Answer 4

Hi,

Be sure you apply 'WITH NO LOCK' on every table of your FROM clause...

On the other hand, I think you should first find which INSERT/UPDATE statement holds locks for such a long time...

P.

Patrick Duflot

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

You could set Isolation Level read Uncommitted for the whole stored proc that has the select that causes dead locks

-Roy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Personally I'd recommend finding the cause of the deadlocks and resolving that (usually bad code or bad indexing) rather than putting nolock everywhere. It can have some interesting effects (read uncommitted data, read rows twice, miss rows entirely and the like)

Nolock's basically saying to SQL that you don't mind if the data's not 100% accurate.

Enabling traceflags 1204 or 1222 will write the deadlock graphs into the error log. With that info, you can identify the processes involved in the deadlock, what procs and what queries in the procs they were running at the time. You can also see the deadlock resource. With that information, 8it should be possible to fix the root cause of the deadlocks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alkesh Khedle Right there with Babe Points: 780 More actions · Answer 7

Hello Gail,

U r absoulately correct about putting Nolock every where is not the best way to avoid the deadlocks becouse it will cause dirty read on the tables where we put Nolock while selecting.But it must be the solution to prevant deadlock condition.

But in my case I have put even with nolock condition for each table what I am using to select data eventhough I am getting the same error in peak hours.That means there is no meaning of using nolock??Is QSL Server 2005 Dynamically select locks irrespective of the hints we used in he sql server.......

Alkesh K.

MCP SQL Server Developer

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Have you checked what the statements are that are involved in the deadlocks?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Rowan SSC Guru Points: 56440 More actions · Answer 9

I'm with Gail here folks. Why not find and fix the problem instead of masking it with NOLOCKs? If you are 100% OK with dirty reads, maybe consider locking hints/local isolation level changes, but I personally have not seen too many occasions where dirty reads are really ideal.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

I'd have to agree. Just about the only thing preventing NOLOCK would be a schema-lock, or something changing your DDL. Do you have something routinely modifying your table structure????

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 11

I agree with the others... find and fix the problem... and, Know this... Setting the Transaction Isolation Level and using WITH (NOLOCK) is NOT a panacea for fixing deadlocks.

We were getting 640 deadlocks per day... the folks before me put WITH (NOLOCK) on every single table name in all the stored procedures and UDF's everywhere... it didn't help because it only works on SELECTs, not Updates, Inserts, or Deletes.

When you find the code that's causing it, post it and maybe we can help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Matt Miller (3/18/2008)
I'd have to agree. Just about the only thing preventing NOLOCK would be a schema-lock, or something changing your DDL. Do you have something routinely modifying your table structure????

It could be that the deadlocks are from data modifications (update insert, delete), since they will always take locks.

I can't recall ever seeing a deadlock that didn't have al least one data modificaion (or an X locking hint) involved.

akumar: Post the deadlock graph here, and we can help you interpret it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alkesh Khedle Right there with Babe Points: 780 More actions · Answer 13

I am agree with your view that sm update statements might be present in the batch that will create the deadlock condition but why with nolock on each table in select query will break the deadlock ,because ultimately with nolock will fetch all commited and uncommited

data then why sql server creating deadlock.......????

I will try to get the deadlock graph today for same senarion and put here, might help us to find the reason...

Thanks

Alkesh K.

MCP DB Developer

Jim Dillon-291542 Hall of Fame Points: 3344 More actions · Answer 14

Mr. Moden and Gilamonster are correct.

I strongly suggest using sp_who and sp_who2, Activity Monitor, or SQL Server Profiler to see what is blocking.

Then look at the suspect code, I bet you find something that could use some tuning.

If you have to put NOLOCK on all your sps, I'd say there's probably something bad wrong.

WITH NOLOCK is not much of a solution.