{"id":508,"date":"2025-07-24T16:28:51","date_gmt":"2025-07-24T07:28:51","guid":{"rendered":"https:\/\/sejiwon.com\/?p=508"},"modified":"2025-07-25T09:39:52","modified_gmt":"2025-07-25T00:39:52","slug":"lock_deadlock-%ec%9d%b4%eb%b2%a4%ed%8a%b8%eb%a1%9c-sql_text-%ec%b0%be%ea%b8%b0","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=508","title":{"rendered":"lock_deadlock \uc774\ubca4\ud2b8\ub85c sql_text \ucc3e\uae30"},"content":{"rendered":"\n<p><code>lock_deadlock<\/code>&nbsp;\uc774\ubca4\ud2b8\uc5d0\uc11c&nbsp;<code>sql_text<\/code>\ub97c \ucc3e\uae30 \uc704\ud574\uc11c\ub294 SQL Server\uc5d0\uc11c \ud655\uc7a5 \uc774\ubca4\ud2b8(Extended Events)\ub97c \ud65c\uc6a9\ud558\ub294 \ubc29\ubc95\uc774 \uac00\uc7a5 \uc815\ud655\ud558\uace0 \uc2e4\uc6a9\uc801\uc785\ub2c8\ub2e4. \uc5ec\uae30\uc11c\ub294 SQL Server\uc5d0\uc11c Deadlock \ubc1c\uc0dd \uc2dc \uad00\ub828 SQL \ud14d\uc2a4\ud2b8(<code>sql_text<\/code>)\ub97c \ucd94\uc801\ud558\uace0 \uc870\ud68c\ud558\ub294 \ubc29\ubc95\uc744 \uc124\uba85\ud569\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\">\ud83d\udd0d Deadlock \ubc1c\uc0dd \uc2dc SQL \ud14d\uc2a4\ud2b8 \ucc3e\ub294 \ubc29\ubc95<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">\ubc29\ubc95 1:&nbsp;<strong>Extended Events \uc138\uc158 \uc0ac\uc6a9<\/strong><\/h4>\n\n\n\n<ol>\n<li><strong>Deadlock \ucd94\uc801\uc6a9 Extended Event \uc138\uc158 \uc0dd\uc131<\/strong><\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>CREATE EVENT SESSION [Deadlock_Capture] ON SERVER\nADD EVENT sqlserver.xml_deadlock_report\nADD TARGET package0.ring_buffer\nWITH (MAX_MEMORY=4096KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=5 SECONDS);\nGO\n\n-- \uc138\uc158 \uc2dc\uc791\nALTER EVENT SESSION [Deadlock_Capture] ON SERVER STATE = START;\n<\/code><\/pre>\n\n\n\n<ol start=\"2\">\n<li><strong>Deadlock \ubc1c\uc0dd \uc2dc XML \ubcf4\uace0\uc11c \ud655\uc778<\/strong><\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>-- \uac00\uc7a5 \ucd5c\uadfc deadlock xml \ubcf4\uace0\uc11c \uc870\ud68c\nSELECT \n    XEvent.value('(event\/data\/value)[1]', 'xml') AS DeadlockReport\nFROM \n    (SELECT CAST(target_data AS XML) AS TargetData\n     FROM sys.dm_xe_sessions AS s\n     JOIN sys.dm_xe_session_targets AS t\n         ON s.address = t.event_session_address\n     WHERE s.name = 'Deadlock_Capture'\n       AND t.target_name = 'ring_buffer') AS Data\nCROSS APPLY TargetData.nodes('\/\/RingBufferTarget\/event') AS XEventData(XEvent)\nWHERE XEvent.value('@name', 'varchar(100)') = 'xml_deadlock_report';\n<\/code><\/pre>\n\n\n\n<ol start=\"3\">\n<li><strong>Deadlock XML\uc5d0\uc11c&nbsp;<code>sql_text<\/code>&nbsp;\ucd94\ucd9c<\/strong><\/li>\n<\/ol>\n\n\n\n<p>XML\uc744 \uc5f4\uc5b4 \ubcf4\uba74&nbsp;<code>&lt;process-list&gt;<\/code>&nbsp;\uc544\ub798\uc5d0&nbsp;<code>&lt;inputbuf&gt;<\/code>&nbsp;\ub610\ub294&nbsp;<code>&lt;executionStack&gt;<\/code>&nbsp;\ud0dc\uadf8\uac00 \uc788\uc73c\uba70, \uc5ec\uae30\uc5d0 \ud574\ub2f9 \uc2dc\uc810\uc758 SQL \ubb38\uc7a5\uc774 \uae30\ub85d\ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4. \uc608:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>&lt;process id=\"...\" ...>\n    &lt;executionStack>\n        &lt;frame ...>SELECT * FROM Orders WHERE ...&lt;\/frame>\n    &lt;\/executionStack>\n    &lt;inputbuf>SELECT * FROM Orders WHERE ...&lt;\/inputbuf>\n&lt;\/process>\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">\ubc29\ubc95 2:&nbsp;<strong>System Health \uc138\uc158\uc5d0\uc11c Deadlock \uc870\ud68c (\uc790\ub3d9 \uc218\uc9d1)<\/strong><\/h4>\n\n\n\n<p>SQL Server\ub294 \uae30\ubcf8\uc801\uc73c\ub85c&nbsp;<code>system_health<\/code>&nbsp;\uc138\uc158\uc5d0\uc11c deadlock \uc815\ubcf4\ub97c \uc218\uc9d1\ud569\ub2c8\ub2e4. \uc774 \uc815\ubcf4\ub97c \ud65c\uc6a9\ud560 \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>-- \ucd5c\uadfc system_health\uc5d0\uc11c deadlock \uc815\ubcf4 \uac00\uc838\uc624\uae30\nSELECT \n    XEvent.value('(event\/data\/value)[1]', 'xml') AS DeadlockReport\nFROM \n    (SELECT CAST(target_data AS XML) AS TargetData\n     FROM sys.dm_xe_session_targets t\n     JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address\n     WHERE s.name = 'system_health'\n       AND t.target_name = 'ring_buffer') AS Data\nCROSS APPLY TargetData.nodes('\/\/RingBufferTarget\/event') AS XEventData(XEvent)\nWHERE XEvent.value('@name', 'varchar(100)') = 'xml_deadlock_report';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83d\udee0 \ucd94\uac00 \ud301<\/h3>\n\n\n\n<ul>\n<li>XML \uacb0\uacfc\ub97c SSMS\uc5d0\uc11c \uc5f4\uace0&nbsp;<code>XQuery<\/code>\ub85c \ud30c\uc2f1\ud574\uc11c&nbsp;<code>inputbuf<\/code>\ub098&nbsp;<code>frame<\/code>&nbsp;\ub178\ub4dc\ub97c \uc815\ub82c\ud558\uac70\ub098 \ud544\ud130\ub9c1\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/li>\n\n\n\n<li>deadlock \uad00\ub828 \uc138\uc158\uc740 \uc131\ub2a5\uc5d0 \ud06c\uac8c \uc601\ud5a5\uc744 \uc8fc\uc9c0 \uc54a\uc73c\uba70, \uc6b4\uc601 \ud658\uacbd\uc5d0\uc11c\ub3c4 \uc548\uc804\ud558\uac8c \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>lock_deadlock&nbsp;\uc774\ubca4\ud2b8\uc5d0\uc11c&nbsp;sql_text\ub97c \ucc3e\uae30 \uc704\ud574\uc11c\ub294 SQL Server\uc5d0\uc11c \ud655\uc7a5 \uc774\ubca4\ud2b8(Extended Events)\ub97c \ud65c\uc6a9\ud558\ub294 \ubc29\ubc95\uc774 \uac00\uc7a5 \uc815\ud655\ud558\uace0 \uc2e4\uc6a9\uc801\uc785\ub2c8\ub2e4. \uc5ec\uae30\uc11c\ub294 SQL Server\uc5d0\uc11c Deadlock \ubc1c\uc0dd \uc2dc \uad00\ub828 SQL \ud14d\uc2a4\ud2b8(sql_text)\ub97c \ucd94\uc801\ud558\uace0 \uc870\ud68c\ud558\ub294 \ubc29\ubc95\uc744 \uc124\uba85\ud569\ub2c8\ub2e4. \ud83d\udd0d Deadlock \ubc1c\uc0dd \uc2dc SQL \ud14d\uc2a4\ud2b8 \ucc3e\ub294 \ubc29\ubc95 \ubc29\ubc95 1:&nbsp;Extended Events \uc138\uc158 \uc0ac\uc6a9 CREATE EVENT SESSION [Deadlock_Capture] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=5\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=508\">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\/508"}],"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=508"}],"version-history":[{"count":2,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/508\/revisions"}],"predecessor-version":[{"id":513,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/508\/revisions\/513"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=508"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=508"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=508"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}