April 28, 2009 at 12:30 pm
In BOL (Customizing Locking for an Index) click here, there is a blub (between the disallowed locks graph and the important notice) that states:
For example, when a table is known to be a point of contention, it can be beneficial to disallow page-level locks, thereby allowing only row-level locks. Or, if table scans are always used to access an index or table, disallowing page-level and row-level locks can help by allowing only table-level locks.
Today, we were having some deadlocking issues on our server. When we identified which queries were involved, one of the dbas pulled up the tables and checked that appropriate indexes were on the tables. While we did have the index, it was with the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options set to ON. The index was altered and these options set to OFF, and immediately the deadlocks disappeared.
One of the indexes that was changed is:
ALTER TABLE [dbo].[xyz] ADD CONSTRAINT [PK_xyz] PRIMARY KEY CLUSTERED ([order_request_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 80) ON [PRIMARY]
Now, what was done seems counter-intuitive. It seems that by not allowing row/page locks, we would be forcing locks at the table level, and that this would lead to more deadlocks.
So, would anyone care to help me understand this?
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 28, 2009 at 12:56 pm
Deadlocks are caused by resource conflicts that can't be resolved by the engine.
If, for example, you allow row locks, and process A locks row 3, and needs row 5, but process B has locked row 5 and needs row 3, then you'll have a deadlock. If, on the other hand, process A locks the whole table, accesses row 3 and needs row 5, then process B has to wait till process A is completely done before it can access row 5, much less lock it. That would be why your deadlocks disappeared.
Deadlocks are almost always caused by code that was written without considering resource locks. The best solution is almost always to make resources be accessed in the same order in each piece of code, but that's not always possible. Almost always, but not always.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 1:34 pm
This makes sense.
During the interim, I've been looking at the procs that were causing the locking issues. I found a proc that runs one of three select statements with joins between 32 tables. Looking at the execution plan, I see only a few seeks, and many clustered index (aka table) scans and index scans. This would also explain why turning off the row/page locks helped with the deadlocks.
Side note: wouldn't it be better to have those 3 different select statements to be in separate procs, with this proc controlling which one gets run?
Edit: added info about 3 selects and side note.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 28, 2009 at 1:35 pm
That one sounds like a serious candidate for optimization! 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 1:59 pm
WayneS (4/28/2009)
Side note: wouldn't it be better to have those 3 different select statements to be in separate procs, with this proc controlling which one gets run?
Quite possibly.
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
April 28, 2009 at 2:29 pm
Side note: wouldn't it be better to have those 3 different select statements to be in separate procs, with this proc controlling which one gets run?
That is what I would do. But if you could possibly do the selection at the client-side it would be even better.
I understand that changing the client code is not always an option but I am just offering it just in case.
* Noel
April 28, 2009 at 3:04 pm
noeld (4/28/2009)
Side note: wouldn't it be better to have those 3 different select statements to be in separate procs, with this proc controlling which one gets run?
That is what I would do. But if you could possibly do the selection at the client-side it would be even better.
I understand that changing the client code is not always an option but I am just offering it just in case.
It would be a lot easier to just change the proc into 4 - (the original) one with the if...elseif...elseif logic, and the other 3 with the separate selects. This way, the existing program doesn't need to be changed, and it won't notice the difference.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 28, 2009 at 3:10 pm
WayneS (4/28/2009)
noeld (4/28/2009)
Side note: wouldn't it be better to have those 3 different select statements to be in separate procs, with this proc controlling which one gets run?
That is what I would do. But if you could possibly do the selection at the client-side it would be even better.
I understand that changing the client code is not always an option but I am just offering it just in case.
It would be a lot easier to just change the proc into 4 - (the original) one with the if...elseif...elseif logic, and the other 3 with the separate selects. This way, the existing program doesn't need to be changed, and it won't notice the difference.
Right I do know that and totally agree that it is the minimum "change". But calling a PROC from a PROC is an overhead that the client(S) could assume. From scaleability point of view the if..else if..else if..else.. are much better done at client-side although they do require extra programming effort.
* Noel
April 28, 2009 at 3:29 pm
I'm trying to set up a deadlock event as outlined in BOL. The job gets created. When I run the supplied code, I do end up getting the deadlock. However, the job never fires.
I did verify that both databases are set up for service broker:
select * from sys.databases where is_broker_enabled = 1 shows both msdb and AdventureWorks.
Any ideas of why the job never fires?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 29, 2009 at 3:34 am
Hey Wayne,
I hope the previous responders won't mind if I add my two cents:
WayneS (4/28/2009)
When we identified which queries were involved, one of the dbas pulled up the tables and checked that appropriate indexes were on the tables. While we did have the index, it was with the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options set to ON. The index was altered and these options set to OFF, and immediately the deadlocks disappeared.
It would be great if you could provide more details on the queries and tables involved in the deadlock.
Usually deadlocks involving concurrent UPDATE and SELECT activity can be minimized by accessing resources in the same order, having useful and/or covering indexes, using the appropriate isolation level, or (in the last resort) by using appropriate locking and granularity hints like UPDLOCK and ROWLOCK. If the obejct in question is a queue, also take a look at READPAST. That's just general advice - I think most people would need to see the system and queries in detail before settling on an approach.
Note that the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS only apply to the specific index they are set on. In your case they were set on the clustered index. This disables row and page locking for the entire table - only non-clustered indexes on the table would be able to use anything other than a table lock. In practice, this would mean that queries which were 'covered' by one or more non-clustered indexes could still use row- and page-level locks.
Disabling either row or page locks is a fairly rare occurrence, and disabling both is rarer still. Disabling both on the clustered index, well you could win a major lotto prize with odds like that.
There are very limited circumstances in which enforcing table locks on the clustered index is a good option. In fact, I am struggling to convince myself that there are any! With that in mind, I was a little surprised that your DBA took this route first...?
If the table in question was the only one involved in the deadlock, it is logical that the deadlocks ceased when you asked the engine to only perform table-level locking. Since only one process can have an exclusive table lock on the same object at the same time (except co-operating bulk inserts), this enforces serial access to the table for processes involved in INSERT, UPDATE or DELETE operations. This is obviously not so great from a concurrency point of view.
Have you considered enabling READ_COMMITTED_SNAPSHOT in this database? While not a pancea by any means, it is frequently a great solution for deadlocks caused by the interaction of readers and writers.
WayneS (4/28/2009)
I found a proc that runs one of three select statements with joins between 32 tables. Looking at the execution plan, I see only a few seeks, and many clustered index (aka table) scans and index scans.
Joins between 32 tables? Seriously?
Ordinarily I would say that the query should be broken down into sections, but 32 joins is just too much to ignore.
Something is seriously wrong there, and that probably needs fixing before too much more detailed analysis is done.
Separating the SELECTs out into separate procedures is usually a good plan, but I think you have larger fish to fry first.
Denormalization can be your friend, you know... 🙂
Concerning the deadlock job thing you are trying to set up: is that really necessary? Would a profiler trace not be just as good? You can even save the XML query plans and deadlock graph to a file. Failing that, have you tried trace flag 1222?
See http://msdn.microsoft.com/en-us/library/ms188246(SQL.90).aspx for details on using profiler to analyze deadlocks.
Cheers,
Paul
April 29, 2009 at 7:07 am
WayneS (4/28/2009)
I'm trying to set up a deadlock event as outlined in BOL. The job gets created. When I run the supplied code, I do end up getting the deadlock. However, the job never fires.
Offhand, absolutely no idea. I usually just use the traceflags and pull the deadlock graph from the error log. The results from traceflag 1222 are pretty easy to read and I usually find it's quicker (for me) to go over that than to use the graphical deadlock graph.
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
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply