Is this correct use of WITH (NOLOCK, READUNCOMMITTED) in delete?

Question

Post reply

Is this correct use of WITH (NOLOCK, READUNCOMMITTED) in delete?

ashuthinks

SSCommitted

Points: 1698
More actions
November 16, 2011 at 7:47 am

#246247

DELETE
dbo.Reporting_BondIndicative
FROM
dbo.Reporting_BondIndicative Reporting_BondIndicative
INNER JOIN
(
SELECT
SecurityId,
MIN(CUSIP) AS CUSIP
FROM
Fireball_Reporting..Reporting_BondIndicative -- WITH (NOLOCK, READUNCOMMITTED)
GROUP BY
SecurityId
) MinCusip ON
Reporting_BondIndicative.CUSIP = MinCusip.CUSIP AND
Reporting_BondIndicative.SecurityId = MinCusip.SecurityId
WHERE
Reporting_BondIndicative.SecurityId IN
(
SELECT
SecurityId
FROM
dbo.Reporting_BondIndicative -- WITH (NOLOCK, READUNCOMMITTED)
GROUP BY
SecurityId
HAVING
COUNT(*) > 1
)
as there is select statement so please tell me i'm correct or not?

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

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

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

Firstly those 2 hints mean exactly the same thing, so you're just repeating yourself.

Secondly, those hints have NO place in most code most of the time.

Thirdly, did you read through all the repeated warning about those hints? NOLOCK is NOT a go-faster switch. If you're having problems with blocking, consider the read committed snapshot isolation.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Only if you don't mind deleting the wrong data, or getting your connection killed (normal behavior with nolock).

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ah! nice catch Gail.

Please don't cross post. It just wastes everyone's time and our answer won't change from one thread to another.

ashuthinks SSCommitted Points: 1698 More actions · Answer 4

i.m just using this stored procedure for reporting purpose..

please let me know my usage is at right or not?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

ashuthinks (11/16/2011)
i.m just using this stored procedure for reporting purpose..
please let me know my usage is at right or not?

Why do you NEED to use this?

It's NOT a go fast magic red button.

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

ashuthinks (11/16/2011)
i.m just using this stored procedure for reporting purpose..
please let me know my usage is at right or not?

Since when does reporting run deletes?

Why are you so determined to use this hint? It's not as if you're paid by the number of keywords or features that you use.

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

ashuthinks SSCommitted Points: 1698 More actions · Answer 7

many reports are accessing this stored procedure at the same time so TO AVOID DEADLOCK I'M USING THI 🙂

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

There's no deadlocks on selects.

Reports should have to delete anything in permanant tables.

Why do you need to delete the base tables' data for that report?

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

ashuthinks (11/16/2011)
many reports are accessing this stored procedure at the same time so TO AVOID DEADLOCK I'M USING THI 🙂

So, if you don't use it you are definitely getting deadlocks? Or are you guessing that you might?

Deadlocks are completely fixable (except in one or 2 really odd edge cases) with tuning code and modifying indexes.

p.s. Please don't shout at me.

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

ashuthinks SSCommitted Points: 1698 More actions · Answer 10

its a huge long store procedure depending upon conditions

i insert data , update data and delete data from tables..

what i have posted is just a sample code of SP.

but i'm not sure about the space of WITH(NOLOCK) into above delete block

so thats why i posted a question .

NOLOCK is working with select only and as delete block is having select statement

so i wonder that I can use it or not ?

ashuthinks SSCommitted Points: 1698 More actions · Answer 11

@gila yes sir...

i knew that if i don't use NOLOCK i will definitely get a deadlock so for successful

execution i need to use NOLOCK

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

ashuthinks (11/16/2011)
@gila yes sir...
i knew that if i don't use NOLOCK i will definitely get a deadlock so for successful
execution i need to use NOLOCK

It's a mam actually..

...backs away slowly 😉

ashuthinks SSCommitted Points: 1698 More actions · Answer 13

ashuthinks

SSCommitted

Points: 1698

November 16, 2011 at 8:11 am

#1408578

OPPS 😛

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

ashuthinks (11/16/2011)
its a huge long store procedure depending upon conditions
i insert data , update data and delete data from tables..
what i have posted is just a sample code of SP.
but i'm not sure about the space of WITH(NOLOCK) into above delete block
so thats why i posted a question .
NOLOCK is working with select only and as delete block is having select statement
so i wonder that I can use it or not ?

That's why temp table were invented. You can't get deadlocks there.

Also it avoids the collision between multiple reports messing with one another's data.