Contentious SP

  • 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

  • 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.

  • 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

  • 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.


    - Craig Farrell

    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

  • 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

  • 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.


    - Craig Farrell

    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

  • 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]

  • 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

  • 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]

  • 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

  • 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

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


    - Craig Farrell

    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

  • 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]

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

  • 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