Overview

The application database frequently encounters deadlock issues, with the following query constantly appearing in the database logs:

UPDATE jiveStatusLevelPntSum ps, (SELECT userID, sum( points ) AS sm 
FROM jiveStatusLevelPnt slp 
WHERE slp.userID > 0 
AND EXISTS 
(SELECT userID FROM jiveStatusLevelPntSum sub WHERE slp.userID = sub.userID 
AND sub.containerID = 1 AND sub.containerType = 14 AND dirty = 1 
AND pointLevel > 0) GROUP BY slp.userID ) lc 
SET ps.pointLevel = lc.sm, ps.dirty = 0 
WHERE ps.userID = lc.userID AND ps.containerType = 14 AND ps.containerID = 1;

This article describes possible solutions to this issue.

Environment

Jive On-Premise.

Information

Jive versions greater than or equal to 9.4

Run the following queries on the application database:

OPTIMIZE TABLE jiveStatusLevelPnt; 
OPTIMIZE TABLE jiveStatusLevelPntSum;

If you are using a MySQL database, review the innodb_buffer_pool_size value. Ideally, this should be between 50-75% of the system RAM.

Jive versions prior to 9.4

Upgrading to the latest version of Jive will address the cause of this issue. As a workaround, the issue may be mitigated by following these steps:

1. Browse to the Admin Console > System.
2. Click on System properties.
3. In the Property Name textbox at the top of the page, paste in: statusLevels.calculator.user.delayedCalculation.enabled
4. In the Property Value textbox below it, paste in: true
5. Click on the Save Property button.
6. In the Property Name textbox at the top of the page, paste in: userPointLevel.updateInterval.seconds
7. In the Property Value textbox below it, paste in: 1800
8. Click on the Save Property button.
9. In the Property Name textbox at the top of the page, paste in: statusLevelLeaders.updateInterval.seconds
10. In the Property Value textbox below it, paste in: 1800
11. Click on the Save Property button.
12. Schedule a time to perform a full restart of your Jive environment.