Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dead Lock Issue -In update Scenario Expand / Collapse
Author
Message
Posted Thursday, May 16, 2013 3:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:37 AM
Points: 36, Visits: 122
HI ,

I have a Dataflowtask in which following following Transformations are used
---OLEDB Source[Pulls Data From X StagingTable]
---Balance data distributor is used [For Parallel Execution]
---CRM Destination is used for Inserts/Updates data into Y Target Table
---Conditional Split[On >0] exists.

Case 1 [>0-Error]:Updates Table X's Row as Unsuccesful
Case 2[<=0-No Error]:Updates Table X's Row as Succesful

While Peforming above scenario Iam getting Dead Lock issue .

"Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005 Description: "Transaction (Process ID 82) was deadlocked on
lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

I belive this is due to Update operation performed on table
Kindly request you to help me in this regard.
Post #1453386
Posted Thursday, May 16, 2013 3:36 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 4,986, Visits: 11,684
What is 'CRM destination'? Is this a custom component?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1453389
Posted Thursday, May 16, 2013 4:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:37 AM
Points: 36, Visits: 122
Its a Third party component [Kingsway]

CRM DESTINATION ADAPTER [inserts data into Microsft CRM Dynamics]
Post #1453407
Posted Thursday, May 16, 2013 4:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 4,986, Visits: 11,684
greeshatu (5/16/2013)
Its a Third party component [Kingsway]

CRM DESTINATION ADAPTER [inserts data into Microsft CRM Dynamics]


If that's where the deadlocks are happening, can you ask them for support?

It would be difficult for most people here to help you, given that this is not a standard component.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1453412
Posted Thursday, May 16, 2013 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:37 AM
Points: 36, Visits: 122
Dead lock is not happening in Third part object CRM Destination adapter

.Dead lock is Occuring in OLEDB Commmand's defined after Conditional Split where in Update Query on Staging table is coded .
Post #1453456
Posted Friday, May 17, 2013 11:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:03 PM
Points: 32, Visits: 307
greeshatu (5/16/2013)
HI ,

I have a Dataflowtask in which following following Transformations are used
---OLEDB Source[Pulls Data From X StagingTable]
---Balance data distributor is used [For Parallel Execution]
---CRM Destination is used for Inserts/Updates data into Y Target Table
---Conditional Split[On >0] exists.

Case 1 [>0-Error]:Updates Table X's Row as Unsuccesful
Case 2[<=0-No Error]:Updates Table X's Row as Succesful


While Peforming above scenario Iam getting Dead Lock issue .

"Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005 Description: "Transaction (Process ID 82) was deadlocked on
lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

I belive this is due to Update operation performed on table
Kindly request you to help me in this regard.


Please see the parts I bolded in your explanation. Table X is the source. Is Table X also being updated? If YES, has the SELECT from Table X completed before said UPDATE? If not you may want to add an asynchronous component before your update to ensure the SELECT from X has completed before Updating X.
Post #1454134
Posted Monday, May 20, 2013 1:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:37 AM
Points: 36, Visits: 122
Select and Update are running paralelly .. this is because I have used Balance data distributor to run the tasks parallely .Which asyncronous transformations is helpful in this case.kindly suggest
Post #1454426
Posted Monday, May 20, 2013 2:15 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 4,986, Visits: 11,684
greeshatu (5/20/2013)
Select and Update are running paralelly .. this is because I have used Balance data distributor to run the tasks parallely .Which asyncronous transformations is helpful in this case.kindly suggest


You might be better off selecting to a Raw file and updating from that - to ensure that you avoid contention issues.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1454429
Posted Monday, May 20, 2013 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:03 PM
Points: 32, Visits: 307
Phil Parkin (5/20/2013)
greeshatu (5/20/2013)
Select and Update are running paralelly .. this is because I have used Balance data distributor to run the tasks parallely .Which asyncronous transformations is helpful in this case.kindly suggest


You might be better off selecting to a Raw file and updating from that - to ensure that you avoid contention issues.


Raw file is a great idea Phil! Probably better than an asynchronous component in most cases. That said, if your'e only dealing with a smaller data set, add a Sort component. It should not add too much time to the overall processing and should do the trick as well.

But I do like the Raw file very much!
Post #1454594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse