Hi all,
So we’ve been noticing that with about 20-minute periodicity, that we get SQL deadlocks in our logs. When our IT people look at things from the SQL side, they see that Ignition is sending multiple SELECT 1 queries per second. Checking the database configuration, this seems to be expected behavior, though the repetition rate seems excessive:
We have two separate databases on the same server, that we access with separate connections, due to historical reasons (we had to move one database off another server after we’d already established the first connection to the server). Again, that would double the number of SELECT 1 queries, but multiple per second still seems very excessive.
Furthermore, at least some number of the deadlocks seem to be due to these SELECT 1 queries, which should be among the fastest queries to resolve, I’d imagine.
- Does anyone know what the cause is for such a frequent validation query?
- Is there some way to change the frequency?
- Is there any reason why such a query would contribute to deadlock?
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 55) 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:196)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4700)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1683)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:956)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingResultSet.next(DelegatingResultSet.java:424)
at com.inductiveautomation.gateway.tags.history.query.DatasourceHistoryInterface.loadSCExecRecords(DatasourceHistoryInterface.java:435)
at com.inductiveautomation.gateway.tags.history.query.DatasourceQueryExecutor.initialize(DatasourceQueryExecutor.java:646)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.AggregateHistoryQueryExecutor.initialize(AggregateHistoryQueryExecutor.java:78)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.AggregateHistoryQueryExecutor.initialize(AggregateHistoryQueryExecutor.java:78)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.HistoryWriter.initLoader(HistoryWriter.java:140)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.HistoryWriter.execute(HistoryWriter.java:210)
at com.inductiveautomation.ignition.gateway.sqltags.SQLTagsManagerImpl.queryHistory(SQLTagsManagerImpl.java:1297)
at com.inductiveautomation.ignition.gateway.project.ProjectSettingsCache$ProjectDefaultTagManagerFacade.queryHistory(ProjectSettingsCache.java:673)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.QueryTagHistory.invoke(QueryTagHistory.java:96)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:404)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:518)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)