Post reply

Exchange Event in SQL Profiler

  • January 14, 2021 at 5:28 pm

    #3833444

    Hi all.

    I received a Deadlock Graph and saw that I have Exchange Event in it.

    I read that it means the problem with parallelism.

    I have such a query:


    SELECT *
    FROM TABLE Account
    WHERE ID NOT INT (SELECT *
    FROM TABLE Account
    WHERE ...)


    Could this be the reason in the subqueries and how can it be solved?

    Attachments:
    You must be logged in to view attached files.

  • January 14, 2021 at 8:23 pm

    #3833517

    Seeing the entire deadlock graph would help.  it is not likely that the query is deadlocking itself, but your query and another have a conflict that requires one of them to roll back and the SELECT was deemed the "easier" one to roll back.

    To resolve the deadlock (moreso, to resolve future deadlocks), you need to determine what caused the deadlock.

    It is not likely that the nested select is the cause of the deadlock.  It is much more likely that your query selecting data while another query was changing the data.  In this case, a SELECT will almost always be chosen to be the victim process because a SELECT is trivial to roll back.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • January 18, 2021 at 11:40 am

    #3834545

    I have already seen that this query was blocked by the same query.

    I have an API using which users receive responses.

    At the same time, different users want to receive the same information. Do we have any way to resolve this situation?

    Thanks.

  • January 26, 2021 at 2:17 am

    #3837440

    Christi1711,

    CXPacket waits are often normal and, by themselves do not indicate a problem. Handling deadlocks is a vast subject.

    I would suggest investigating the use of a more optimistic isolation level for your transactions. Read Committed Snapshot Isolation allows queries to run based on row versions stored in tempdb. With this isolation level, readers don't block writers and writers don't block readers.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply