Dead Lock

Question

Post reply

Dead Lock

Loner

SSC-Insane

Points: 21279
More actions
July 2, 2007 at 10:47 am

#117689

I have a view and it has
select a, b, c from tableA
UNION ALL
select a, b, c from tableB
UNION ALL
select a, b, c from tableC
What really surprised me is there are two queries using this view and they ran into a deadlock. How can it be?
Is it some wrong in our configuration and it is standard.
Lock min 500 max 2147483647
Should I put in SET DEADLOCK_PRIORITY LOW?
This happens when two different sources tried to insert record into the same table.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

Is the call to the view in an explicit transaction that updated one of the tables in the view?

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

Loner SSC-Insane Points: 21279 More actions · Answer 2

No none of the call updates any table. They are all Select statements. That's why I did not understand why a deadlock would happen.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Check what is the type of lock held by the blocking SPID. You can set your isolation level to read committed so that select statements can avoid locks.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Interesting...I've been meaning to ask this for some time.

When querying a database for reporting purposes, I'll almost always use the NOLOCK clause (read uncommitted) because it's generally quicker, sometimes very much so.

I understand this is because it prevents dropping in read locks and reads uncommitted data so doesn't hold up transactions and isn't held up by pending transactions. I've not come across a situation yet where uncommitted data has screwed up the print job but appreciate that sooner or later it may happen, when a transaction (the changed data of which has already been read by my reporting query) is rolled back.

Am I right in my understanding of this? What's the general concensus on using the NOLOCK clause for reporting?

Loner, I hope you don't mind me asking this, and perhaps you could try the NOLOCK clause yourself - provided that you are sure your output won't be skewed by uncommitted data.

Many thanks

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

With read uncommited isolation level there is a possibility to read dirty reads which can be avoided with read commited level. But the more the level you use the higher is the locking possibility. So plan before you use an isolation level. You can have a good and easy reading of ISOLATION Levels @

http://www.expresscomputeronline.com/20040426/techspace01.shtml

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

There may be some normal "healthy" blocking going on... you sure these are "deadlocks" (found in the SQL Server log) and not just blocking?

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