preferred repository for OpenIDM?

This topic contains 4 replies, has 4 voices, and was last updated by Profile photo of Thomas Wolfram Thomas Wolfram 4 months, 1 week ago.

  • Author
    Posts
  • #8415
    Profile photo of patrick.hagen@kit.edu patrick.hagen@kit.edu 
    Participant

    Hi all,

    I’ve been running OpenIDM for about a year now, using Sqlserver as a backend for my repository.From the start there have been deadlock issues in the log like “Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 317) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.” Those issues happened even when not running in a cluster.

    I didn’t like it, but thought “well, if OpenIDM has to restart a transaction once or twice, that’s ok, the performance is still fine.”

    But there has been very strange misbehaviour, e.g. last week two cluster nodes performed a create operation in the same moment, causing a “creative” race condition. Perhaps the cluster-synchronization failed because of deadlock-issues in the database? Perhaps OpenIDM fails in handling exceptions related to the repository?

    Has anybody seen “strange” issues with a Sqlsever backend? Can anybody compare backends? Are people happy with MySQL or PostgreSQL?

    Any help would be greatly appreciated.

    Yours,
    Patrick.

    #8426
    Profile photo of tim.sedlack tim.sedlack 
    Participant

    DB contention is a possibility, but without having logs (both IDM and SQL), it would be difficult to say what’s really going on. Ideally we’d have a recreatable situation that we can use to work with.

    Patrick, I feel like I know your level of expertise on the product (very high!), so if you could assist us in determining when the contention occurs, we can have support and the product team look at it to see if it’s a condition we can help with (or avoid altogether).

    Thanks,
    Tim

    #8427
    Profile photo of Gentjan Kocaqi Gentjan Kocaqi 
    Participant

    Patrick,
    I saw that issue using SQL Server as repository for OpenIDM 3.0.0. And as far as I remember it happened after changes made to OpenIDM. The workaround was rebuilding the indexes.
    But like Tim said, getting some more logs especially from the SQL (to get the specific queries that are causing the deadlock) would be helpful to figure out the root cause of the issue.

    I can’t say if it happens the same using other database servers as repository but I am pretty sure that upgrading the OpenIDM at the latest version is suggested cause the database schema is better.

    Regards,
    Gentjan

    #8479
    Profile photo of patrick.hagen@kit.edu patrick.hagen@kit.edu 
    Participant

    Hi Gentjan,

    I’ll try to check and rebuild index information, that would be a fantastic solution, since it should be quite easy. But I doubt it. :(

    Getting some (sanitized) OpenIDM-logs is pretty easy, getting the sqlserver-information is hard. Sometimes I wish I could run all the systems I need on my own. :(

    One server says 5 times:
    Mar 08, 2016 3:31:40 AM org.forgerock.openidm.servlet.internal.ServletConnectionFactory$3 filterGenericError
    WARNING: Resource exception: 500 Internal Server Error: “DB reported failure executing query WITH results AS ( SELECT rowNo = ROW_NUMBER() OVER( ORDER BY obj.id DESC ), obj.fullobject FROM openidm_prod.managedobjects obj WHERE (EXISTS (SELECT * FROM openidm_prod.managedobjectproperties prop WHERE prop.managedobjects_id = obj.id AND prop.propkey = ? AND prop.propvalue = ? ))) SELECT * FROM results WHERE rowNo BETWEEN 1 AND 2147483647 with params: {_resource=managed/user, _pagedResultsOffset=0, _sortKeys=[], _queryId=null, _pageSize=2147483647, _queryExpression=null, _queryFilter=/gupId eq “M00003219″} error code: 1205 sqlstate: 40001 message: Transaction (Process ID 314) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

    And the other one
    Mar 08, 2016 3:31:40 PM org.forgerock.openidm.util.LogUtil logAtLevel
    INFO: Scheduled service “scheduler-service-group.7fee0c92-a652-48e2-afe3-99f7c5f87449” found, invoking.
    Mar 08, 2016 3:31:40 PM org.identityconnectors.framework.impl.api.local.ConnectorPoolManager$ConnectorPoolHandler testObject
    INFO: checkAlive DatabaseTable connector
    Mar 08, 2016 3:31:40 PM org.identityconnectors.databasetable.DatabaseTableConnector checkAlive
    INFO: valid connection query is empty, test connection using default
    Mar 08, 2016 3:31:40 PM org.identityconnectors.framework.impl.api.local.operations.SyncImpl sync
    INFO: check the ObjectClass and result handler
    Mar 08, 2016 3:31:40 PM org.identityconnectors.framework.impl.api.local.operations.SyncImpl sync
    INFO: Sync token is 2016-03-08 15:30:04.937
    Mar 08, 2016 3:31:40 PM org.identityconnectors.dbcommon.DatabaseConnection prepareStatement
    INFO: Prepare SQL Statement : SELECT … WHERE modifiedat > ? ORDER BY modifiedat ASC
    Mar 08, 2016 3:31:40 PM org.identityconnectors.framework.impl.api.local.operations.SyncImpl sync
    INFO: execute sync query SELECT … FROM studierende WHERE modifiedat > ? ORDER BY modifiedat ASC on ObjectClass: __ACCOUNT__
    Mar 08, 2016 3:31:40 PM org.identityconnectors.framework.impl.api.local.operations.SyncImpl sync
    INFO: commit sync account
    Mar 08, 2016 3:31:40 PM org.forgerock.openidm.util.LogUtil logAtLevel

    And I know for sure that the id mentioned by the first server is not at all related to the table the second server is accessing.

    I’ll try to speed up the migration to OpenIDM 4.0 (I want to anyway), but that requires quite a bit of preparation.

    Regards,
    Patrick.

    #16366
    Profile photo of Thomas Wolfram Thomas Wolfram 
    Participant

    Hi Patrick,
    after our discussion last week I checked again our OpenIDM (4.5) logs and I have not found any deadlock issues. At least not yet. We are running MS SQL Server 2014 SP2.

    Also I used SQL profiler to look into which default transaction isolation level OpenIDM is using for the repository database on SQL Server. It is the normal READ COMMITTED level. But they are using it with snapshot isolation. This is enabled by a special database level option called “READ_COMMITTED_SNAPSHOT”. When this option is enabled SQL Server uses an “optimistic” concurrency model on the statement level based on snapshot isolation (using row versioning in tempdb) .

    AFAIK this means that concurrent readers are practically never blocked by locks (writers still can block each other). But SELECTs like the ones in your logs you pasted above should not run into any blocking locks let alone into deadlocks.

    Therefore I wonder whether the READ_COMMITTED_SNAPSHOT option is enabled in your repository database? It appears that ForgeRock added this option to the SQL repository init script for MS SQL between OpenIDM 3.0.0 and 3.1.0 (see OPENIDM-2480).

    Mabye it was not added when you upgraded your repository from 3.0.0 to 3.1.0? Or maybe your DBAs disabled it without notifying you? Because this option is increasing the load on the tempdb. Though I believe it is not such a big deal enabling it. This option exists since SQL 2005 and AFAIR Oracle uses row versioning (snapshot isolation) even by default.

    Thomas

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.

©2017 ForgeRock - we provide an identity and access platform to secure every online relationship for the enterprise market, educational sector and even entire countries. Click to view our privacy policy and terms of use.

Log in with your credentials

Forgot your details?