SQL statement used to view deadlocks

Source: Internet
Author: User

  1. If exists (Select*FromDbo. sysobjectsWhereId = object_id (N'[Dbo]. [sp_who_lock]')AndOBJECTPROPERTY (id, N'Isprocedure') = 1)
  2. Drop Procedure[Dbo]. [sp_who_lock]
  3. GO
  4. /*************************************** ************************************
  5. // Create:
  6. // Date:
  7. // Modify:
  8. // Description: view the blocking and deadlock conditions in the database
  9. **************************************** ***********************************/
  10. Use master
  11. Go
  12. Create ProcedureSp_who_lock
  13. As
  14. Begin
  15. Declare@ SpidInt, @ BlInt,
  16. @ IntTransactionCountOnEntryInt,
  17. @ IntRowcountInt,
  18. @ IntCountPropertiesInt,
  19. @ IntCounterInt
  20. Create Table# Tmp_lock_who (
  21. IdIntIdentity (1, 1 ),
  22. SpidSmallint,
  23. BlSmallint)
  24. IF @ ERROR <> 0RETURN@ ERROR
  25. Insert Into# Tmp_lock_who (spid, bl)Select0, blocked
  26. From(Select*FromSysprocessesWhereBlocked> 0)
  27. Where NotExists (Select*From(Select*FromSysprocessesWhereBlocked> 0) B
  28. WhereA. blocked = spid)
  29. Union SelectSpid, blockedFromSysprocessesWhereBlocked> 0
  30. IF @ ERROR <> 0RETURN@ ERROR
  31. -- Find the number of records in the temporary table
  32. Select@ IntCountProperties =Count(*), @ IntCounter = 1
  33. From# Tmp_lock_who
  34. IF @ ERROR <> 0RETURN@ ERROR
  35. If @ intCountProperties = 0
  36. Select 'No blocking and deadlock information' AsMessage
  37. -- Start of Loop
  38. While @ intCounter <= @ intCountProperties
  39. Begin
  40. -- Retrieve the first record
  41. Select@ Spid = spid, @ bl = bl
  42. From# Tmp_lock_whoWhereId = @ intCounter
  43. Begin
  44. If @ spid = 0
  45. Select 'The cause of database deadlock is :'+CAST(@ BlAS VARCHAR(10) +'Process number, and the SQL syntax executed by it is as follows'
  46. Else
  47. Select 'Process ID :'+CAST(@ SpidAS VARCHAR(10) +'Be'+'Process ID :'+CAST(@ BlAS VARCHAR(10) +'Blocking, the SQL syntax executed by the current process is as follows'
  48. Dbcc inputbuffer (@ bl)
  49. End
  50. -- Move the loop pointer down
  51. Set@ IntCounter = @ intCounter + 1
  52. End
  53. Drop Table# Tmp_lock_who
  54. Return0
  55. End

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.