Re-creatable deadlock on index - rebuild index does not solve

Question

Post reply

Re-creatable deadlock on index - rebuild index does not solve

scott_lotus

SSCommitted

Points: 1504
More actions
September 29, 2010 at 1:07 am

#142098

Morning,
I'm seeing a re-creatable deadlock on an index occur during the execution of a query used for reporting.
Using the profiler for deadlock monitoring , the resulting graphical display shows:
(Server Process details1) REQUEST MODE: S --> INDEX NAME: Device_PK -- > OWNER MODE X (Server process details2)
(Server Process details1) OWNER MODE: U <-- INDEX: Device_Veh <-- REQUEST MODE: X (Server process details2)
From my limited understanding:
Server process 1 requests "shared lock" on index:Device_PK then exclusive lock on server process 2 (which it owns)
Server process 2 requests "exclusive lock" on index:Device_Veh then update lock on server process1.
i.e its deadlocking when server process 2 request exclusive lock and cannot get it due to another exclusive lock.
Troubleshooting:
Rebuild index Device_Veh did not resolve.
Rebuild index Device_PK did not resolve (didnt expect this to but was worth a go).
Must be a specific record in the index as amending the query parameters causes the query used for reporting to run ok.
1) can anyone explain the above chart ?
2) can anyone suggest other fixes ?
Guess i could drop the index and recreate.
Thank you
Scott

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

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

scott_lotus SSCommitted Points: 1504 More actions · Answer 1

Disabled the problematic index and created a new one. Re-ran the query with no issues. Will continue to monitor.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

removed

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

If the query is for reporting only, why would it be taking out an exclusive lock on the index? Are you sure that the query isn't doing some type of write?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

scott_lotus SSCommitted Points: 1504 More actions · Answer 4

That's a good point. Im sure the query is SELECT only. Added (NOLOCK) to the larger tables used but not the table in question.

1) I thought SELECTS exclusively lock by default in SQL 2005. Wouldn't that account for the X lock explained above ?

2) Also , if i add a (NOLOCK) to the problematic table in the query in question, i assume the (NOLOCK) is applied to the index it uses ? (i.e do i have to specify the index and then query hint (NOLOCK) on the index too ? dont expect this is the case , just wanted to double check).

Thanks for the response.

Scott

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

scott_lotus (9/29/2010)
That's a good point. Im sure the query is SELECT only. Added (NOLOCK) to the larger tables used but not the table in question.
1) I thought SELECTS exclusively lock by default in SQL 2005. Wouldn't that account for the X lock explained above ?
2) Also , if i add a (NOLOCK) to the problematic table in the query in question, i assume the (NOLOCK) is applied to the index it uses ? (i.e do i have to specify the index and then query hint (NOLOCK) on the index too ? dont expect this is the case , just wanted to double check).
Thanks for the response.
Scott

I'm not a fan of putting NOLOCK on queries, but, no you don't need to specify the index, it just doesn't put locks on.

SELECT statements take out a lock, yeah, but not an exclusive one... unless. What's your isolation level on the connection? Is it by chance Serializable? That might explain things. Also, that would mean your connection & the NOLOCKS are fighting one another.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

scott_lotus SSCommitted Points: 1504 More actions · Answer 6

I agree that NOLOCK on queries are not ideal but its saved show stopping performance issues on several occasions and i have not found anything else to date that can replace it.

"SELECT statements take out a lock, yeah, but not an exclusive one... unless. What's your isolation level on the connection? Is it by chance Serializable? That might explain things. Also, that would mean your connection & the NOLOCKS are fighting one another"

Struggling to find the isolation level sorry. Select snapshot_isolation_state_desc from sys.databases says OFF. Suspect it will be at the default which i believe is READ COMMITTED.

Thank you again for the reply.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

Yes, READ COMMITTED is the default. Well, I'm stumped then. Something that is just a SELECT shouldn't be grabbing exclusive locks, so the best thing I can suggest is seeing what else is happening on the system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

scott_lotus SSCommitted Points: 1504 More actions · Answer 8

Grant Fritchey (9/29/2010)
Yes, READ COMMITTED is the default. Well, I'm stumped then. Something that is just a SELECT shouldn't be grabbing exclusive locks, so the best thing I can suggest is seeing what else is happening on the system.

Thanks again for the reply Grant. I think it must be the table mentioned above , didn't apply NOLOCK to it as has a low number or records. Have amended now and will see what happens.

While i have your attention would you mind if i asked quickly:

1) In SQL 2008 i understand READUNCOMMITTED is the equivalent on NOLOCK. When replacing NOLOCK with READUNCOMMITTED in SQL 2005 however i have the same performance issues i was avoiding with NOLOCK. Hoping that when migrating to SQL 2008 READUNCOMMITTED will act as NOLOCK does on SQL 2005. Are you aware if this is the case ?

2) Regarding ISOLATION LEVELS. How do they differ from LOCK HINTS on a query ? can you quickly explain ISOLATION LEVELS in layman's terms ?

Best regards

Scott

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

There is a good chance that setting your database to read_committed_snapshot (row version isolation) will resolve your deadlock issues, and will eliminate most blocks created by long running selects without having to deal with the problems caused by NOLOCK.

I have used it serveral times to eliminate very serious deadlocking problems.

Using Row Versioning-based Isolation Levels

http://msdn.microsoft.com/en-us/library/ms179599(v=SQL.90).aspx

Using Snapshot Isolation

http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx

alter database MyDatabase set allow_snapshot_isolation on;

alter database MyDatabase set read_committed_snapshot on;

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

Yeah, late to the game, I was going to suggest READ COMMITTED SNAPSHOT as well.

Isolation levels are very different from locking hints. Isolation levels define how transactions will behave within the system, which in turn defines, some, locking mechanisms.

In your case, I would have expected READ UNCOMITTED to behave, mostly, identical to NOLOCK. If you're seeing something different, I'd need to see the query and the structures to understand why there would be a difference.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning