Post reply

Deadlocks when high concurrent connections are running in parallel.

  • April 3, 2020 at 7:53 am

    #3740150

    I have a legacy process where users are performing a task in parallel. The business logic is that each user has to receive a unique set of records otherwise each user could be collecting the same products doubling the work.

    It has always worked & on a normal day to day operation we have anywhere from 500 to 1300 users with no deadlocks or incidents occurring.

    On our Performance testing environment we have been able to still have this operation working when there are 2000 concurrent users performing this task, however as soon as it increases and concurrent users reach around ~2600 our test fails due to deadlocks occurring.

    We currently have an Exclusive Lock (XLOCK) on the table while it selects the records and finishes the process before the lock is released. I identified that the deadlock is caused by this process and specifically due to the XLOCK.

    I've been reading that changing an XLOCK to ROWLOCK may resolve the issue but not sure if this will keep the uniqueness of data or cause any other problems.

    I also haven't tested in our PT environment yet to see if it can handle the ~2600 concurrent users but just after some extra advice or another way to potentially resolve.

    Any help would be appreciated.

     

     

     

     

     

     

     

  • April 3, 2020 at 9:57 am

    #3740168

    An exclusive lock on a table is quite drastic. Which isolationslevels are in play? http://thesqlgirl.com/2016/11/01/sql-transaction-isolation-levels/

  • April 5, 2020 at 11:12 pm

    #3740610

    Isolation level used is READ COMMITTED.

     

  • April 6, 2020 at 2:06 am

    #3740631

    I can only imagine that someone is starting an explicit transaction in the code and then using a mixed bag of SELECT, UPDATE, and possibly INSERT.  The only thing I can say without seeing the code is that the code must be written to execute faster and using a smaller transaction.  If you were to post the code, perhaps we could show you a possible fix where you wouldn't actually have to do an XLOCK in order to guarantee proper distribution of tasks with no overlap between calling parties.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 6, 2020 at 10:19 am

    #3740659

    Tava,

    Can you supply some more details of the processing involved?

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

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