sql server

Elon Musk 161 Reputation points
2022-11-08T02:38:23.04+00:00

In my splunk log record, I find deadlock issue happening every 10 minutes

{"@l":"Information","@mt":"{@Response}","Response":{"Uri":"http://mytdeliveryopsapi.tesla.cn/api/widget/finance/Info/RN802421737","Path":"/api/widget/finance/Info/RN802421737","Body":"{\"Data\":null,\"Success\":false,\"ErrorData\":{\"FriendlyMessage\":\"System.Data.SqlClient.SqlException (0x80131904): Snapshot isolation transaction failed in database 'WarpDelivery' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.\n at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__126_0(Task1 result)\\n at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()\n at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\n--- End of stack trace from previous location ---\n at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)\n--- End of stack trace from previous location ---\n at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 473\n at DeliveryOpsAPI.Repository.WarpDelivery.CustomerReadinessRepository.GetReadinessDetailAsync(String referenceNumber) in /app/src/DeliveryOpsAPI.Api/Repository/WarpDelivery/CustomerReadinessRepository.cs:line 126\n at DeliveryOpsAPI.Service.WarpDelivery.WidgetService.GetFinanceOfferAsync(String referenceNumber, GetFinanceOfferRequest request, String email) in /app/src/DeliveryOpsAPI.Api/Managers/WarpDelivery/WidgetService.cs:line 1126\nClientConnectionId:153acfb9-cde1-40f3-b721-ed6afbadc390\nError Number:3961,State:1,Class:16\nClientConnectionId before routing:c5aa4d18-0061-48e2-86b7-b558221248d6\nRouting Destination:PVG03P1WRPDB02.teslamotors.com,1433\",\"InternalErrorMessage\":\"System.Data.SqlClient.SqlException (0x80131904): Snapshot isolation transaction failed in database 'WarpDelivery' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.\n at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__126_0(Task1 result)\\n at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()\n at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\n--- End of stack trace from previous location ---\n at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)\n--- End of stack trace from previous location ---\n at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 473\n at DeliveryOpsAPI.Repository.WarpDelivery.CustomerReadinessRepository.GetReadinessDetailAsync(String referenceNumber) in /app/src/DeliveryOpsAPI.Api/Repository/WarpDelivery/CustomerReadinessRepository.cs:line 126\n at DeliveryOpsAPI.Service.WarpDelivery.WidgetService.GetFinanceOfferAsync(String referenceNumber, GetFinanceOfferRequest request, String email) in /app/src/DeliveryOpsAPI.Api/Managers/WarpDelivery/WidgetService.cs:line 1126\nClientConnectionId:153acfb9-cde1-40f3-b721-ed6afbadc390\nError Number:3961,State:1,Class:16\nClientConnectionId before routing:c5aa4d18-0061-48e2-86b7-b558221248d6\nRouting

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 40,156 Reputation points
    2022-11-08T06:10:46.717+00:00

    I find deadlock issue happening every 10 minutes

    The error message you posted has nothing to do with deadlocks.

    Snapshot isolation transaction failed in database 'WarpDelivery' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction

    Have you read that part of the erro message; that's the root cause. Some process modified table defintion during the transaction.

    0 comments No comments

  2. Elon Musk 161 Reputation points
    2022-11-08T06:12:47.39+00:00

    How can I get the details so I can engage my app team to turn these queries


  3. Seeya Xi-MSFT 16,426 Reputation points
    2022-11-08T09:01:23.95+00:00

    Hi @Elon Musk ,

    You get this error "very often", it suggests someone has made a bad design choice somewhere: a table's metadata really isn't supposed to change "very often" in the first place, and where this can be expected to be the case, using snapshot isolation is probably not the best choice. You can override this with the READCOMMITTEDLOCK

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.