With DLFileRank enabled, Deadlock or SQL Update Batch Errors may happen due to DLFileRank updates.
When using DLFileRank, unique constraints can be found due to the amount of writes that are done to the database. Many times it is helpful to take a look at the constraint that is causing the batch update issue. These errors come about due to the fact that every time content is viewed, it will update the database to write the most recently downloaded documents for a given site. With DLFileRank there are some small reads, a bulk read and a write that tries to rapidly remove entries one at a time. Therefore when you add clustering into the mix, this issue steadily becomes more prevalent.
The following is an example of the stacktrace snippet when the deadlock was happening:
2019-06-10 15:32:54.498 ERROR [ajp-nio-8009-exec-678][JDBCExceptionReporter:234]
Transaction (Process ID 487) was deadlocked on lock | thread resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
2019-06-10 15:32:54.498 ERROR
[ajp-nio-8009-exec-678][TransactionCommitCallbackUtil:46] Unable to execute transaction
commit callback
com.liferay.portal.kernel.exception.SystemException:
com.liferay.portal.kernel.dao.orm.ORMException:
org.hibernate.exception.LockAcquisitionException: could not execute query
at
com.liferay.portal.kernel.service.persistence.impl.BasePersistenceImpl.processException(BasePersistenceImpl.java:269)
at
com.liferay.document.library.file.rank.service.persistence.impl.DLFileRankPersistenceImpl.fetchByC_U_F(DLFileRankPersistenceImpl.java:2280)
at
com.liferay.document.library.file.rank.service.persistence.impl.DLFileRankPersistenceImpl.fetchByC_U_F(DLFileRankPersistenceImpl.java:2190)
at
com.liferay.document.library.file.rank.service.impl.DLFileRankLocalServiceImpl.updateFileRank(DLFileRankLocalServiceImpl.java:200)
at sun.reflect.GeneratedMethodAccessor880.invoke(Unknown Source)
...
at
com.liferay.document.library.internal.service.DLFileRankDLAppHelperLocalServiceWrapper.lambda$getFileAsStream$0(DLFileRankDLAppHelperLocalServiceWrapper.java:74)
...
Caused by: com.liferay.portal.kernel.dao.orm.ORMException:
org.hibernate.exception.LockAcquisitionException: could not execute query
...
at
com.liferay.document.library.file.rank.service.persistence.impl.DLFileRankPersistenceImpl.f
etchByC_U_F(DLFileRankPersistenceImpl.java:2252)
... 106 more
Caused by: org.hibernate.exception.LockAcquisitionException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:107)
…..
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 487)
was deadlocked on lock | thread resources with another process and has been chosen as
the deadlock victim. Rerun the transaction.
...
Resolution
The suggestion, in this case, is that if DLFileRank is not necessary for the project, you can improve the performance of the database by turning it off.
For versions 6.0.x, 6.1.x, 6.2.x, DXP 7.0, DLFileRank can be turned off with this property: dl.file.rank.enabled=false
For DXP 7.1.x, services and models of DLFileRank were extracted to an OSGi service in an independent module (LPS-74888). In order to disable file ranks, the module document-library-file-rank-service needs to be deactivated:
To Disable DLFileRank Temporarily: (The module will be started again after removing osgi/state and restarting the server.)
- Go to Gogoshell (Control Panel -> Configuration -> Gogoshell).
-
Execute ss| grep "com.liferay.document.library.file.rank.service" and the output should look like the following:
g! ss| grep "com.liferay.document.library.file.rank.service" 304 ACTIVE com.liferay.document.library.file.rank.service_1.0.12
- Execute stop 304.
-
Execute ss| grep "com.liferay.document.library.file.rank.service" and the output should look like the following: (status is resolved)
g! ss| grep "com.liferay.document.library.file.rank.service" 304 RESOLVED com.liferay.document.library.file.rank.service_1.0.12
To Disable DLFileRank Permanently: (by blacklisting the module)
- Go to Control Panel, navigate to Configuration → System Settings → Module Container.
- In the Bundle Blacklist screen, add the bundle symbolic name "com.liferay.document.library.file.rank.service". -> Update.
- The following message will be displayed in the log:
2019-06-11 02:56:54.476 INFO [CM Event Dispatcher (Fire
ConfigurationEvent:
pid=com.liferay.portal.bundle.blacklist.internal.BundleBlacklistConfigur
ation)][BundleBlacklist:188] Stopping blacklisted bundle
com.liferay.document.library.file.rank.service_1.0.12 [304]
2019-06-11 02:56:54.571 INFO [CM Event Dispatcher (Fire
ConfigurationEvent:
pid=com.liferay.portal.bundle.blacklist.internal.BundleBlacklistConfigur
ation)][BundleStartStopLogger:42] STOPPED
com.liferay.document.library.file.rank.service_1.0.12 [304]
Additional Information
The more people that are accessing the documents, the more likely this error is to occur because of the sheer number of writes to the database. Therefore, it is necessary to keep in mind potential bandwidth of the site.