Please share any images or screenshots, if relevant.
Hello everyone,
We currently upgraded our Test environment from 8.3p2 to 8.5. The process we did it was, upgrade to 8.4 successfully. Then proceed to upgrade 8.4 - 8.5. We had some issues along the way but were able to resolve those (schema mismatch, logins for SSMS and load balancer). However, we are experiencing major issue with the database. The first issue was the access request ID started from 0000. We updated the tables to reflect the current access request ID number. Then an error started populating “org.hibernate.exception.LockAcquisitionException: could not execute query”. This is causing multiple of problems. If SailPoint is running a Full aggregation, we can not sign in. If can sign in, running our validation tests, any LCM provisioning fails with this error. We have a diagnostic tool from SailPoint to capture database performance. We started with 131 - 146 ms. Then we fine tuned the database and we are getting 9 - 17 ms. That is satisfactory. However, we still are encountering the Lock issue.
Has anybody ran into this issue? If so, what were the actions you did to overcome that?
We removed over 140 workflow cases. This helped to clear out the latency while in SailPoint. We ran a AD aggregation and tried signing back in. We received the following error
Did you clear all your workflow cases, since you faced the error??
The error states thatHibernate tried to access the database but could not get a lock on a table or row, as The database was locked, blocked, or rolled back the transaction,
can you please restart your db server and post that tomcat?? and see if it fixes your error.
I checked the logs for this error and this is what it stated:
ERROR Thread-3823 engine.jdbc.spi.SqlExceptionHelper:142 - An error occurred during the current command (Done status 0). Transaction (Process ID 90) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim.
I believe the process ID is the main reason.
To answer the question - this occurs when every any major aggregation occurs.
Since it was patch Tuesday this week, I updated Windows (not saying this was a solution) and restarted the server. I increased the Java Initial Memory to 10GB from 200 MB.
Updates: I can sign in with out the org.hibernate error. I am running small task (perform maintenance) and so far no lock issues. Once this is completed, I will increase the load size of the tasks (AD then Full Identity).
@fcmendoza Could you please try adjusting the partition threads in request definition and see if it helps. Possibly, if all threads are consumed, it is causing this issue.
@fcmendoza - The issue org.hibernate.exception.LockAcquisitionException after an upgrade to IIQ 8.5 often indicates database deadlocks or contention during high-concurrency tasks like full aggregations and LCM provisioning. This can be caused by legacy data, exhausted thread pools, or specific configuration mismatches in the new version.
@kannan_sb85 This is helpful. We had already adjusted the Partitioning. But where do we adjust the threads? Also, would the corrupt Workflow Cases be the pending workflows or the identities that show up in the lockAcquistion error?
DO we need to restart the db server after making adjustments?
@FelipeMadeiraAlberto For RequestDefinition changes, db or app server restart is not needed. Even after adjusting the partitioning threads, do you still see the issue?
@fcmendoza - Thread adjustments are made within the Request Definition attributes. ‘Corrupt’ cases refer to stale WorkflowCase objects in the Debug page, not the identities themselves. No DB restart is needed for these changes, though a Tomcat/webserver restart is good practice to clear the connection pool.
We reran the Identity Refresh task with TRACE logging enabled for sailpoint.api.Identitizer. The updated log file is attached for your review.
Based on the TRACE output, the refresh task appears to start successfully and the Identitizer initialization is being entered as expected. The logs show multiple refresh workers entering the Identitizer flow and loading the refresh arguments for the Full Identity Refresh task.
SQL Error: 1205 Transaction was deadlocked ... and has been chosen as the deadlock victim
Based on this, the issue appears to occur when IIQ attempts to lock the Identity object during refresh processing, and SQL Server detects a deadlock condition.
@fcmendoza - It looks like the SQL Server 1205 deadlock during the IIQ Identity Refresh is tied to a known concurrency issue caused by the refreshManagerStatus option. That setting tends to create locking conflicts between the manager object and the Identity object. Disabling it or running it as a separate, isolated task should help avoid the deadlock. If you can share the trace logs as well, that would make it much easier to pinpoint the exact cause and confirm the locking pattern.
Thank you for the info, we are attempting a whole new upgrade to identify the steps taken during this process. We will be looking at refreshManagerStatus option.
