ACS - Slow transformation causes a bottleneck of uncommitted transactions on the DB
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.
Steps to reproduce
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
Login within Share and upload the .HTML document attached to this case (see testFile.html under FTP)
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
Now click on the document within Share to open the document detail page (to see the full preview)
The document detail page shows the document preview and no "idle_in_transaction" connections are left within the database
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:
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.
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).