{"id":502,"date":"2025-07-23T08:38:59","date_gmt":"2025-07-22T23:38:59","guid":{"rendered":"https:\/\/sejiwon.com\/?p=502"},"modified":"2025-07-23T08:38:59","modified_gmt":"2025-07-22T23:38:59","slug":"mssql-deadlock%ec%9b%90%ec%9d%b8%ed%8c%8c%ec%95%85","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=502","title":{"rendered":"Mssql deadlock\uc6d0\uc778\ud30c\uc545"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\">1.&nbsp;<strong>SQL Server Error Log \ub610\ub294 Deadlock Graph \ud655\uc778<\/strong><\/h3>\n\n\n\n<p>SQL Server\ub294 Deadlock \ubc1c\uc0dd \uc2dc \ud574\ub2f9 \uc815\ubcf4\ub97c&nbsp;<strong>SQL Server Error Log<\/strong>\ub098&nbsp;<strong>Extended Events<\/strong>,&nbsp;<strong>Trace<\/strong>,&nbsp;<strong>System Health Session<\/strong>\ub4f1\uc5d0 \uae30\ub85d\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u2705 \ubc29\ubc95 A: \uc2dc\uc2a4\ud15c \ud5ec\uc2a4 \uc138\uc158(System Health Session)\uc5d0\uc11c \ud655\uc778<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT\n    XEvent.value('(event\/data[@name=\"resource\"]\/value)[1]', 'VARCHAR(MAX)') AS Resource,\n    XEvent.value('(event\/data[@name=\"objectname\"]\/value)[1]', 'VARCHAR(MAX)') AS ObjectName,\n    XEvent.value('(event\/data[@name=\"database_name\"]\/value)[1]', 'VARCHAR(MAX)') AS DatabaseName,\n    XEvent.value('(event\/action[@name=\"sql_text\"]\/value)[1]', 'VARCHAR(MAX)') AS SQLText,\n    XEvent.value('(event\/data[@name=\"transactionname\"]\/value)[1]', 'VARCHAR(MAX)') AS TransactionName,\n    DeadlockGraph\nFROM (\n    SELECT\n        XEvent.query('.') AS XEvent,\n        DeadlockGraph.query('.') AS DeadlockGraph\n    FROM (\n        SELECT CAST(target_data AS XML) AS TargetData\n        FROM sys.dm_xe_session_targets st\n        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address\n        WHERE s.name = 'system_health'\n          AND st.target_name = 'ring_buffer'\n    ) AS Data\n    CROSS APPLY TargetData.nodes('\/\/RingBufferTarget\/event[@name=\"xml_deadlock_report\"]') AS XEventData(XEvent)\n    CROSS APPLY XEventData.XEvent.nodes('.') AS Deadlock(DeadlockGraph)\n) AS DeadlockEvents;\n<\/code><\/pre>\n\n\n\n<p>\uc774 \ucffc\ub9ac\ub294 Deadlock\uc774 \ubc1c\uc0dd\ud55c \uac1d\uccb4, SQL \ubb38\uc7a5, \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ub4f1\uc744 \ubcf4\uc5ec\uc90d\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2.&nbsp;<strong>SQL Server Profiler \ub610\ub294 Extended Events\ub85c \ucd94\uc801<\/strong><\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">\u2705 Extended Events Deadlock \ucea1\ucc98 \uc608\uc2dc<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>CREATE EVENT SESSION DeadlockSession ON SERVER\nADD EVENT sqlserver.lock_deadlock(\n    ACTION(sqlserver.sql_text, sqlserver.session_id))\nADD TARGET package0.ring_buffer;\nGO\n\nALTER EVENT SESSION DeadlockSession ON SERVER STATE = START;\n<\/code><\/pre>\n\n\n\n<p>\uc774 \ud6c4&nbsp;<code>sys.dm_xe_session_targets<\/code>\ub97c \uc870\ud68c\ud574 Deadlock \uc815\ubcf4\ub97c XML\ub85c \ubd84\uc11d\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3.&nbsp;<strong>\ub370\ub4dc\ub77d \uadf8\ub798\ud504(XML) \ubd84\uc11d<\/strong><\/h3>\n\n\n\n<p>XML Deadlock Report\ub97c \ubcf4\uba74 \ub2e4\uc74c\uc744 \uc54c \uc218 \uc788\uc2b5\ub2c8\ub2e4:<\/p>\n\n\n\n<ul>\n<li><strong>Victim (\ud76c\uc0dd\uc790):<\/strong>&nbsp;deadlock victim\uc73c\ub85c \uc120\uc815\ub41c \uc138\uc158<\/li>\n\n\n\n<li><strong>Owner \/ Waiter:<\/strong>&nbsp;\uc5b4\ub5a4 \uc138\uc158\uc774 \uc5b4\ub5a4 \ub9ac\uc18c\uc2a4\ub97c \uac00\uc9c0\uace0 \uc788\uace0 \uae30\ub2e4\ub9ac\ub294\uc9c0<\/li>\n\n\n\n<li><strong>Resource Type:<\/strong>&nbsp;\uc608:&nbsp;<code>KEY<\/code>,&nbsp;<code>RID<\/code>,&nbsp;<code>PAGE<\/code>,&nbsp;<code>OBJECT<\/code>&nbsp;\ub4f1<\/li>\n\n\n\n<li><strong>Lock Mode:<\/strong>&nbsp;<code>S<\/code>,&nbsp;<code>X<\/code>,&nbsp;<code>U<\/code>,&nbsp;<code>IX<\/code>&nbsp;\ub4f1<\/li>\n<\/ul>\n\n\n\n<p>\ud83d\udc49&nbsp;<code>resource-list<\/code>\uc640&nbsp;<code>process-list<\/code>&nbsp;\ud56d\ubaa9\uc774 \uc911\uc694\ud558\uba70, \ub450 \uc138\uc158\uc774 \uc5b4\ub5a4 \uc21c\uc11c\ub85c \ub77d\uc744 \uc5bb\uc73c\ub824\uace0 \ud558\ub2e4 \ucda9\ub3cc\ud588\ub294\uc9c0 \ucd94\uc801\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udca1 Deadlock \uc6d0\uc778 \uc608\uc2dc<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\uc6d0\uc778<\/th><th>\uc124\uba85<\/th><\/tr><\/thead><tbody><tr><td>\ub77d \uc21c\uc11c \ubd88\uc77c\uce58<\/td><td>\uc138\uc158 A\ub294 \ud14c\uc774\ube14 A \u2192 B \uc21c\uc73c\ub85c, \uc138\uc158 B\ub294 \ud14c\uc774\ube14 B \u2192 A \uc21c\uc73c\ub85c \uc811\uadfc<\/td><\/tr><tr><td>\uacfc\ub3c4\ud55c \ud2b8\ub79c\uc7ad\uc158 \ubc94\uc704<\/td><td>\ud2b8\ub79c\uc7ad\uc158 \ubc94\uc704\uac00 \ud06c\uac70\ub098 \uc624\ub798 \uac78\ub824 \ub77d \ubcf4\uc720 \uc2dc\uac04\uc774 \uae38\uc5b4\uc9d0<\/td><\/tr><tr><td>\uc778\ub371\uc2a4 \ubbf8\uc0ac\uc6a9<\/td><td>\ud14c\uc774\ube14 \uc2a4\uce94\uc73c\ub85c \uc778\ud574 \ub354 \ub9ce\uc740 \ud589\uc5d0 \ub77d\uc774 \uac78\ub9bc<\/td><\/tr><tr><td>\uc798\ubabb\ub41c \uaca9\ub9ac \uc218\uc900<\/td><td>\uc608: Repeatable Read\ub098 Serializable\ub85c \uc778\ud55c \uacfc\ub3c4\ud55c \ub77d<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udee0 \ud574\uacb0 \ubc29\ubc95<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\ubc29\ubc95<\/th><th>\uc124\uba85<\/th><\/tr><\/thead><tbody><tr><td>\ud2b8\ub79c\uc7ad\uc158 \ubc94\uc704 \ucd95\uc18c<\/td><td><code>BEGIN TRAN<\/code>\uacfc&nbsp;<code>COMMIT<\/code>&nbsp;\uc0ac\uc774\uc758 \ucf54\ub4dc \ucd5c\uc18c\ud654<\/td><\/tr><tr><td>\ub77d \uc21c\uc11c \ud1b5\uc77c<\/td><td>\uc5ec\ub7ec \ud14c\uc774\ube14\uc744 \uc811\uadfc\ud560 \ub54c\ub294 \ud56d\uc0c1 \ub3d9\uc77c\ud55c \uc21c\uc11c\ub85c \ub77d \uc694\uccad<\/td><\/tr><tr><td>\uc801\uc808\ud55c \uc778\ub371\uc2a4 \uc0ac\uc6a9<\/td><td>\ud544\uc694\ud55c \uc778\ub371\uc2a4\ub97c \ud1b5\ud574 \ud14c\uc774\ube14 \uc2a4\uce94 \ucd5c\uc18c\ud654<\/td><\/tr><tr><td>\ub370\ub4dc\ub77d \ub9ac\ud2b8\ub77c\uc774 \ub85c\uc9c1<\/td><td>\uc560\ud50c\ub9ac\ucf00\uc774\uc158\uc5d0\uc11c \ub370\ub4dc\ub77d \uc5d0\ub7ec(<code>1205<\/code>) \ubc1c\uc0dd \uc2dc \uc7ac\uc2dc\ub3c4 \ub85c\uc9c1 \uad6c\ud604<\/td><\/tr><tr><td>READ COMMITTED SNAPSHOT \uc0ac\uc6a9<\/td><td>\ubc84\uc804 \uae30\ubc18 \uc77d\uae30 \uc801\uc6a9\uc73c\ub85c \ub77d \ucda9\ub3cc \ubc29\uc9c0 \uac00\ub2a5<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>1.&nbsp;SQL Server Error Log \ub610\ub294 Deadlock Graph \ud655\uc778 SQL Server\ub294 Deadlock \ubc1c\uc0dd \uc2dc \ud574\ub2f9 \uc815\ubcf4\ub97c&nbsp;SQL Server Error Log\ub098&nbsp;Extended Events,&nbsp;Trace,&nbsp;System Health Session\ub4f1\uc5d0 \uae30\ub85d\ud569\ub2c8\ub2e4. \u2705 \ubc29\ubc95 A: \uc2dc\uc2a4\ud15c \ud5ec\uc2a4 \uc138\uc158(System Health Session)\uc5d0\uc11c \ud655\uc778 SELECT XEvent.value(&#8216;(event\/data[@name=&#8221;resource&#8221;]\/value)[1]&#8217;, &#8216;VARCHAR(MAX)&#8217;) AS Resource, XEvent.value(&#8216;(event\/data[@name=&#8221;objectname&#8221;]\/value)[1]&#8217;, &#8216;VARCHAR(MAX)&#8217;) AS ObjectName, XEvent.value(&#8216;(event\/data[@name=&#8221;database_name&#8221;]\/value)[1]&#8217;, &#8216;VARCHAR(MAX)&#8217;) AS DatabaseName, XEvent.value(&#8216;(event\/action[@name=&#8221;sql_text&#8221;]\/value)[1]&#8217;, &#8216;VARCHAR(MAX)&#8217;) AS SQLText, XEvent.value(&#8216;(event\/data[@name=&#8221;transactionname&#8221;]\/value)[1]&#8217;, &#8216;VARCHAR(MAX)&#8217;) AS TransactionName, DeadlockGraph FROM (\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=502\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[11],"tags":[],"_links":{"self":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/502"}],"collection":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=502"}],"version-history":[{"count":1,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/502\/revisions"}],"predecessor-version":[{"id":503,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/502\/revisions\/503"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=502"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=502"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=502"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}