none
数据库发生死锁,怎么办??? 很奇怪的问题。 RRS feed

  • 问题

  • 存储过程A,自己锁自己。   存储过程B和存储过程C相互锁定。

    问题如下截图所示:

    问题一:存储过程为什么自己锁自己???   是因为 “sp_who_lock”这个存储过程检查的不对吗???

    问题二: 我该怎么样找到死锁发生的位置?  比如提示第333行。

          但是存储过程的执行方式是, C过程调用 D过程,D过程调用 E过程 这种方式。  显示行号以后,  我应该从哪一行开始数???

         从 Create proc语句,还是声明变量 declare ,还是 as begin  ...  中间的隔开的行又算不算???  
         SQLServer提示的行号,应该怎么看???   

    求大神帮忙啊。


    Science and technology is my lover.

    2015年1月4日 1:51

答案

  • Hi,

    A deadlock occurs in two steps. In the first, each of the two processes requests and acquires a lock. This will be within a transaction, explicit or not, and hence neither process will release these locks immediately. In the second step, each of the two processes requests a lock on the resource on which the competing session holds a lock that is incompatible with the requested lock.

    So I think you need to check all your store procedures.

    There is a document about Sql Deadlock, please refer to the link below:

    https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

    Hope it's useful for you.

    Best Regards,

    Michelle Ge

    2015年1月6日 9:07
    版主

全部回复

  • Hi,

    A deadlock occurs in two steps. In the first, each of the two processes requests and acquires a lock. This will be within a transaction, explicit or not, and hence neither process will release these locks immediately. In the second step, each of the two processes requests a lock on the resource on which the competing session holds a lock that is incompatible with the requested lock.

    So I think you need to check all your store procedures.

    There is a document about Sql Deadlock, please refer to the link below:

    https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

    Hope it's useful for you.

    Best Regards,

    Michelle Ge

    2015年1月6日 9:07
    版主
  • Same as https://social.msdn.microsoft.com/Forums/zh-CN/23c7b23e-aabd-4da9-ad19-13cc8a002414/-?forum=sqlserverzhchs, did you check responses there?
    2015年1月6日 14:22