{"id":531,"date":"2025-07-28T09:32:43","date_gmt":"2025-07-28T00:32:43","guid":{"rendered":"https:\/\/sejiwon.com\/?p=531"},"modified":"2025-07-28T09:32:43","modified_gmt":"2025-07-28T00:32:43","slug":"%ec%9d%98%ec%8b%ac%ec%bf%bc%eb%a6%ac-%ec%b0%be%eb%8a%94-%eb%b2%95","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=531","title":{"rendered":"\uc758\uc2ec\ucffc\ub9ac \ucc3e\ub294 \ubc95"},"content":{"rendered":"\n

MSSQL\uc5d0\uc11c Deadlock\uc774\ub098 \uc131\ub2a5 \ubb38\uc81c\ub97c \uc720\ubc1c\ud558\ub294 “\uc758\uc2ec \ucffc\ub9ac”<\/strong> \ub97c \ucc3e\uc73c\ub824\uba74, \ub2e4\uc74c\uacfc \uac19\uc740 \uc9c4\ub2e8 \ud3ec\uc778\ud2b8<\/strong>\ub97c \uc911\uc2ec\uc73c\ub85c \ucffc\ub9ac\ub97c \uc218\uc9d1\ud558\uace0 \ubd84\uc11d\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n

\n\n\n\n

\ud83d\udd0d 1. \ud604\uc7ac \uc2e4\ud589 \uc911\uc774\uac70\ub098 \ube14\ub85c\ud0b9 \uc911\uc778 \ucffc\ub9ac \ucc3e\uae30<\/strong><\/h2>\n\n\n\n
-- \ud604\uc7ac \uc2e4\ud589 \uc911\uc774\uba70 \ub2e4\ub978 \uc138\uc158\uc744 \ube14\ub85c\ud0b9 \uc911\uc778 \ucffc\ub9ac \ucc3e\uae30\nSELECT\n    r.session_id,\n    r.blocking_session_id,\n    r.status,\n    r.cpu_time,\n    r.total_elapsed_time,\n    r.wait_type,\n    r.wait_time,\n    r.wait_resource,\n    SUBSTRING(t.text, (r.statement_start_offset \/ 2) + 1,\n              ((CASE r.statement_end_offset\n                    WHEN -1 THEN DATALENGTH(t.text)\n                    ELSE r.statement_end_offset\n                END - r.statement_start_offset) \/ 2) + 1) AS running_statement,\n    t.text AS full_query,\n    s.host_name,\n    s.program_name,\n    s.login_name,\n    s.reads,\n    s.writes\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) t\nWHERE r.blocking_session_id <> 0 OR r.status = 'running'\nORDER BY r.total_elapsed_time DESC;\n<\/code><\/pre>\n\n\n\n
\n
\u2705 \uc774 \ucffc\ub9ac\ub294 \ud604\uc7ac \uc2e4\ud589 \uc911\uc778 \ucffc\ub9ac\ub4e4 \uc911\uc5d0\uc11c \ube14\ub85c\ud0b9\ud558\uac70\ub098 \uc624\ub798 \uc2e4\ud589 \uc911\uc778 \uac83<\/strong>\uc744 \ubcf4\uc5ec\uc90d\ub2c8\ub2e4. total_elapsed_time<\/code>, cpu_time<\/code> \uae30\uc900\uc73c\ub85c \uc815\ub82c\ud574\uc11c “\ub290\ub9b0 \ucffc\ub9ac” \ud30c\uc545\uc5d0 \uc801\ud569\ud569\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\ud83d\udd25 2. \ucd5c\uadfc \uc2e4\ud589\ub41c \ube44\ud6a8\uc728 \ucffc\ub9ac \uc870\ud68c (\ucffc\ub9ac \uce90\uc2dc \uae30\ubc18)<\/strong><\/h2>\n\n\n\n
-- \uc2e4\ud589 \ud69f\uc218 \ub300\ube44 \ud3c9\uade0 CPU \uc0ac\uc6a9\ub7c9\uc774 \ub192\uc740 \ucffc\ub9ac TOP 20\nSELECT TOP 20\n    qs.execution_count,\n    qs.total_worker_time \/ qs.execution_count AS avg_cpu_time,\n    qs.total_elapsed_time \/ qs.execution_count AS avg_elapsed_time,\n    SUBSTRING(st.text, (qs.statement_start_offset \/ 2) + 1,\n              ((CASE qs.statement_end_offset\n                    WHEN -1 THEN DATALENGTH(st.text)\n                    ELSE qs.statement_end_offset\n                END - qs.statement_start_offset) \/ 2) + 1) AS query_text,\n    st.text AS full_text,\n    db_name(st.dbid) AS db_name\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st\nORDER BY avg_cpu_time DESC;\n<\/code><\/pre>\n\n\n\n
\n
\u2705 \uc774 \ucffc\ub9ac\ub294 \ucd5c\uadfc \uc2e4\ud589\ub41c \ucffc\ub9ac \uc911 CPU\ub97c \ub9ce\uc774 \uc4f4 \uc758\uc2ec \ucffc\ub9ac<\/strong>\ub97c \ucc3e\uc544\ub0c5\ub2c8\ub2e4. \uc2e4\uc81c deadlock\uc740 \uc544\ub2c8\ub354\ub77c\ub3c4 \uacfc\ubd80\ud558 \uc720\ubc1c\uc790<\/strong>\ub97c \ucd94\uc801\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\ud83e\udde0 3. Deadlock \uc720\ubc1c \uac00\ub2a5\uc131 \ub192\uc740 \ucffc\ub9ac \ud2b9\uc9d5<\/strong><\/h2>\n\n\n\n
\ud2b9\uc9d5<\/th>\uc124\uba85<\/th><\/tr><\/thead>
\ud83d\udd01 \ud2b8\ub79c\uc7ad\uc158 \uc548\uc5d0\uc11c \uc5ec\ub7ec \ud14c\uc774\ube14\uc744 \ub3d9\uc2dc\uc5d0 \uc870\uc791<\/td>\uc21c\uc11c\uac00 \uaf2c\uc774\uba74 \uad50\ucc29 \uc0c1\ud0dc \ubc1c\uc0dd<\/td><\/tr>
\ud83d\udd12 WITH (TABLOCK)<\/code>, SERIALIZABLE<\/code> \uc0ac\uc6a9<\/td>\uc7a0\uae08 \ud655\uc7a5 \ub610\ub294 \ubc94\uc704 \uc7a0\uae08<\/td><\/tr>
\ud83d\udd0d \uc778\ub371\uc2a4 \ubbf8\uc0ac\uc6a9 or \uc798\ubabb\ub41c \uc870\uac74<\/td>\uc2a4\uce94\uc73c\ub85c \uc778\ud574 \uc7a0\uae08 \ubc94\uc704 \ub113\uc5b4\uc9d0<\/td><\/tr>
\u2757 \ucffc\ub9ac \uc2e4\ud589 \uc2dc\uac04 or I\/O \ube44\uc815\uc0c1\uc801\uc73c\ub85c \ub192\uc74c<\/td>\ub2e4\ub978 \ucffc\ub9ac\ub97c \ub9c9\uace0 \uc788\ub294 \uac00\ub2a5\uc131<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
\ud83d\udee0\ufe0f 4. \uc7a0\uae08 \ubcf4\uc720 \ud604\ud669\uc73c\ub85c \uc758\uc2ec \uc138\uc158 \ucc3e\uae30<\/strong><\/h2>\n\n\n\n
-- \ud604\uc7ac \uc5b4\ub5a4 \ud14c\uc774\ube14\uc5d0 \ub77d\uc744 \uac78\uace0 \uc788\ub294\uc9c0 \ubcf4\uae30\nSELECT\n    r.session_id,\n    r.blocking_session_id,\n    r.wait_type,\n    r.wait_resource,\n    t.text AS sql_text,\n    o.name AS locked_object,\n    p.object_id,\n    l.resource_type,\n    l.request_mode\nFROM sys.dm_tran_locks l\nJOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id\nJOIN sys.dm_exec_sessions s ON r.session_id = s.session_id\nLEFT JOIN sys.objects o ON l.resource_associated_entity_id = o.object_id\nCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t\nJOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id\nWHERE s.is_user_process = 1;\n<\/code><\/pre>\n\n\n\n
\n
\u2705 \ub77d\uc744 \uc7a1\uace0 \uc788\ub294 \uc138\uc158\uc774 \uc5b4\ub5a4 \ud14c\uc774\ube14\uc744 \ub300\uc0c1\uc73c\ub85c \uc5b4\ub5a4 \ubaa8\ub4dc\uc758 \ub77d\uc744 \uac78\uace0 \uc788\ub294\uc9c0 \ubd84\uc11d\ud574 \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\ud83d\udccc \uc694\uc57d: \uc758\uc2ec \ucffc\ub9ac\ub97c \ucc3e\ub294 \ubc29\ubc95<\/h2>\n\n\n\n
\ubaa9\uc801<\/th>\ucffc\ub9ac<\/th>\uc124\uba85<\/th><\/tr><\/thead>
\ud83d\udd0e \ud604\uc7ac \ube14\ub85c\ud0b9 \uc0c1\ud0dc \ubcf4\uae30<\/td>sys.dm_exec_requests<\/code><\/td>\ud604\uc7ac \ubb38\uc81c \uc720\ubc1c\uc790 \ud655\uc778<\/td><\/tr>
\ud83d\udcca \uacfc\uac70 \ube44\ud6a8\uc728 \ucffc\ub9ac \ubcf4\uae30<\/td>sys.dm_exec_query_stats<\/code><\/td>\uce90\uc2dc\ub41c \ucffc\ub9ac \uc911 \uace0\ube44\uc6a9 \ucffc\ub9ac \ud0d0\uc9c0<\/td><\/tr>
\ud83d\udd10 \ub77d \uc815\ubcf4 \ubcf4\uae30<\/td>sys.dm_tran_locks<\/code><\/td>\uc5b4\ub5a4 \uac1d\uccb4\uac00 \uc7a0\uaca8 \uc788\ub294\uc9c0 \ubd84\uc11d<\/td><\/tr>
\ud83d\udcc8 \ud788\uc2a4\ud1a0\ub9ac \ucd94\uc801<\/td>Extended Events<\/td>Deadlock \ubc1c\uc0dd \ucffc\ub9ac \ucd94\uc801 \uac00\ub2a5<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"
MSSQL\uc5d0\uc11c Deadlock\uc774\ub098 \uc131\ub2a5 \ubb38\uc81c\ub97c \uc720\ubc1c\ud558\ub294 “\uc758\uc2ec \ucffc\ub9ac” \ub97c \ucc3e\uc73c\ub824\uba74, \ub2e4\uc74c\uacfc \uac19\uc740 \uc9c4\ub2e8 \ud3ec\uc778\ud2b8\ub97c \uc911\uc2ec\uc73c\ub85c \ucffc\ub9ac\ub97c \uc218\uc9d1\ud558\uace0 \ubd84\uc11d\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ud83d\udd0d 1. \ud604\uc7ac \uc2e4\ud589 \uc911\uc774\uac70\ub098 \ube14\ub85c\ud0b9 \uc911\uc778 \ucffc\ub9ac \ucc3e\uae30 — \ud604\uc7ac \uc2e4\ud589 \uc911\uc774\uba70 \ub2e4\ub978 \uc138\uc158\uc744 \ube14\ub85c\ud0b9 \uc911\uc778 \ucffc\ub9ac \ucc3e\uae30 SELECT r.session_id, r.blocking_session_id, r.status, r.cpu_time, r.total_elapsed_time, r.wait_type, r.wait_time, r.wait_resource, SUBSTRING(t.text, (r.statement_start_offset \/ 2) + 1, ((CASE r.statement_end_offset\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\/531"}],"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=531"}],"version-history":[{"count":1,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/531\/revisions"}],"predecessor-version":[{"id":533,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/531\/revisions\/533"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=531"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=531"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=531"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}