Users are not able to log in due to transaction lock errors in database

Issue

• Some users are not able to log in to the environment, even after clearing the database cache
• The user generally encounters a temporarily unavailable banner or a not authorized 401 error
• The following transaction lock error appears in the database logs

Exception executing batch [java.sql.BatchUpdateException: (conn=227) Lock wait timeout exceeded; try restarting transaction], SQL: insert into SamlSpSession (companyId, userId, userName, createDate, modifiedDate, samlPeerBindingId, assertionXml, jSessionId, samlSpSessionKey, sessionIndex, terminated_, samlSpSessionId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Environment

• Liferay Cloud
• Liferay DXP 7.4

Resolution

• Sometimes there can be a stuck transaction on the database server side which can potentially cause a node discrepancy related to the lock wait timeout error that is shown
• This can result in some of the users not being able to log in
• To resolve this, you can restart the Liferay nodes one by one to end the transaction that is locking the table. Please reach out to Liferay Support if assistance is needed with this.
• Please note that a hard refresh of the browser might be needed after the restart, to refresh the cache