queries with (nolock) still runs into deadlock with others

Question

queries with (nolock) still runs into deadlock with others

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
January 28, 2008 at 11:37 am

#183416

I am running profiler to capture deadlocks. I see select quries that has (nolock) on each table, but it still runs into deadlock with other queries, could someone please tell me why is the case?
Thanks much!
Kathleen

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

What kind of queries are they? If they're deadlocking, I assume some form of data modification (since readers don't block readers) Data modification (insert, update, delete) will always take locks. Nolock only applies to select statements.

Do you know what the queries are that are deadlocking? If not, turn traceflag 1204 on (DBCC TRACEON (1204,-1)) and the deadlock graph will get written into the error log. You can examine that to see what the two processes involved in the deadlock were doing and what respources they were deadlocked over.

If you need help interpreting the graph, or seeing the cause of the deadlocks, post the graph and the queries here and we'll take a look

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

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 2

Thanks Gail!

I am running profiler to capture the deadlock chains and deadlock graphs.

One of the processes selects bunch of columns from joined tables, each of them has (nolock) as the table hint; the other process is a stored procedure that runs as one transaction that first disable the triggers, then insert a record into other tables, and delete the record from the current table. The second process uses tables in the first process. I am seeing Sch-s and Sch-M locks on these two processes. Aren't they compatible?

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

KATHLEEN Y ZHANG (1/28/2008)
I am seeing Sch-s and Sch-M locks on these two processes. Aren't they compatible?

No. They're the equivalent of shared and exclusive locks for the table structure. Sch-S is a schema stability lock. It's used to prevent schema changes. Sch-M is a schema modification lock. Taken when a process wants to change the schema

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

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 4

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534

January 28, 2008 at 1:18 pm

#772617

Oops! I guess they are in-compatible.

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 5

So when I add (nolock) to the tables, each of them get a sch-s lock?

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

I think you'll get schema stability no matter what. The query has to ensure that the table isn't changing while the query's running.

Why's it necessary in your second process to disable the triggers before adding a record?

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

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 7

This is what I found:

Schema stability (Sch-S) locks are used when compiling queries.

I think because our select query is embedded in asp, so it gets compiled each time it runs, thats why sch-s is granted on it.

The reason we disable triggers is because there are a lot of logic in the triggers that we do not want them to run.

Thanks!

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

Isn't the purpose of a trigger to be run every time? If you have to continually disable triggers, are you sure you're not missing what a trigger is built for? In that case - would it not be better to re-write what the triggers do as something else?

It just seems strange that you'd have ASP pages (read - internet, read - anonymous acces, read - not easy to adequately secure) messing with your database schema. That sounds like a security flaw just waiting to blow up in your face, espcially if you're dealing with "classic" ASP, which has no encryption/securing mechanism worth speaking of....

----------------------------------------------------------------------------------
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?

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 9

The triggers run most of the time, this is an exception that the tiggers cannot run.

The asp code is not messing with the schema, but it has has a select query embedded, that, unlike a stored procedure, gets compiled each time it runs, the compiling puts sch-s locks on the tables selected.