ACS - Slow transformation causes a bottleneck of uncommitted transactions on the DB

Description

A slow transformation with the Transform Service causes a series of uncommitted transactions within the DB until the rendition is returned.
This is a problem that occurs using Share and could cause the db connections pool to run out when there is
an high load on the system.

Supporting evidence

Steps to reproduce

  1. Deploy ACS 6.2.2 with the docker-compose file available here: https://github.com/Alfresco/acs-deployment/blob/master/docker-compose/6.2.N-docker-compose.yml

  2. Login within Share and upload the .HTML document attached to this case (see testFile.html under FTP)

  3. Now check the existing db connections open within the database (in this case i'm using PostgreSQL, perform a query on the "pg_stat_activity" table to get this information), no "idle_in_transaction" connections at this stage

  4. Now click on the document within Share to open the document detail page (to see the full preview)

Expected Behaviour
The document detail page shows the document preview and no "idle_in_transaction" connections are left within the database

Observed Behaviour
The document detail page does not display the document preview and a query is left in the database with "idle_in_transaction" status (see attachment "uncommittedTransaction.png").
Any subsequent refresh of the page will result in more queries left open within the database, quickly filling the db connection pool. (see attachment "openConnections.png").
Same behaviour occurs with any operation on the document while Share waits for the rendition.
The query left in "idle_in_transaction" status is the following:

Additional findings
The database connections are kept in this uncommitted status until the transformation service provides a result, and this is a problem because for example by default the LibreOffice timeout is set to 20 minutes.
In my local environment this sample document always hit the LibreOffice timeout limit (probably due to a LibreOffice limitation), causing the connections to wait for 20 minutes in an uncommitted state, which is a big problem for the customer when the load on the system is high.

Workaround
Currently the only tested workaround for this is to lower the LibreOffice timeout limit within the all-in-one transformer using the LIBREOFFICE_TIMEOUT property (https://github.com/Alfresco/alfresco-transform-core/blob/master/docs/external-engine-configuration.md#core-aio).

Environment

None

Testcase ID

None
Deferred
Your pinned fields
Click on the next to a field label to start pinning.

Assignee

Indy Sandhu

Reporter

Damiano Mondardo

Labels

Escalated By

CSO

ACT Numbers

01021877, 00374357

Delivery Team

Team 7

Bug Priority

Category 1