System is blocked when LayoutFriendlyURL Table contains many entries

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.X EE, Master
• Fix Version/s: 7.0.0 DXP FP29, 7.0.X EE, 7.0.4 CE GA5, 7.1.X, Master
• Component/s: Staging > Export/Import
• Labels:
  • ee-ts
  • liferay-digital-enterprise-70-sp5
  • liferay-fixpack-de-29-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Fix Priority: 4
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/51043

Description

If the LayoutFriendlyURL table contains many entries, threads are blocked when loading some pages. From the thread dumps (threaddump.out), it can be seen that the thread is blocked at LayoutFriendlyURLPersistenceImpl#fetchByPrimaryKeys().

Seems like the problem is the sql query (you can see the query in "select_statement.txt" file), because the customer has 133566 entries in the LayoutFriendlyURL table.

Steps to reproduce:

• Startup a fresh liferay bundle
• Execute the sql statement from the attached file 'LayoutFriendlyURL.sql'. This will import all the LayoutFriendlyURL entries in the table.
• Clear the database cache or restart server
• Browse in the portal - navigate to "Site > Publishing > Export" or "Site > Publishing > Import".

 The loading of the page stucks

Reproduced on

• 7.0 de-26
• master (3093baf677062a452989b69c3285fba415fcfffa)