System.Data.SqlClient.SqlException - (Deadlock)

DEEWR have 4 target FIM environments (construction=>test=>preprod=>prod) and this problem has only been detected in PREPROD, for which one of several environment variations happens to be a clustered SQL2008 instance (where MSDTC has only just been enabled when it was identified as a missing IdB 3.0.6 prerequisite). The apparent deadlock problem is occurring only after requests for a full import of one of the 2 adapters (this one is called ADW which is an organisational role hierarchy).

While the DEEWR DBAs and developers are investigating this problem (e.g. running SQL profiler), is there anything else that you suggest might be causing this problem?

I can't speak for the IdB database, but a well-known record locking problem with the FIM sync server database is the performing of ad-hoc queries on this database without specifying "with (nolock)". I don't know if this is the first time you've ever seen this problem, and it could be some SQL maintenance process that is causing the locking - in which case we'll find that out from the trace I guess (and a "list locks").

Bob, please see PRDGRP:Support.

What version of Identity Broker is in place?

I believe the same issue has already been resolved, please see IDB-107 for a discussion.

Please see SUBIDB:Downloads for Patch 1 and Patch 2 for v3.0.6.1.

Checked versions and found that we are using version 3.0.6.1 Patch 1 in DEV (construction) but Patch 2 in PPROD. Trevor explained that he noticed the latest patch and downloaded it and ran with it ... we're now going to roll back to Patch 1 for PPROD and regression test.

In a rush out to the airport, but wanted to escalate this to you as a matter of urgency ... please see last comment. I will try to call you from the cab.

SQL locking still occurring regardless of patch 1 or patch 2 (current). Locking was despite restarting the IdB service - so only IdB was accessing its own database. DBA advises that the traces attached demonstrate that the select/insert statements are contending with each other, and that a "with (nolock)" is required to fix the problem ... I advised that this is Linq-generated SQL, so the question was then what Linq directives can be given to ensure a nolock?

I reiterated that this problem hasn't surfaced in either DEV or TEST environments with roughly the same source (ADW) database size responsible for the initial volume of Identity Broker database entity records. The DBA said that while the trace was conclusive in terms of what the cause of the locking is, they are now investigating what environmental differences exist between the different database instances (PPROD vs DEV/TEST) to see if there is a database/instance level setting that affects the default read lock behaviour. I left them with this investigation ... TBC.

Please confirm that Identity Broker v3.0.6.1 was installed prior to installing the patches. And that patch 2 was installed alongside patch 1.

Can I suggest changing the connectors to using IDB306:Never timing so that we can narrow down the problem?

Thanks.

(from the DBA ...)

(@SelectId uniqueidentifier)DELETE Entity FROM Entity JOIN #EntitySelect ON Entity.EntityId = #EntitySelect.EntityId AND Entity.PartitionId = #EntitySelect.PartitionId WHERE EntitySelectGroupId = @SelectId

Is causing a lot of blockings.

Trev - for you to progress as far as you're comfortable to do so Speak tomorrow.

this is the original file that I could find timing attributes in the unify services folder. I have tried to make the changes “not this file”but didn’t make a difference for me.

From: Bob Bradley (Assigned) (JIRA) jira@unifysolutions.jira.com
Sent: Thursday, 22 September 2011 5:41 PM
To: WILLIAMS,Trevor
Subject: JIRA Studio (DEEWR-26) System.Data.SqlClient.SqlException - (Deadlock)

https://unifysolutions.jira.com/s/en_US5qga4p/650/89/_/jira-logo-scaled.png

https://unifysolutions.jira.com/secure/useravatar?ownerId=bob.bradley&avatarId=10537Bob Bradley<https://unifysolutions.jira.com/secure/ViewProfile.jspa?name=bob.bradley> assigned https://unifysolutions.jira.com/images/icons/genericissue.gif DEEWR-26<https://unifysolutions.jira.com/browse/DEEWR-26> to Trevor Williams (DEEWR)<https://unifysolutions.jira.com/secure/ViewProfile.jspa?name=deewr.trevor.williams>
System.Data.SqlClient.SqlException - (Deadlock)<https://unifysolutions.jira.com/browse/DEEWR-26>

Trev - for you to progress as far as you're comfortable to do so https://unifysolutions.jira.com/images/icons/emoticons/smile.gif Speak tomorrow.

Change By:

Bob Bradley<https://unifysolutions.jira.com/secure/ViewProfile.jspa?name=bob.bradley> (22/Sep/11 5:40 PM)

Assignee:

Bob Bradley Trevor Williams (DEEWR)

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators<https://unifysolutions.jira.com/secure/ContactAdministrators!default.jspa>.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Notice:

The information contained in this email message and any attached files may be confidential information, and may also be the subject of legal professional privilege. If you are not the intended recipient any use, disclosure or copying of this email is unauthorised. If you received this email in error, please notify the sender by contacting the DEEWR Switchboard on 13 33 97 (1DEEWR) during business hours (8am - 8pm AEST) and delete all copies of this transmission together with any attachments.

Hi Guys,

this is the current connectorEngine.extensibility.config.xml file which has not been modified with the timing attributes set to Never.

trev

Yes, this is correct.

Please confirm that Identity Broker v3.0.6.1 was installed prior to installing the patches. And that patch 2 was installed alongside patch 1.

Can I suggest changing the connectors to using IDB306:Never timing so that we can narrow down the problem?

Thanks.

Hi Trevor,

From the attachments, I can't see where you have set the timing to never. For example:

<timing name="Never" />

Once the timing is set to never and the Identity Broker service has been restarted, you should be able to see how the operations perform in isolation.

Thanks.

Turns out the problem was with the "Patch 2" update applied to the PPROD environment. When it was applied to DEV (after taking a backup of DLLs!!!) the same exception occurred, and rolling back it worked again. Rolled back the patch in PPROD by deleting the Unify.Data.dll file and reinstating the original Unify.Framework.Adapter.dll and Unify.Repository.Entity.Sql.dll files.

The problem can now be considered resolved @ DEEWR at least, but it is clear there is still a deadlocking problem with Patch 2 which comments on IDB-107 indicated had since been resolved (not the case @ DEEWR).

Please let us know if there is anything you need further from the DEEWR deployment, because as it stands I am presuming that without some resolution here, DEEWR would be unable to install subsequent patches in future without this issue raising its head again.

Bob,

A combined Patch 1, 2 and 3 has been released.

Please see SUBIDB:Downloads.

Please let me know if you think you could attempt to reproduce the problems with this patch.

Thanks.

This is the corrupted FullImport.txt file produced @ DEEWR after applying patch 3. The Sites property for 66 Org records (including UID=AMEB,OU=Orgs) was corrupted - this is configured as a multi-value reference attribute, and the latest code seems to perform some sort of cartesian product, bloating the file from approx 3.5 Mb (pre patch) to 64 Mb (post patch). Rolling back the patch resolved the problem.

Adam - the import ran without error this time, but has introduced a new problem (see previous comment and attached FullImport.zip). Please let me know if you need anything else ... full connector/adapter configs are unchanged from a previous DEEWR project post (will look this up now).

For connector/adapter configs, see https://unifysolutions.jira.com/browse/DEEWR-7

Hi Tony,

Could you please take a look at this?

Thanks.

Assigned to Matthew.

Updated billing key.

Investigated behaviour and implemented fix. Assigned to Adam.

Hi Bob,

I have uploaded a new version of Patch 3. It is still called Patch 1, 2 and 3.

Please let me know how it goes.

Thanks.

Initial tests are positive - no net difference after upgrade from patch 1 to (the latest) patch 3. Will speak to Trevor about progressing to TEST/PPROD. Thanks Adam.

Trevor - this patch is now ready to apply to TEST and then PPROD (or maybe the reverse order as impact is less on PPROD) at your leisure. Please let me know if you need anything from me.

Thanks Bob.

Bob,

Is this issue still active?

Problem has not resurfaced after patch 3. Solution is still being migrated into and tested in 3 different environments ... will close once some level of PROD testing is performed by DEEWR