# SQL Server deadlocked

**URL:** <https://forum.inductiveautomation.com/t/sql-server-deadlocked/35685>\
**Category:** Ignition\
**Created:** [June 15, 2020, 11:34pm UTC](https://forum.inductiveautomation.com/t/sql-server-deadlocked/35685 "2020-06-15T23:34:19Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tom.S](https://avatars.discourse-cdn.com/v4/letter/t/85f322/32.png) [@Tom.S](https://forum.inductiveautomation.com/u/Tom.S)\
**Post date:** [June 15, 2020, 11:34pm UTC](https://forum.inductiveautomation.com/t/sql-server-deadlocked/35685/1 "2020-06-15T23:34:19Z")

</div>

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…

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [June 15, 2020, 11:56pm UTC](https://forum.inductiveautomation.com/t/sql-server-deadlocked/35685/2 "2020-06-15T23:56:01Z")

</div>

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:

```auto
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/](https://blog.sqlauthority.com/2017/01/09/sql-server-get-historical-deadlock-information-system-health-extended-events/)

---

<div class="post-metadata">

**Author:** ![Tom.S](https://avatars.discourse-cdn.com/v4/letter/t/85f322/32.png) [@Tom.S](https://forum.inductiveautomation.com/u/Tom.S)\
**Post date:** [June 16, 2020, 12:56am UTC](https://forum.inductiveautomation.com/t/sql-server-deadlocked/35685/3 "2020-06-16T00:56:57Z")

</div>

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?

```auto
<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>

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 16, 2020, 12:11pm UTC](https://forum.inductiveautomation.com/t/sql-server-deadlocked/35685/4 "2020-06-16T12:11:41Z")

</div>

> [@SQL deadlocks on accessing sqlth\_sce](http://forum.inductiveautomation.com/t/sql-deadlocks-on-accessing-sqlth-sce/29717):
>
> 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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/0/0890950f345114a497a5a1b6388853a73d2a151c.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