CannotAcquireLockException is shown in the server logs

Issue

• A CannotAcquireLockException is shown several times in the server log:

ERROR [http--10.1.1.133-8080-281][JSONWebServiceServiceAction:97] 
org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; 
SQL [INSERT INTO Users_UserGroups (userGroupId, userId) VALUES (?, ?)]; 
Unknown error 1205; nested exception is java.sql.SQLException: Unknown error 1205

Environment

• Portal 6.2

Resolution

• If this error appears in the log, it indicates that the database cannot get a lock for Users_UserGroups table to execute the INSERT query.
  If MySQL is used as database and many SELECT queries are being executed on the Users_UserGroups table, the database may prioritize the SELECT queries and wait for them to be executed before execute the INSERT query.
  If this timeout exceeds the time specified in the MySQL's innodb_lock_wait_timeout property, the CannotAcquireLockException error occurs.

• It is possible to reduce the number of SELECT queries executed on the database by adding the affected table to the table.mapper.cache.mapping.table.names property in the portal-ext.properties, in which case the queries to this table will be cached by the portal:

  table.mapper.cache.mapping.table.names=Users_UserGroups

  It will be necessary to restart the portal for the new value to be applied.

Additional Information

• As of 6.2 Fix pack Portal 108, this property was changed to table.mapper.cacheless.mapping.table.names, being by default the opposite behavior, i.e., all tables are cached and only the tables specified in this property will be executed against the database.