none
Chain of Transaction RRS feed

  • Question

  • Hi,
    We have a chain of transactions which need to be completed next
    And the deadlock has occurred
    What is the solution?
    Sunday, June 7, 2015 4:44 PM

Answers

All replies

  • dbcc traceon(1222,1204) will record details in error log.

    Avoid deadlocks:- Refer
    1) follow the consistent access pattern

    2) database modification:- do not allow user use data during transactions
    3) avoid cursors
    4)keep transactions small
    5) reduce transaction time
    6)controlling lock escalation

    7)choose appropriate isolation level:- use read committed so reduce locking contention.
    8)consider using bound connections   Refer


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue



    • Edited by AV111 Sunday, June 7, 2015 5:24 PM
    Sunday, June 7, 2015 5:17 PM
  • Thanks , But What should be done to solve the problem of transaction?
    Sunday, June 7, 2015 5:30 PM
  • Now deadlock is occur and how to break this chain?

    If we assume that the situation will be re-run entire transaction.


    • Edited by Arash_89 Sunday, June 7, 2015 5:40 PM
    Sunday, June 7, 2015 5:37 PM

  • exec sp_who2 active

    Look at the blocked by column. That will list the spid that is most likely blocking everyone else.

    then run KILL <put spid here>.

    Then rerun sp_who2 again to see if the lock is resolved. Sometimes you need to do this 2-3 times.


    This stops the immediate problem but it doesn't prevent it from happenning again.

    Also keep in mind that all the transations that were running need to be restarted.

    http://publib.boulder.ibm.com/tividd/td/BSM/SC32-9084-00/en_US/HTML/bsmd240.htm


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue



    • Edited by AV111 Sunday, June 7, 2015 5:57 PM
    Sunday, June 7, 2015 5:49 PM
  • http://publib.boulder.ibm.com/tividd/td/BSM/SC32-9084-00/en_US/HTML/bsmd240.htm

    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Deadlocking is a condition that occurs when two users or sessions have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. The SQL server automatically detects and resolves deadlocks. If a deadlock is encountered, one of the processes is terminated, allowing the other process to continue

    But if every process terminated,exactly this process for complete need to resource from next process.

    Now what we do?


    Sunday, June 7, 2015 6:00 PM
  • can share spids using dbcc inputbuffer


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Sunday, June 7, 2015 6:07 PM
  • Dinesh

    You have a bad habit of posting to almost all forum threads and I am pretty sure you don't know answer so can you stop wasting time none of your post would even slightly answer what OP has asked

    Aarsh,

    Posting some diagram is not going to provide you solution you have posted just that you are getting deadlock and its almost impossible to find solution without looking at deadlock graph so please post it


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Sunday, June 7, 2015 6:45 PM
    Moderator
  • Good day Arash,

    Please give us more information. you gave us only general story about the issue. we understand that you claim that deadlock has occurred. how did you check it? please give us the testing and the results that lead you to the conclusion. what is you actual code that leads to the deadlock? and so on :-)

    For general story regarding deadlock we can only point you to general articles regarding the issue. In this case you can find them yourself with Google :-). here is some good starting point for learning about deadlock and monitoring the issue:

    >> http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx

    >> https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    Friday, July 17, 2015 1:42 PM
    Moderator