{"id":528,"date":"2025-07-28T09:29:36","date_gmt":"2025-07-28T00:29:36","guid":{"rendered":"https:\/\/sejiwon.com\/?p=528"},"modified":"2025-07-28T09:29:36","modified_gmt":"2025-07-28T00:29:36","slug":"dead-lock%ec%9d%84-%eb%aa%a8%eb%8b%88%ed%84%b0%eb%a7%81%ed%95%a0-%ec%88%98-%ec%9e%88%eb%8a%94-%ec%bf%bc%eb%a6%ac","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=528","title":{"rendered":"DEAD LOCK\uc744 \ubaa8\ub2c8\ud130\ub9c1\ud560 \uc218 \uc788\ub294 \ucffc\ub9ac"},"content":{"rendered":"\n

MSSQL\uc5d0\uc11c Deadlock(\uad50\ucc29 \uc0c1\ud0dc)<\/strong> \uc744 \ubaa8\ub2c8\ud130\ub9c1\ud558\uac70\ub098 \ud0d0\uc9c0<\/strong>\ud558\ub824\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \ubc29\ubc95\ub4e4\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4:<\/p>\n\n\n\n

\n\n\n\n

\u2705 1. Deadlock \ubaa8\ub2c8\ud130\ub9c1\uc6a9 \uae30\ubcf8 DMV \ucffc\ub9ac<\/strong><\/h2>\n\n\n\n
-- \ud604\uc7ac \ube14\ub85c\ud0b9 \ub610\ub294 \uad50\ucc29 \uc0c1\ud0dc \uc758\uc2ec \uc138\uc158 \uc870\ud68c\nSELECT\n    r.session_id,\n    r.status,\n    r.command,\n    r.blocking_session_id,\n    r.wait_type,\n    r.wait_time,\n    r.wait_resource,\n    t.text AS query_text,\n    s.host_name,\n    s.program_name,\n    s.login_name\nFROM sys.dm_exec_requests r\nJOIN sys.dm_exec_sessions s ON r.session_id = s.session_id\nCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t\nWHERE r.blocking_session_id <> 0;\n<\/code><\/pre>\n\n\n\n
\ud83d\udfe1 \uc774 \ucffc\ub9ac\ub294 \ud604\uc7ac \ube14\ub85c\ud0b9 \uc0c1\ud0dc<\/strong>\ub97c \ubcf4\uc5ec\uc8fc\uba70, blocking_session_id<\/code>\ub97c \ud1b5\ud574 \uc7a0\uc7ac\uc801 deadlock<\/strong>\uc744 \ucd94\uc801\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
\n\n\n\n
\u2705 2. \ucd5c\uadfc Deadlock \uc774\ubca4\ud2b8 \uc870\ud68c (Extended Events \uc0ac\uc6a9)<\/strong><\/h2>\n\n\n\n
SQL Server 2012 \uc774\uc0c1\uc5d0\uc11c\ub294 Extended Events<\/strong>\uc5d0\uc11c deadlock \uc774\ubca4\ud2b8\ub97c \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
-- \ucd5c\uadfc Deadlock \uc774\ubca4\ud2b8 \ubcf4\uae30 (system_health \uc138\uc158 \ud65c\uc6a9)\nSELECT \n    XEvent.value('(event\/@timestamp)[1]', 'datetime') AS [Deadlock Time],\n    XEvent.value('(event\/data\/value)[1]', 'varchar(max)') AS [Deadlock Graph XML]\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 = 'system_health'\n      AND t.target_name = 'ring_buffer'\n) AS Data\nCROSS APPLY TargetData.nodes('\/\/RingBufferTarget\/event[@name=\"xml_deadlock_report\"]') AS XEventData(XEvent)\nORDER BY [Deadlock Time] DESC;\n<\/code><\/pre>\n\n\n\n
\ud83d\udee0\ufe0f \ucc38\uace0: \uc704 \ucffc\ub9ac\ub294 \ucd5c\uadfc \ubc1c\uc0dd\ud55c Deadlock\uc758 XML \ud615\uc2dd \uadf8\ub798\ud504<\/strong>\ub97c \ubc18\ud658\ud569\ub2c8\ub2e4. SSMS\uc5d0\uc11c \ud074\ub9ad\ud558\uba74 Deadlock Viewer\ub85c \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
\n\n\n\n
\u2705 3. Deadlock \uc54c\ub9bc \uc790\ub3d9\ud654 (\uc774\ubca4\ud2b8 \uc138\uc158 \uc0dd\uc131)<\/strong><\/h2>\n\n\n\n
\ub354 \uba85\ud655\ud55c \ubaa8\ub2c8\ud130\ub9c1\uc744 \uc704\ud574 \ubcc4\ub3c4\uc758 Extended Events \uc138\uc158\uc744 \uc0dd\uc131\ud560 \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
-- Deadlock \uc804\uc6a9 \uc774\ubca4\ud2b8 \uc138\uc158 \uc0dd\uc131\nCREATE EVENT SESSION [DeadlockMonitor] ON SERVER\nADD EVENT sqlserver.xml_deadlock_report\nADD TARGET package0.ring_buffer\nWITH (STARTUP_STATE = ON);\nGO\n\n-- \uc138\uc158 \uc2dc\uc791\nALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START;\n<\/code><\/pre>\n\n\n\n
Deadlock \ubc1c\uc0dd \uc2dc \ub85c\uadf8\ub97c \ub2e4\uc74c\uacfc \uac19\uc774 \uc870\ud68c\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4:<\/p>\n\n\n\n
-- DeadlockMonitor \uc138\uc158\uc5d0\uc11c Deadlock \uadf8\ub798\ud504 \uc870\ud68c\nSELECT \n    XEvent.value('(event\/@timestamp)[1]', 'datetime') AS [DeadlockTime],\n    XEvent.value('(event\/data\/value)[1]', 'varchar(max)') AS [DeadlockGraphXML]\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 = 'DeadlockMonitor'\n      AND t.target_name = 'ring_buffer'\n) AS Data\nCROSS APPLY TargetData.nodes('\/\/RingBufferTarget\/event[@name=\"xml_deadlock_report\"]') AS XEventData(XEvent)\nORDER BY [DeadlockTime] DESC;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u2705 4. Deadlock \ubc1c\uc0dd \uc5ec\ubd80 \ube60\ub974\uac8c \ud655\uc778 (\uc5d0\ub7ec \ub85c\uadf8)<\/strong><\/h2>\n\n\n\n
EXEC xp_readerrorlog 0, 1, 'deadlock', NULL, NULL, NULL, 'DESC';\n<\/code><\/pre>\n\n\n\n
\n
\ud83d\udccc SQL Server \uc5d0\ub7ec \ub85c\uadf8\uc5d0 \ub0a8\uc740 Deadlock \uba54\uc2dc\uc9c0\ub97c \uc870\ud68c\ud569\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\ud83d\udccc \uc815\ub9ac<\/h2>\n\n\n\n
\ubaa9\uc801<\/th>\uc0ac\uc6a9 \ucffc\ub9ac\/\ubc29\ubc95<\/th><\/tr><\/thead>
\ud604\uc7ac \ube14\ub85c\ud0b9 \uc0c1\ud0dc \ud655\uc778<\/td>sys.dm_exec_requests<\/code> + blocking_session_id<\/code><\/td><\/tr>
\uacfc\uac70 Deadlock \ud655\uc778<\/td>Extended Events (system_health<\/code>)<\/td><\/tr>
\uc2e4\uc2dc\uac04 Deadlock \ubaa8\ub2c8\ud130\ub9c1<\/td>\uc0ac\uc6a9\uc790 \uc815\uc758 Extended Event \uc138\uc158<\/td><\/tr>
\ub85c\uadf8\ub85c Deadlock \ucd94\uc801<\/td>xp_readerrorlog<\/code> \uc0ac\uc6a9<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"
MSSQL\uc5d0\uc11c Deadlock(\uad50\ucc29 \uc0c1\ud0dc) \uc744 \ubaa8\ub2c8\ud130\ub9c1\ud558\uac70\ub098 \ud0d0\uc9c0\ud558\ub824\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \ubc29\ubc95\ub4e4\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4: \u2705 1. Deadlock \ubaa8\ub2c8\ud130\ub9c1\uc6a9 \uae30\ubcf8 DMV \ucffc\ub9ac — \ud604\uc7ac \ube14\ub85c\ud0b9 \ub610\ub294 \uad50\ucc29 \uc0c1\ud0dc \uc758\uc2ec \uc138\uc158 \uc870\ud68c SELECT r.session_id, r.status, r.command, r.blocking_session_id, r.wait_type, r.wait_time, r.wait_resource, t.text AS query_text, s.host_name, s.program_name, s.login_name FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)\u2026 Read More »<\/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\/528"}],"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=528"}],"version-history":[{"count":1,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/528\/revisions"}],"predecessor-version":[{"id":530,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/528\/revisions\/530"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=528"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=528"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=528"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}