SQL Server deadlocked

[Tom.S]

Hi,
Running V7.6.14

I keep having this error appear in my logs.

How do I solve the issue.
I am sure how to track down what 2 resources are trying to update the table at the same time…

com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 66) 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:207)

at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)

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.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:1283)

at com.inductiveautomation.ignition.gateway.project.ProjectSettingsCache$ProjectDefaultTagManagerFacade.queryHistory(ProjectSettingsCache.java:669)

at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.QueryTagHistory.invoke(QueryTagHistory.java:96)

at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:405)

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$NotAsyncServlet.service(ServletHolder.java:1391)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:760)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:590)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1607)

at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)

at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1577)

at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

at org.eclipse.jetty.server.Server.handle(Server.java:500)

at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)

at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)

at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)

at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)

at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)

at java.lang.Thread.run(Unknown Source)

[nminchin]

I had an issue with this that was caused by a table trigger running too long and too frequently which was causing it to queue up and deadlock other queries.

You can check which queries are causing the deadlock. In SQL server you would use:

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
SELECT XEvent.query('.') AS XEvent
FROM (
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.NAME = 'system_health'
AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS source;

This was taken from:
https://blog.sqlauthority.com/2017/01/09/sql-server-get-historical-deadlock-information-system-health-extended-events/

[Tom.S]

Thank you for the Reply.
This is from the first entry of 117

Looks like it’s the sqlth_sce table.

Any ideas why this happens?

<deadlock>
  <victim-list>
    <victimProcess id="process1b9b4ab9c28" />
  </victim-list>
  <process-list>
    <process id="process1b9b4ab9c28" taskpriority="0" logused="0" waitresource="RID: 6:1:63599713:7" waittime="1631" ownerId="113946291" transactionname="SELECT" lasttranstarted="2020-06-15T21:18:14.637" XDES="0x1b6396e5ac0" lockMode="S" schedulerid="3" kpid="12404" status="suspended" spid="71" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2020-06-15T21:18:14.640" lastbatchcompleted="2020-06-15T21:18:14.640" lastattention="1900-01-01T00:00:00.640" clientapp="Microsoft SQL Server JDBC Driver" hostname="050PNIGS003V001" hostpid="0" loginname="IgnitionSheppSql" isolationlevel="read committed (2)" xactid="113946291" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="62" stmtend="352" sqlhandle="0x02000000c9849f0dca401de45b0e6d9a375992805ed80d520000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P0 bigint,@P1 bigint,@P2 int)SELECT "scid","start_time","end_time","rate" FROM sqlth_sce WHERE "start_time"&lt;=@P0 AND "end_time"&gt;=@P1 AND ("scid"=@P2) ORDER BY "start_time" ASC                           </inputbuf>
    </process>
    <process id="process1b9b4dd88c8" taskpriority="0" logused="0" waitresource="RID: 6:1:63599713:7" waittime="1631" ownerId="113946290" transactionname="SELECT" lasttranstarted="2020-06-15T21:18:14.637" XDES="0x1b603c0fac0" lockMode="S" schedulerid="5" kpid="10800" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2020-06-15T21:18:14.640" lastbatchcompleted="2020-06-15T21:18:14.640" lastattention="1900-01-01T00:00:00.640" clientapp="Microsoft SQL Server JDBC Driver" hostname="050PNIGS003V001" hostpid="0" loginname="IgnitionSheppSql" isolationlevel="read committed (2)" xactid="113946290" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="62" stmtend="352" sqlhandle="0x02000000c9849f0dca401de45b0e6d9a375992805ed80d520000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P0 bigint,@P1 bigint,@P2 int)SELECT "scid","start_time","end_time","rate" FROM sqlth_sce WHERE "start_time"&lt;=@P0 AND "end_time"&gt;=@P1 AND ("scid"=@P2) ORDER BY "start_time" ASC                           </inputbuf>
    </process>
    <process id="process1b9b4aaf088" taskpriority="0" logused="244" waitresource="KEY: 6:72057594052214784 (cdf919e30a10)" waittime="1631" ownerId="113946026" transactionname="implicit_transaction" lasttranstarted="2020-06-15T21:18:14.623" XDES="0x1b99f418490" lockMode="X" schedulerid="6" kpid="12736" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-15T21:18:14.627" lastbatchcompleted="2020-06-15T21:18:14.627" lastattention="1900-01-01T00:00:00.627" clientapp="Microsoft SQL Server JDBC Driver" hostname="050PNIGS003V001" hostpid="0" loginname="IgnitionSheppSql" isolationlevel="read committed (2)" xactid="113946026" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="100" stmtend="316" sqlhandle="0x020000001b7b640375aca11156ce340b79e5c93f0067fab70000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P0 bigint,@P1 int,@P2 int,@P3 bigint,@P4 bigint)UPDATE sqlth_sce SET "end_time"=@P0 WHERE "scid"=@P1 and "rate"=@P2 and "start_time"&lt;=@P3 and "end_time"&gt;=@P4                                           </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <ridlock fileid="1" pageid="63599713" dbid="6" objectname="F1-FD-DB.dbo.sqlth_sce" id="lock1b995e8df00" mode="X" associatedObjectId="72057594050117632">
      <owner-list>
        <owner id="process1b9b4aaf088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process1b9b4ab9c28" mode="S" requestType="wait" />
      </waiter-list>
    </ridlock>
    <ridlock fileid="1" pageid="63599713" dbid="6" objectname="F1-FD-DB.dbo.sqlth_sce" id="lock1b995e8df00" mode="X" associatedObjectId="72057594050117632">
      <owner-list>
        <owner id="process1b9b4ab9c28" mode="S" requestType="wait" />
      </owner-list>
      <waiter-list>
        <waiter id="process1b9b4dd88c8" mode="S" requestType="wait" />
      </waiter-list>
    </ridlock>
    <keylock hobtid="72057594052214784" dbid="6" objectname="F1-FD-DB.dbo.sqlth_sce" indexname="sqlth_sceend_timendx" id="lock1b99dc7c700" mode="S" associatedObjectId="72057594052214784">
      <owner-list>
        <owner id="process1b9b4dd88c8" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process1b9b4aaf088" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

[JordanCClark]

SQL deadlocks on accessing sqlth_sce

We’re expriencing quite some DB errors due to deadlocks in one of our projects (see screenshots below). After executing DBCC TRACEON (1204, -1) on the SQL server, to trace the deadlocks, I was able to get a log from it: deadlock.log (11.5 KB) It appears that a read and a write to the sqlth_sce table are causing the issue. But I don’t get when or why the system will use that table. What can we investigate to find the root cause of these errors? This system is running 7.9.11 [afbeelding] [af…