{"id":2172,"date":"2012-06-08T14:40:55","date_gmt":"2012-06-08T14:40:55","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2172"},"modified":"2012-12-28T10:37:59","modified_gmt":"2012-12-28T10:37:59","slug":"%e6%ad%bb%e9%94%81%e5%8e%9f%e5%9b%a0%e7%9a%84%e6%8e%92%e6%9f%a5","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/06\/08\/%e6%ad%bb%e9%94%81%e5%8e%9f%e5%9b%a0%e7%9a%84%e6%8e%92%e6%9f%a5\/","title":{"rendered":"\u6b7b\u9501\u539f\u56e0\u7684\u6392\u67e5"},"content":{"rendered":"

1\uff0c\u6253\u5f00\u8ddf\u8e2a\uff0c\u5199\u5165\u65e5\u5fd7
\nDBCC<\/span> TRACEON <\/span>(<\/span>3605<\/span>,<\/span>1222<\/span>,<\/span>-1<\/span>) –3605\u5199\u5165errorlog,1222\u6b7b\u9501<\/span>
\nDBCC TRACEON(1222,-1) \/\u82e5\u5728\u542f\u52a8\u65f6\uff0c\u52a0-T 1222
\n\u540c\u65f6\uff0c\u53ef\u5f00profile\u4e2d\u7684Deadlock graph\u8ddf\u8e2a\uff0c\u4ee5\u56fe\u5f62\u5316
\n2\uff0c\u5206\u6790\u5b89\u88c5\u76ee\u5f55\u4e0b\u751f\u6210\u7684\u65e5\u5fd7
\n   1\uff09\u786e\u5b9a\u6b7b\u9501\u7684\u8d44\u6e90, \u636e\u5bf9\u8c61\u4e0d\u540c\u67e5\u770b\u65b9\u5f0f\u4e0d\u540c\u3002<\/a>
\nDBCC <\/span>TRACEON<\/span>(<\/span>3604<\/span>)<\/span><\/p>\n

\n\tDBCC <\/span>PAGE<\/span>(<\/span>8<\/span>,<\/span>1<\/span>,<\/span>96<\/span>,<\/span>1<\/span>)<\/span>
\n\tDBCC <\/span>TRACEOFF<\/span>(<\/span>3604<\/span>)<\/span><\/div>\n

\u6b7b\u9501\u4ea7\u751f\u7684\u524d\u63d0\uff1a\u53cc\u65b9\u4e92\u5360\u6709\u4e86\u5bf9\u65b9\u6240\u9700\u6c42\u7684\u8d44\u6e90\uff0c\u82e5\u8d44\u6e90\u5e76\u4e0d\u5fc5\u8981\uff0c\u53ef\u8fc7\u6ee4\u6389\u3002
\n\u8d44\u6e90\u4e0d\u5fc5\u8981\uff1a1\uff09\u626b\u63cf\u4e86\u5bf9\u65b9\u7684\u8d44\u6e90\uff0c\u626b\u63cf\u8fc7\u7684\u5c31\u4f1a\u52a0\u9501\uff0c\u907f\u514d\u88ab\u626b\u63cf\u5230\uff0c\u5982\u52a0\u7d22\u5f15\u7b49\u3002
\n3,\u6b7b\u9501\u5904\u7406\u65b9\u6cd5\uff1a
\n 1\uff09\u4ece\u6027\u80fd\u51fa\u53d1\uff0c\u4f18\u5316sql
\n 2\uff09\u4ece\u4e1a\u52a1\u903b\u8f91\u51fa\u53d1\uff0c\u770b\u662f\u5426\u53ef\u53bb\u6389\u5bf9\u6b7b\u9501\u8d44\u6599\u7684\u5173\u8054\u3002
\n 3\uff09\u82e5\u8fd8\u662f\u4e0d\u80fd\u89e3\u51b3\uff0c\u6b7b\u9501\u662f\u6b63\u5e38\u60c5\u51b5\uff0c\u907f\u514d\u4e0d\u4e86\uff0c\u4f46\u53ef\u907f\u514d\u8f93\u51fa1205\u9519\u8bef\u4fe1\u606f\u7ed9\u5ba2\u6237\u7aef\uff0c\u65b9\u6cd5\u5c31\u662f\u52a0\u4e0atry catch,\u53ef\u4ee5\u7b49\u4e00\u4f1a\u513f\u518d\u91cd\u65b0\u6267\u884c\u3002
\n\u5982\uff1a<\/p>\n

\r\n\/*\u907f\u514d\u6b7b\u9501\u663e\u793a\u7ed9\u5ba2\u6237\u7aef<\/a> *\/\r\nDECLARE @retries INT ;\r\nSET @retries = 4 ;\r\nWHILE ( @retries > 0 )\r\n    BEGIN\r\n        BEGIN TRY\r\n            BEGIN TRANSACTION ;\r\n            -- place sql code here\r\n            SET @retries = 0 ;\r\n            COMMIT TRANSACTION ;\r\n        END TRY\r\n        BEGIN CATCH\r\n            -- Error is a deadlock\r\n            IF ( ERROR_NUMBER() = 1205 )\r\n                SET @retries = @retries - 1 ;\r\n                -- Error is not a deadlock\r\n            ELSE\r\n                BEGIN\r\n                    DECLARE @ErrorMessage NVARCHAR(4000) ;\r\n                    DECLARE @ErrorSeverity INT ;\r\n                    DECLARE @ErrorState INT ;\r\n                    SELECT  @ErrorMessage = ERROR_MESSAGE() ,\r\n                            @ErrorSeverity = ERROR_SEVERITY() ,\r\n                            @ErrorState = ERROR_STATE() ;\r\n                    -- Re-Raise the Error that caused the problem\r\n                    RAISERROR (@ErrorMessage, -- Message text.\r\n                    @ErrorSeverity, -- Severity.\r\n                    @ErrorState -- State.\r\n                    ) ;\r\n                    SET @retries = 0 ;\r\n                END\r\n            IF XACT_STATE() <> 0\r\n                ROLLBACK TRANSACTION ;\r\n        END CATCH ;\r\n    END ;\r\nGO\r\n\r\n<\/pre>\n
<\/p>\n
\r\n\/*\u6b7b\u9501\u6a21\u62df\r\n1,\u5efa\u7acb\u6570\u636e\r\n2\uff0c\u8fde\u7eed\u4e24\u4e2a\u4e8b\u52a1\r\n*\/\r\ndrop table Employee_Demo_Heap\r\ngo\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE TABLE [Employee_Demo_Heap](\r\n\t[EmployeeID] [int] NOT NULL,\r\n\t[NationalIDNumber] [nvarchar](15) NOT NULL,\r\n\t[ContactID] [int] NOT NULL,\r\n\t[LoginID] [nvarchar](256) NOT NULL,\r\n\t[ManagerID] [int] NULL,\r\n\t[Title] [nvarchar](50) NOT NULL,\r\n\t[BirthDate] [datetime] NOT NULL,\r\n\t[MaritalStatus] [nchar](1) NOT NULL,\r\n\t[Gender] [nchar](1) NOT NULL,\r\n\t[HireDate] [datetime] NOT NULL,\r\n\t[ModifiedDate] [datetime] NOT NULL   DEFAULT (getdate()),\r\n CONSTRAINT [PK_Employee_EmployeeID_Demo_Heap] PRIMARY KEY nonCLUSTERED \r\n(\r\n\t[EmployeeID] ASC\r\n)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, \r\nIGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGO\r\n\r\nCREATE NONCLUSTERED INDEX [IX_Employee_ManagerID_Demo_Heap] ON [Employee_Demo_Heap] \r\n(\r\n\t[ManagerID] ASC\r\n)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\nGO\r\nCREATE NONCLUSTERED INDEX [IX_Employee_ModifiedDate_Demo_Heap] ON [Employee_Demo_Heap] \r\n(\r\n\t[ModifiedDate] ASC\r\n)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\nGO\r\ninsert into Employee_Demo_Heap\r\nselect [EmployeeID] ,\r\n\t[NationalIDNumber] ,\r\n\t[ContactID] ,\r\n\t[LoginID] ,\r\n\t[ManagerID],\r\n\t[Title] ,\r\n\t[BirthDate] ,\r\n\t[MaritalStatus] ,\r\n\t[Gender] ,\r\n\t[HireDate] ,\r\n\t[ModifiedDate] from HumanResources.Employee\r\ngo\r\n\r\n\r\n\u73b0\u5728\u5c31\u7528\u4e0b\u9762\u8fd9\u7ec4\u811a\u672c\u6a21\u62df\u51fa\u4e00\u4e2a\u6b7b\u9501\u6765\u3002\u5728\u4e00\u4e2a\u8fde\u63a5\u91cc\uff0c\u8fd0\u884c\u4e0b\u9762\u7684\u8bed\u53e5\u3002\u53cd\u590d\u5f00\u542f\u4e8b\u52a1\u3002\u5728\u8fd9\u4e2a\u4e8b\u52a1\u91cc\uff0c\u5148\u4fee\u6539\u4e00\u6761NationalIDNumber=‘480951955’\u7684\u8bb0\u5f55\uff0c\u7136\u540e\u518d\u628a\u5b83\u67e5\u8be2\u51fa\u6765\u3002\u505a\u5b8c\u4ee5\u540e\uff0c\u63d0\u4ea4\u4e8b\u52a1\u3002\r\nset nocount on\r\ngo\r\nwhile 1=1\r\nbegin\r\nbegin tran\r\nupdate dbo.Employee_Demo_Heap\r\nset BirthDate = getdate()\r\nwhere NationalIDNumber = '480951955'\r\nselect * from dbo.Employee_Demo_Heap\r\nwhere NationalIDNumber = '480951955'\r\ncommit tran\r\nend\r\n\t\u5728\u53e6\u5916\u4e00\u4e2a\u8fde\u63a5\u91cc\uff0c\u4e5f\u8fd0\u884c\u8fd9\u4e9b\u8bed\u53e5\u3002\u552f\u4e00\u7684\u5dee\u522b\u662f\u8fd9\u6b21\u4fee\u6539\u548c\u67e5\u8be2\u7684\u662f\u53e6\u4e00\u6761NationalIDNumber = ‘407505660’\u7684\u8bb0\u5f55\u3002\r\nset nocount on\r\ngo\r\nwhile 1=1\r\nbegin\r\nbegin tran\r\nupdate dbo.Employee_Demo_Heap\r\nset BirthDate = getdate()\r\nwhere NationalIDNumber = '407505660'\r\nselect * from dbo.Employee_Demo_Heap \r\nwhere NationalIDNumber = '407505660'\r\ncommit tran\r\nend\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
1\uff0c\u6253\u5f00\u8ddf\u8e2a\uff0c\u5199\u5165\u65e5\u5fd7 DBCC TRACEON (3605,1222,-1) &#821 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2172","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2172"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2172\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}