Partager via

SharePoint 2007 BDC Profile Import – SQL Deadlock Exceptions

  • Article

If you use the BDC to import user profile data, or if you use multiple threads to update the profile database using the profile APIs, you may have encountered SQL deadlocks if you’re trying to update the manager property of the profile.

The issue can be traced down to a couple of stored procedures:

profile_UpdateUserProfileData – Initiates a transaction and updates the user profile table numerous times based on a number of properties passed to it. If manager is updated, this code block runs:

 IF @PropertyID = @PROPERTY_MANAGER_ID

BEGIN

UPDATE UserProfile SET Manager = CAST(@PropertyValue AS NVARCHAR(400)) WHERE RecordID = @RecordID

exec dbo.QuickLinksUpdateOrgContacts @RecordID, 1

END

Which calls the QuickLinksUpdateOrgContacts procedure which also attempts to read and update the UserProfile table. This procedure is causing the issues due to reading from the UserProfile table then updating the table within the scope of the transaction started by the profile_UpdateUserProfileData procedure. What is occurring is a lock escalation from the shared lock created during the read to an exclusive lock for the update because the select and update SQL statements are occurring within a transaction. If this occurs across multiple SQL processes, a deadlock can occur.

A couple of observations:

  • The AD import process take a different code path than BDC import when updating the profile database and does not encounter the deadlocking issue on either a full or incremental import when manger is updated in AD. Updating the manager property on every account in AD has no consequence and does not reproduce the deadlock.
  • Attempting to update the manager property with the BDC consistently reproduces the issue when the crawler is set to use more than one thread. Full or incremental import both produce the same deadlocking result.
  • The deadlocking via BDC import only occurs when the manager property has changed. If the database being imported has the same manger value as the profile db, the stored procedure does not attempt an update (and subsequent lock escalation) which avoids the deadlock
  • When a deadlock occurs, the record in question is not updated. There is no retry logic. However, the rest of the records in the update set are processed (at least attempted).
  • The API exhibits the same behavior as the BDC. Updating the Manager property using multi-threaded logic will reproduce the behavior explained above. Again, the deadlock only occurs if the property value being set is different than what is already in the profile database. Running the same update logic on a single thread avoids the deadlocking.

So, how do you avoid this? Well you have a few options:

Hope this helps!

Technorati Tags: SharePoint 2007,BDC,Business Data Catalog,Deadlock,User Profile Import,profile_UpdateUserProfile