We are seeing a large number of errors in the lightstreamer logs along with warnings from our database due to an increasing number of deadlocks and long lock times in our production environment.
We are currently on lightstreamer 7.4.0 with two identical load-balanced instances and one database on a very beefy SQL Server in AWS RDS.
A quick look at the COMMANDS table shows that there are a few hundred rows with the module ID referenced in the exceptions and with a timestamp from yesterday.
The constraint violations don't seem to cause an immediate problem, but I am worried that the increasing number of deadlocks and waits on the database might escalate - and obviously we would rather not have new errors in the logs every few seconds.
We have two more identical setups for our development and staging environments which are completely fine, but those are obviously under a lot less load.
Deadlocks look like this:
25.Sep.25 10:45:03,666 <ERROR> Collection of old module 3a605d64-b453-4dcf-a4b9-7c726d92ef1e failed
com.lightstreamer.mpndb.e: Transaction commit failed: org.hibernate.exception.LockAcquisitionException: could not execute statement
at com.lightstreamer.mpndb.o.i(o.java)
at com.lightstreamer.mpn.t.J(t.java)
at com.lightstreamer.mpn.t.e(t.java)
at com.lightstreamer.mpn.p.y(p.java)
at com.lightstreamer.s.j.q.o(q.java)
at com.lightstreamer.s.j.q.run(q.java)
at com.lightstreamer.s.j.c.run(c.java)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at com.lightstreamer.s.j.k.q.B(q.java)
at com.lightstreamer.s.j.k.x.B(x.java)
at com.lightstreamer.s.j.df.run(df.java)
at com.lightstreamer.s.j.dd.run(dd.java)
Caused by: javax.persistence.OptimisticLockException: org.hibernate.exception.LockAcquisitionException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.wrapLockException(ExceptionConverterImpl.java:277)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:98)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1366)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:448)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
... 13 common frames omitted
Caused by: org.hibernate.exception.LockAcquisitionException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:120)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:46)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3615)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3875)
at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:123)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:344)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:99)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
... 21 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:648)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:567)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:512)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
... 33 common frames omitted
25.Sep.25 10:45:03,665 <ERROR> Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Constraint violations:
25.Sep.25 10:56:56,275 <ERROR> Collection of old module 3a605d64-b453-4dcf-a4b9-7c726d92ef1e failed
com.lightstreamer.mpndb.e: Transaction commit failed: org.hibernate.exception.ConstraintViolationException: could not execute statement
at com.lightstreamer.mpndb.o.i(o.java)
at com.lightstreamer.mpn.t.J(t.java)
at com.lightstreamer.mpn.t.e(t.java)
at com.lightstreamer.mpn.p.y(p.java)
at com.lightstreamer.s.j.q.o(q.java)
at com.lightstreamer.s.j.q.run(q.java)
at com.lightstreamer.s.j.c.run(c.java)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at com.lightstreamer.s.j.k.q.B(q.java)
at com.lightstreamer.s.j.k.x.B(x.java)
at com.lightstreamer.s.j.df.run(df.java)
at com.lightstreamer.s.j.dd.run(dd.java)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1366)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:448)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
... 13 common frames omitted
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:109)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:46)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3615)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3875)
at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:123)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:344)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:99)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
... 21 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK35tvvsxr2lgp74avoag7apc3f". The conflict occurred in database "lightstreamer-mpn", table "dbo.COMMANDS", column 'MODULE_ID'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:648)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:567)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:512)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
... 33 common frames omitted
25.Sep.25 10:56:56,275 <ERROR> Transaction commit failed #258678 for module collection: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement.
25.Sep.25 10:56:56,270 <ERROR> The DELETE statement conflicted with the REFERENCE constraint "FK35tvvsxr2lgp74avoag7apc3f". The conflict occurred in database "lightstreamer-mpn", table "dbo.COMMANDS", column 'MODULE_ID'.
