Use Dynamic Query when checking articles.

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.X, 7.1.X, Master
• Fix Version/s: 7.0.0 DXP FP73, 7.0.10.11 DXP SP11, 7.0.X, 7.1.10 DXP FP7, 7.1.10.2 SP2, 7.1.3 CE GA4, 7.1.X, Master
• Component/s: WCM
• Labels:
  • ee-ts
  • liferay-dxp-70-sp11
  • liferay-dxp-71-sp2
  • liferay-fixpack-de-73-7010
  • liferay-fixpack-dxp-7-7110
• Branch Version/s:
  7.1.x, 7.0.x
• Backported to Branch:
  Committed
• Story Points: 0.25
• Fix Priority: 2
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/66800

When many web content articles need to be updated (such as expiring them), Liferay will use a scheduler_dispatch thread to update them. However, Liferay does it all in one transaction.

This can cause issues because if the transaction fails for some reason, Liferay will need to roll it all back and try again later, only to fail and roll back again. When it comes to expiring web content, it is not necessary to use a single transaction because it is not critical to prevent "dirty reads" in this case.

Steps to Reproduce
1. Get a database other than MySQL since we will need to limit the database's transaction log size later. MySQL will automatically "break" the max log size limit, which is not what we want in this case. So I used DB2 10.5 (https://files.liferay.com/private/vms/vm-win2012r2/vm-win2012r2.vmdk.x64.db2-10.5.zip)
2. Assuming you're using DB2, create a database with a page size of 32k create database <DATABASENAME> using codeset utf8 territory us pagesize 32 k
3. Connect Liferay 7 to it:

	jdbc.default.driverClassName=com.ibm.db2.jcc.DB2Driver
	jdbc.default.url=jdbc:db2://<IP_OF_YOUR_VM>:50000/<DATABASENAME>:deferPrepares=false;fullyMaterializeInputStreams=true;fullyMaterializeLobData=true;progressiveLocators=2;progressiveStreaming=2;
	jdbc.default.username=administrator
	jdbc.default.password=password

Remember to add the JDBC driver [^db2jcc4.jar]
4. Start Liferay and deploy the [^liferay.dummy.factory-7.0.1.jar]. It can also be found at https://github.com/yasuflatland-lf/liferay-dummy-factory or alternatively use the attached groovy script.
5. Go to Control Panel -> Apps -> Dummy. Use the portlet to Create 200,000 web content. This will take about 2 hours.
6. Once the web content is created, shutdown Liferay
7. Go into the database (I used RazorSQL) and do a select * on JournalArticle. Copy a date from DISPLAYDATE.
8. You will update the Expiration Date to be any day before the Display Date. I used this query: update journalarticle set EXPIRATIONDATE ='2018-12-04 22:25:00.0'
9. Using DB2 Command Window (search for it in the Windows Start menu), minimize the transaction logs for DB2 by running these queries:

update db cfg for {database_name} using logprimary 3
update db cfg for {database_name} using logsecond 10

To verify that your settings took place, run this query and look for the logprimary and logsecond parameters:

get database configuration for {database_name}

10. Restart DB2, or since it's faster, restart Windows. I don't mean restart VMWare, I mean to make sure to restart the OS. This will make your settings from Step 9 take effect.
11. Start up Liferay, and wait. If you take thread dumps during this time, you should see scheduler threads like:

at org.hibernate.property.BasicPropertyAccessor$BasicGetter.get(BasicPropertyAccessor.java:172) 
at org.hibernate.tuple.entity.AbstractEntityTuplizer.getIdentifier(AbstractEntityTuplizer.java:227) 
at org.hibernate.persister.entity.AbstractEntityPersister.getIdentifier(AbstractEntityPersister.java:3876) 
at org.hibernate.event.def.DefaultFlushEntityEventListener.checkId(DefaultFlushEntityEventListener.java:80) 
at org.hibernate.event.def.DefaultFlushEntityEventListener.getValues(DefaultFlushEntityEventListener.java:190) 
at org.hibernate.event.def.DefaultFlushEntityEventListener.onFlushEntity(DefaultFlushEntityEventListener.java:147) 
at org.hibernate.event.def.AbstractFlushingEventListener.flushEntities(AbstractFlushingEventListener.java:219) 
at org.hibernate.event.def.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:99) 
at com.liferay.portal.dao.orm.hibernate.event.NestableAutoFlushEventListener.onAutoFlush(NestableAutoFlushEventListener.java:59) 
at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:1185) 
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1261) 
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) 
at com.liferay.portal.dao.orm.hibernate.QueryImpl.list(QueryImpl.java:120) 
at com.liferay.portal.dao.orm.hibernate.QueryImpl.list(QueryImpl.java:111) 
at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:83) 
at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:51) 
at com.liferay.journal.service.persistence.impl.JournalArticlePersistenceImpl.findByG_A_ST(JournalArticlePersistenceImpl.java:27230) 
at com.liferay.journal.service.persistence.impl.JournalArticlePersistenceImpl.findByG_A_ST(JournalArticlePersistenceImpl.java:27100) 
at com.liferay.journal.service.persistence.impl.JournalArticlePersistenceImpl.findByG_A_ST(JournalArticlePersistenceImpl.java:27078) 
at com.liferay.journal.service.impl.JournalArticleLocalServiceImpl.getPreviousApprovedArticle(JournalArticleLocalServiceImpl.java:3230) 
at com.liferay.journal.service.impl.JournalArticleLocalServiceImpl.updatePreviousApprovedArticle(JournalArticleLocalServiceImpl.java:8127) 
at com.liferay.journal.service.impl.JournalArticleLocalServiceImpl.checkArticlesByExpirationDate(JournalArticleLocalServiceImpl.java:6483) 
at com.liferay.journal.service.impl.JournalArticleLocalServiceImpl.checkArticles(JournalArticleLocalServiceImpl.java:818)

For me, it took about 9 minutes for Liferay to throw this error [^batch-error.txt]
Based on https://www-01.ibm.com/support/docview.wss?uid=swg21617184, this is error we want to see.
If you continue waiting, you'll see the error thrown again, every time the scheduler runs.

RESULT: Liferay was unable to commit the transaction so it rolls it back and tries again, only to fail and roll back again.
EXPECTED: Liferay can expire web contents in multiple transactions as a single transaction is unnecessary for this use case.

Errors
[^batch-error.txt]