SQL deadlock problem

I am currently having a big issue with sql deadlocking on the 
PrincipalObjectAccess table. 

The last few months I have been working on a synchronization process using a 
Biztalk orchestration. The sync uses the crm webservices to create and update 
account and contact records in CRM. But now deployment to the production 
environment gives me some problems. It seems that when trying to update 
account records (which is one of the first actions in the sync process) the 
webservice gives me Generic SQL errors and SQL timeouts. After extensive 
profiling and tracing in SQL I found that there are two sql statements that 
are been called several times involving the PrincipalObjectAccess table 
(something to do with getting the account.owner and checking for inherited 
access). After going through this cycle several times the webservices throws 
the sql exception.  Tracing determined that there are deadlocks occuring on 
the PrincipalObjectAccess table. When continuing the syncing process 
eventually the worker thread crashes.

Some more info:
The CRM webclient seems to be working fine with updating and creating. 
Rollup 2 was installed but did not solve the problem. 
Development and test environment does not have this problem, but also does 
not have security roles in place. The account used for synchronizing has a 
administrator role. 
When skipping account updates, the next entity synced is contact. The same 
problem occurs here.. Other entities seem to sync ok ( when the worker thread 
did not crash before..)

Any ideas on where to look for solutions?
0
Daisy (15)
9/10/2007 6:46:01 AM
crm 35858 articles. 1 followers. Follow

3 Replies
784 Views

Similar Articles

[PageSpeed] 23

Just wondering if you have any callouts registered with the account
that actually use SQL to change the account permission directly from
the PrincipalObjectAccess table. If so, remove the callouts to see if
you are getting the same issue.

Darren Liu
Crowe
http://www.crowecrm.com

On Sep 10, 1:46 am, Daisy <Da...@discussions.microsoft.com> wrote:
> I am currently having a big issue with sql deadlocking on the
> PrincipalObjectAccess table.
>
> The last few months I have been working on a synchronization process using a
> Biztalk orchestration. The sync uses the crm webservices to create and update
> account and contact records in CRM. But now deployment to the production
> environment gives me some problems. It seems that when trying to update
> account records (which is one of the first actions in the sync process) the
> webservice gives me Generic SQL errors and SQL timeouts. After extensive
> profiling and tracing in SQL I found that there are two sql statements that
> are been called several times involving the PrincipalObjectAccess table
> (something to do with getting the account.owner and checking for inherited
> access). After going through this cycle several times the webservices throws
> the sql exception.  Tracing determined that there are deadlocks occuring on
> the PrincipalObjectAccess table. When continuing the syncing process
> eventually the worker thread crashes.
>
> Some more info:
> The CRM webclient seems to be working fine with updating and creating.
> Rollup 2 was installed but did not solve the problem.
> Development and test environment does not have this problem, but also does
> not have security roles in place. The account used for synchronizing has a
> administrator role.
> When skipping account updates, the next entity synced is contact. The same
> problem occurs here.. Other entities seem to sync ok ( when the worker thread
> did not crash before..)
>
> Any ideas on where to look for solutions?


0
Darren
9/10/2007 3:47:48 PM
no, I do not have such callouts. 
I do have callouts registered but if that was the problem I would expect the 
webclient to have issues as well. But in any case I tried synchronizing with 
the callouts turned off and this did nog help either. 

Any other ideas?

"Darren Liu" wrote:

> Just wondering if you have any callouts registered with the account
> that actually use SQL to change the account permission directly from
> the PrincipalObjectAccess table. If so, remove the callouts to see if
> you are getting the same issue.
> 
> Darren Liu
> Crowe
> http://www.crowecrm.com

0
Utf
9/12/2007 6:48:00 AM
I looked into my problem further.

It seems I am having issues with the crm webservice only. When I try to use 
the webservice to set the parentaccount of an account to null I am getting 
sql time outs exceptions. It seems sql deadlocks on the principalobjectaccess 
table and gets into a loop requesting and updateing values..

Using the crm web client I can set and unset the parentaccount without any 
problems although it also seems a bit slow. 



Any help is appreciated!
0
Utf
9/17/2007 2:34:05 PM
Reply:

Similar Artilces: