Reconsiliations Are Stopping Abnormally (Doc ID 1992080.1)

Last updated on NOVEMBER 09, 2016

Applies to:

Oracle Waveset - Version 8.1 and later
Information in this document applies to any platform.

Goal

Q1 by customer:

Reconsiliations for multiple systems with a big count of accounts (>35K) are stopping abnormally in first 5 minutes with following exception:

Component
Reconciler

Error Code
EXMN00

Reported cause
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 88) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
com.waveset.util.IOException:
==> com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 88) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.waveset.repository.RelationalDataStore.resolveNextBlock(RelationalDataStore.java:6120)
at com.waveset.repository.AbstractDataStore.resolveBlock(AbstractDataStore.java:7284)
at com.waveset.repository.AbstractDataStore.resolveBlock(AbstractDataStore.java:7180)
at com.waveset.repository.AbstractDataStore.list(AbstractDataStore.java:4064)
at com.waveset.repository.AbstractDataStore.list(AbstractDataStore.java:4014)
at com.waveset.repository.ServerRepository.list(ServerRepository.java:4137)
at com.waveset.server.InternalSession.listObjects(InternalSession.java:1398)
at com.waveset.recon.ReconTask$ResourceThread.examineLighthouse(ReconTask.java:1186)
at com.waveset.recon.ReconTask$ResourceThread.reconcileResource(ReconTask.java:754)
at com.waveset.recon.ReconTask$ResourceThread.run(ReconTask.java:580)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 88) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.microsoft.sqlserver.jdbc.SQLServerExceptio

the error occures, because the first phase of the reconsiliation reads the data in read mode "commited" from the database and the changes to accounts, that are done while the read occures leading to deadlock.
example queries on the database: (this one leads to a deadlock)
update userattr set attrname=@P0, attrval=@P1 where id=@P2 and attrname=@P3 and attrval=@P4
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000))update userattr set attrname=@P0, attrval=@P1 where id=@P2 and attrname=@P3 and attrval=@P4

Second user queries (thats from recon)
12/Jan/2015 --> SELECT userobj.id, name, '', '', summary, '', '', xmlSize, '' FROM userobj WHERE userobj.id IN ( select id from userattr where attrname=N'USER_RESOURCES' and attrval=N'#ID#08D1-:CED33FF7F31:C2F55133-:1FDAE890AA491CE0' ) order by name

Environment:

OW 8.1.4
Tomcat 6.0.20
Microsoft SQL Server 2008 R2
Microsoft SQL Server Management Studio 10.0.5520.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 9.11.9600.17501
Microsoft .NET Framework 2.0.50727.5485
Betriebssystem 6.1.7601
Java 6.0.85
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms