locked
Stagin stored procedure deadlock RRS feed

  • Question

  • Hello ,

    We have SSIS packages to import/load data to master data tables.We have SQL jobs scheduled for this task.

    Since, there are two different packages which executes loading stored procedure (i.e [stg].[udp_entityname_Leaf]) deadlock occurs stating error as :

    Error message : Executing the query "exec [stg].[udp_Entityname_Leaf]  @Versio..." failed with the following error: "Transaction (Process ID 85) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Because of which , I can see some data loss also.

    Kindly help me out with this situation.

    Is there anything we can do to this SP to Lock it when in use? but since it is product SP I haven't modified anything.



    Thanks & Regards, Sagar

    Tuesday, May 12, 2015 5:45 AM

Answers

  • You will have enable trace flags indicated by Dinesh (above) and figure out what statements are ending-up with deadlock, we need both statements (The one that failed and the one that succeeded)

    Meanwhile, can you check whether the queries written in your stored procedure are hitting any of these indexes and if not then can you create an index to help optimizer prepare another access plan that may potentially resolve your issue

    If you can send us the statement that are colliding then we could offer more useful tips to you

    HTH


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Proposed as answer by Charlie Liao Tuesday, May 26, 2015 6:23 AM
    • Marked as answer by Charlie Liao Wednesday, June 10, 2015 7:00 AM
    Tuesday, May 12, 2015 7:06 AM
  • Hi Sagar,

    I belive indexing can help you.

    Please add index to all the system generated columns which will be updated during execution of this SP.

    There won't be an issue for deadlock.

    Since it's product Stored procedure please be careful while adding index to columns.

    • Proposed as answer by Charlie Liao Tuesday, May 26, 2015 6:23 AM
    • Marked as answer by Charlie Liao Wednesday, June 10, 2015 7:00 AM
    Wednesday, May 13, 2015 7:17 AM

All replies

  • Query:- DBcc traceon(1222,1204,-1)  keep deadlock details in sql agent will give clear idea.

    http://www.java2s.com/Tutorial/SQLServer/0020__Query/UsingTableHintsWITHNOLOCK.htm

    http://www.bradleyschacht.com/ssis-2012-deadlock-when-running-ssis-from-sql-agent-job/

    Tuesday, May 12, 2015 5:58 AM
    Answerer
  • Hello ! Can you show us the full source code for the procedure udp_Entityname_Leaf along with the tables touched by this stored procedure, their size and index details:

    For Table size: You can use exec sp_spaceused 'tablename'

    For Index details: You can use exec sp_helpindex 'tablename'



    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Tuesday, May 12, 2015 6:05 AM
  • This is  MDS product stored procedure, I am just using it in my SSIS packages to load data from staging to MDM tables. But since 2 different packages calls this SP at same time, one process gets terminated due to deadlock. 

    I unable to provide to that SP ssince it is too big and exceeding message body length.


    Space information :

    

    Index information :


    Thanks & Regards, Sagar

    Tuesday, May 12, 2015 6:58 AM
  • You will have enable trace flags indicated by Dinesh (above) and figure out what statements are ending-up with deadlock, we need both statements (The one that failed and the one that succeeded)

    Meanwhile, can you check whether the queries written in your stored procedure are hitting any of these indexes and if not then can you create an index to help optimizer prepare another access plan that may potentially resolve your issue

    If you can send us the statement that are colliding then we could offer more useful tips to you

    HTH


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Proposed as answer by Charlie Liao Tuesday, May 26, 2015 6:23 AM
    • Marked as answer by Charlie Liao Wednesday, June 10, 2015 7:00 AM
    Tuesday, May 12, 2015 7:06 AM
  • Hi Sagar,

    I belive indexing can help you.

    Please add index to all the system generated columns which will be updated during execution of this SP.

    There won't be an issue for deadlock.

    Since it's product Stored procedure please be careful while adding index to columns.

    • Proposed as answer by Charlie Liao Tuesday, May 26, 2015 6:23 AM
    • Marked as answer by Charlie Liao Wednesday, June 10, 2015 7:00 AM
    Wednesday, May 13, 2015 7:17 AM