June 29, 2012 at 5:15 pm
One of our vendors is running an application that reads AMI meters on a 15min interval. The SP they've implemented is always deadlocking against itself (becomes entangled due to the multiple DML statements), as many as 50 at one time. While we're waiting for them to address the issue, are there any thoughts as to the best way to resolve the contention?
USE [CentralServices]
GO
/****** Object: StoredProcedure [dbo].[UpdEPCollectorAssociation] Script Date: 06/29/2012 16:11:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[UpdEPCollectorAssociation]
@EndpointId int,
@CollectorId int,
@startDatedatetime,
@startTimedatetime
AS
SET NOCOUNT ON
SET XACT_ABORT ON
begin TRANSACTION T1
UPDATE EndPoints SET prevSpuId = spuId, spuId = @CollectorId WHERE endpointId = @EndpointId
-- Delete any previous record from today
DELETE CollectorTransitionHistory WHERE endpointid = @endpointId AND dailyReadStartDate = @startDate
-- Close out the previous record
UPDATE CollectorTransitionHistory SET dailyReadEndDate = @startDate, endDate = @startTime
WHERE endpointId = @endpointId AND dailyReadEndDate IS NULL
-- Insert the new record
INSERT INTO CollectorTransitionHistory
(endpointId, collectorId, dailyReadStartDate, startDate)
VALUES (@endpointId, @collectorId, @startDate, @startTime)
COMMIT TRANSACTION T1
GRANT EXEC ON UpdEPCollectorAssociation TO WebAccess
Thx,
~Les
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
June 29, 2012 at 5:48 pm
I'd make sure that the table has the appropriate clustered index for one. And second, make the procedure serializable so that only one instance of the procedure can update the table at a time.
June 29, 2012 at 5:55 pm
Thanks Lynn. I'd already checked the index, and added 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'. I just posted the original SP code. Still no joy.
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
June 29, 2012 at 6:00 pm
I assume multiple calls against the same proc are what's causing the deadlocking, or is it deadlocking itself?
If it's multiple calls, you might try using an sp_applock wrapper in the proc with no timeouts so it's forced to wait against any other runs.
If it's a single call deadlock this can get ugly fast.
Edit: I should mention that my usual preference for dealing with queueing deadlocking multiple calls like this is Service Broker. You can use the applock as a poor man's queue until you get something permanent in place though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2012 at 6:17 pm
I believe it's multiple calls, though given the nature, I'm not positive.
I've never used sp_getapplock... but from what I've gleamed, I would copy the DML from this SP to another SP, then change this SP to call that SP using the sp_getapplock? ...or do I wrap this DML in sp_getapplock? (BOL's not real clear).
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
June 29, 2012 at 6:32 pm
Wrap this DML in an applock.
Basically:
CREATE PROC blah AS
sp_getapplock
Begin Tran
...
End Tran
sp_releaseapplock (or whatever it's called, that's close)
END
What that'll do is force a waitstate at the sp_getapplock until it can get the applock. It's configurable with timeouts and the like. Basically an applock is nothing more than a 'handled' counter internal in a table that basically exists or doesn't. Until it doesn't exist, another simultaneous call can't get the lock, so it'll sit there and spin until timeout or eternity until it does... or until whatever got it in the first place and is blocking it has its SPID closed. There's rules about how long an applock lasts that don't give it huge power but in this case it'll be just enough without being too much.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 30, 2012 at 1:34 am
Which columns has clustered and non clustered indexes on the CollectorTransitionHistory
table? What about the unique/PK?
The second update seems causing the issue also at the same time it looks like it is not doing much?
Final insert statement is putting the dailyReadEndDate as null. Then it is updated only in this proc next time the same proc is called with same endpoint but maybe any start time(I am assuming you do not have any other update /insert proc on this table).
One more thing is dailystrat startdate and dailyenddate are just the dates(means no time component)?
My doubt is on the update statement and thinks that there is no index on endpoint and dailyendrate and thus all the rows for endpoint are locked which in turn are stopping other same proc execution blocking?
Try to use the repeatbale read or read committed transaction levels (serializable is causing the deadlocks) if possible otherwise have proper indexes on the columns in where clause so that the data which is actuially getting changed is locked only.
e.g..
drop table a
go
create table a( id int ,col1 int,col2 char(4000)not null)
go
insert into a
select 1,1,'dsm'
union
select 1,2,'dsm'
union
select 1,3,'dsm'
union
select 1,4,'dsm'
union
select 1,5,'dsm'
union
select 2,1,'ds1m'
union
select 2,2,'ds1m'
go
create clustered index idx_id on a(id)
go
set transaction isolation level serializable
go
begin tran
update a set
col2 = 'msd'
where id = 1 and col1 = 4 -- no index on id and col1..Thus all 5 rows will be locked...This could cause serious issues..I will prefer read committed unless you have any other reason to use any other isolation level
select i.name,* from sys.dm_tran_locks dtc
inner join sys.partitions p
on dtc.resource_associated_entity_id = p.hobt_id
inner join sys.indexes i
on p.index_id= i.index_id
and p.object_id= i.object_id
where request_session_id=@@SPID
rollback tran
set transaction isolation level read committed
go
dbcc traceon (3604)
dbcc page(9,1,38490,3)
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
June 30, 2012 at 1:28 pm
G...
Here's the DDL...
USE [CentralServices]
GO
/****** Object: Table [dbo].[CollectorTransitionHistory] Script Date: 06/30/2012 12:24:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CollectorTransitionHistory](
[endpointId] [int] NOT NULL,
[collectorId] [int] NOT NULL,
[dailyReadStartDate] [smalldatetime] NOT NULL,
[startDate] [smalldatetime] NOT NULL,
[dailyReadEndDate] [smalldatetime] NULL,
[endDate] [smalldatetime] NULL,
CONSTRAINT [PKC_CltrTransHist_epIdcltrId] PRIMARY KEY CLUSTERED
(
[dailyReadStartDate] ASC,
[endpointId] ASC,
[collectorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
I can modify the existing SP, but can't alter the calling code. So, I can relegate this to anther procedure (different name), and call it with a SP of this name, or alter the existing... until the Vendor delivers a fix. At the current rate of escalation, I'm sure the data is not complete/correct, or is nearing that state. We've only installed 1/3 of the meters, and we're already experiencing hundreds of deadlocks daily.
Thx,
~Les
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
June 30, 2012 at 1:51 pm
Looks like the indexes are issues here.
The delete statement is using clustered index but all index keys are not part of it.Thus all the rows which has dailystartdate =@startdate will have the locks while only the one for a particular endpoint will be deleted.Thus for the same @startdate will have to wait...
I am not sure about exact logic of your application but I could guess that the delete statement should have a collectorid as well because the delete statement is deleting the data for all collector for a given startdate and endpoint. This seems kind of strange to me.
Issue for the update is even worse..I am not sure whether you have index on endpoint or not but still it will lock even more rows but the rows are already deleted at least for startdate provided and thus it will lock the data for other startdates. Thus it could cause more blocking and deadlocks.
I am assuming that there is index on endpoint column,if this index doesnt exists then whole table is locked.This could be one of the major issue then.
Seriously,To me this update also doesnt make sense either because dailyendrate is null for all the values and most of them might have been deleted earlier.
Thus most of deadlocks must be happening because first session acuiqred lock on deletes, and updates and want to acquire lock to insert the data (which might be held by another session) while second session got the locks for delete statement but can not get the locks for update statements this must be happening mostly if both of these session uses same endpointid..
Thus better indexes should be able to resolve the deadlock issue. But I am somewhat confused about the way the proc is deletingupdating and inserting data .It doesnt make much sense to me but you should be in better position to know that
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
June 30, 2012 at 2:08 pm
Evil Kraig F (6/29/2012)
Wrap this DML in an applock.Basically:
CREATE PROC blah AS
sp_getapplock
Begin Tran
...
End Tran
sp_releaseapplock (or whatever it's called, that's close)
END
Hmmm...no joy. No matter where I put 'sp_getapplock' in the DDL, it chokes. There are also no similar examples in BOL, only...
USE AdventureWorks2008R2;
GO
EXEC sp_getapplock @DbPrincipal = 'dbo', @Resource = 'Form1',
@LockMode = 'Shared';
EXEC sp_releaseapplock @DbPrincipal = 'dbo', @Resource = 'Form1';
GO
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
June 30, 2012 at 4:59 pm
G...
Looks like the indexes are issues here.
The delete statement is using clustered index but all index keys are not part of it.Thus all the rows which has dailystartdate =@startdate will have the locks while only the one for a particular endpoint will be deleted.Thus for the same @startdate will have to wait...
I added two indexes to the CollectorTransitionHistory table, based on the predicates. The first statement is against the Endpoints table, and the EndpointID is the PK (clustered), so it's probably not the culprit.
I am not sure about exact logic of your application but I could guess that the delete statement should have a collectorid as well because the delete statement is deleting the data for all collector for a given startdate and endpoint. This seems kind of strange to me.
<...>
Thus better indexes should be able to resolve the deadlock issue. But I am somewhat confused about the way the proc is deletingupdating and inserting data .It doesnt make much sense to me but you should be in better position to know that
I get what they're doing and why, I just don't agree with the methodology.
Thx
~Les
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
June 30, 2012 at 6:10 pm
Les Cardwell (6/30/2012)
Hmmm...no joy. No matter where I put 'sp_getapplock' in the DDL, it chokes. There are also no similar examples in BOL, only...
USE AdventureWorks2008R2;
GO
EXEC sp_getapplock @DbPrincipal = 'dbo', @Resource = 'Form1',
@LockMode = 'Shared';
EXEC sp_releaseapplock @DbPrincipal = 'dbo', @Resource = 'Form1';
GO
Please define 'chokes'. You get an error message?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 30, 2012 at 11:14 pm
I added two indexes to the CollectorTransitionHistory table, based on the predicates. The first statement is against the Endpoints table, and the EndpointID is the PK (clustered), so it's probably not the culprit.
Not the very first statement. I am taking about the second(delete),3rd(update) ,4th statements(insert).
Related to indexes ,delete statement has the first column of the clustered index key as well as 3rd column. Thus it might be using clustered index instead of non clustered index defined on say endpoint and dailystartdate based on estimates of rows it is making. Clustered index usage will lock much more rows than nonclustered index.
The 3rd statement i.e. update is happening based on the endpoint and you can have many rows for the endpoint and thus actually lots of rows might be updated by that statement and thus locking lots of rows which might be blocking others.Also if you have index on the endpoint and dailyendrate then during update the index key might move within index and this could be another reason for contention.
But all of above can be validated based on the actual plan for these statements,if possible deadlock graph as well?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 1, 2012 at 12:22 pm
>>Please define 'chokes'. You get an error message?
Msg 102, Level 15, State 1, Procedure UpdEPCollectorAssociation, Line 15
Incorrect syntax near 'sp_getapplock'.
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
July 1, 2012 at 12:47 pm
Gullimeel (6/30/2012)
Related to indexes ,delete statement has the first column of the clustered index key as well as 3rd column. Thus it might be using clustered index instead of non clustered index defined on say endpoint and dailystartdate based on estimates of rows it is making. Clustered index usage will lock much more rows than nonclustered index.The 3rd statement i.e. update is happening based on the endpoint and you can have many rows for the endpoint and thus actually lots of rows might be updated by that statement and thus locking lots of rows which might be blocking others.Also if you have index on the endpoint and dailyendrate then during update the index key might move within index and this could be another reason for contention.
I haven't run into that before. So, given the clustered PK on the first three columns, because it's also selecting on another column besides a column in the PK, it may be locking others because it alters conceptual uniqueness? Interesting.
But all of above can be validated based on the actual plan for these statements,if possible deadlock graph as well?
Most of my auditing info comes from Spotlight, including the graphs. Are you refering to one that can be obtained via SSMS? AAR, I've attached the query plan. You're right...it looks a tad less than efficient.
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply