{"id":311,"date":"2021-08-01T17:40:45","date_gmt":"2021-08-01T09:40:45","guid":{"rendered":"http:\/\/dba.qishuo.xin\/?p=311"},"modified":"2022-04-23T22:31:42","modified_gmt":"2022-04-23T14:31:42","slug":"oracle%e6%9f%a5%e8%af%a2%e5%bd%93%e5%89%8denq-tx-row-lock-contention%e4%ba%8c","status":"publish","type":"post","link":"http:\/\/dba.qishuo.xin\/?p=311","title":{"rendered":"Oracle\u67e5\u8be2\u5f53\u524denq: TX \u2013 row lock contention(\u4e8c)"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_76 ez-toc-wrap-right counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<label for=\"ez-toc-cssicon-toggle-item-6a143bc54fc73\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a143bc54fc73\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"http:\/\/dba.qishuo.xin\/?p=311\/#%E9%98%BB%E5%A1%9E%E4%B8%8E%E9%94%81\" >\u963b\u585e\u4e0e\u9501<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"http:\/\/dba.qishuo.xin\/?p=311\/#1_%E6%9F%A5%E8%AF%A2%E9%98%BB%E5%A1%9E\" >1. \u67e5\u8be2\u963b\u585e<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/dba.qishuo.xin\/?p=311\/#2_%E6%9F%A5%E8%AF%A2%E9%98%BB%E5%A1%9E%E5%90%8Esql%E8%AF%AD%E5%8F%A5\" >2. \u67e5\u8be2\u963b\u585e\u540esql\u8bed\u53e5<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"http:\/\/dba.qishuo.xin\/?p=311\/#3_%E6%9F%A5%E8%AF%A2%E6%9C%AA%E6%8F%90%E4%BA%A4%E7%9A%84%E4%BA%8B%E5%8A%A1\" >3. \u67e5\u8be2\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"http:\/\/dba.qishuo.xin\/?p=311\/#4_%E6%9F%A5%E8%AF%A2%E9%94%81\" >4. \u67e5\u8be2\u9501<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"http:\/\/dba.qishuo.xin\/?p=311\/#5_%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%BD%E8%B8%AA%E6%97%A5%E5%BF%97\" >5. \u6570\u636e\u5e93\u8ffd\u8e2a\u65e5\u5fd7<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"http:\/\/dba.qishuo.xin\/?p=311\/#%E6%80%BB%E7%BB%93\" >\u603b\u7ed3<\/a><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"%E9%98%BB%E5%A1%9E%E4%B8%8E%E9%94%81\"><\/span>\u963b\u585e\u4e0e\u9501<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"1_%E6%9F%A5%E8%AF%A2%E9%98%BB%E5%A1%9E\"><\/span>1. \u67e5\u8be2\u963b\u585e<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT &#039;\u8282\u70b9 &#039; || a_s.INST_ID || &#039; session &#039; || a_s.sid || &#039;,&#039; || a_s.SERIAL# ||\n       &#039; \u963b\u585e\u4e86 \u8282\u70b9 &#039; || b_s.INST_ID || &#039; session &#039; || b_s.SID || &#039;,&#039; ||\n       b_s.SERIAL# blockinfo,\n       a_s.INST_ID,\n       a_s.SID,\n       a_s.SCHEMANAME,\n       a_s.MODULE,\n       a_s.MACHINE,\n       a_s.PROGRAM,\n       a_s.STATUS,\n       &#039;\u540e\u4e3a\u88ab\u963b\u585e\u4fe1\u606f&#039;,\n       b_s.INST_ID blocked_inst_id,\n       b_s.SID blocked_sid,\n       b_s.SCHEMANAME blocked_SCHEMANAME,\n       b_s.EVENT blocked_event,\n       b_s.MODULE blocked_module,\n       b_s.STATUS blocked_status,\n       b_s.SQL_ID blocked_sql_id,\n       obj.owner blocked_owner,\n       obj.object_name blocked_object_name,\n       obj.OBJECT_TYPE blocked_OBJECT_TYPE,\n       case\n         when b_s.ROW_WAIT_OBJ# &lt;&gt; -1 then\n          dbms_rowid.rowid_create(1,\n                                  obj.DATA_OBJECT_ID,\n                                  b_s.ROW_WAIT_FILE#,\n                                  b_s.ROW_WAIT_BLOCK#,\n                                  b_s.ROW_WAIT_ROW#)\n         else\n          &#039;-1&#039;\n       end blocked_rowid, --\u88ab\u963b\u585e\u6570\u636e\u7684rowid\n       decode(obj.object_type,\n              &#039;TABLE&#039;,\n              &#039;select * from &#039; || obj.owner || &#039;.&#039; || obj.object_name ||\n              &#039; where rowid=&#039;&#039;&#039; ||\n              dbms_rowid.rowid_create(1,\n                                      obj.DATA_OBJECT_ID,\n                                      b_s.ROW_WAIT_FILE#,\n                                      b_s.ROW_WAIT_BLOCK#,\n                                      b_s.ROW_WAIT_ROW#) || &#039;&#039;&#039;&#039;,\n              NULL) blocked_data_querysql\n  from gv$session a_s, gv$session b_s, dba_objects obj\n where b_s.BLOCKING_INSTANCE is not null\n   and b_s.BLOCKING_SESSION is not null\n   and a_s.INST_ID = b_s.BLOCKING_INSTANCE\n   and a_s.SID = b_s.BLOCKING_SESSION\n   and b_s.ROW_WAIT_OBJ# = obj.object_id(+)\n order by a_s.inst_id, a_s.sid;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_dcd97c4e3c9ccee88383396db80dce1a.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_dcd97c4e3c9ccee88383396db80dce1a.jpg\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_458359550f4d7ba162a47ada07a522be.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_458359550f4d7ba162a47ada07a522be.jpg\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_f291b5b2f19a7471e77b85d84b32964e.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_f291b5b2f19a7471e77b85d84b32964e.jpg\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_c1deb4f04e01560e91fdb6d829bcaead.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_c1deb4f04e01560e91fdb6d829bcaead.jpg\" alt=\"\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"2_%E6%9F%A5%E8%AF%A2%E9%98%BB%E5%A1%9E%E5%90%8Esql%E8%AF%AD%E5%8F%A5\"><\/span>2. \u67e5\u8be2\u963b\u585e\u540esql\u8bed\u53e5<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT *\n  FROM v$sql\nWHERE sql_id = &#039;39c75p5w6h3tg&#039;;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_870de940176250cb862b2191bdaaba65.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_870de940176250cb862b2191bdaaba65.jpg\" alt=\"\" \/><\/a><\/p>\n<p>\u627e\u5230\u4e86\u8fd9\u4e2a\u4f1a\u8bdd\uff1a<\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_11557bef7b6c5d738b9acf49a722bf22.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_11557bef7b6c5d738b9acf49a722bf22.jpg\" alt=\"\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"3_%E6%9F%A5%E8%AF%A2%E6%9C%AA%E6%8F%90%E4%BA%A4%E7%9A%84%E4%BA%8B%E5%8A%A1\"><\/span>3. \u67e5\u8be2\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT s.sid,\n       s.serial#,\n       s.username,\n       s.osuser,\n       s.program,\n       s.event,\n       to_char(s.logon_time, &#039;yyyy-MM-dd hh24:mi:ss&#039;),\n       to_char(t.start_date, &#039;yyyy-MM-dd hh24:mi:ss&#039;),\n       s.last_call_et,\n       s.blocking_session,\n       s.status,\n       (SELECT q.sql_text\n          FROM v$sql q\n         WHERE q.last_active_time = t.start_date\n           AND ROWNUM &lt;= 1) AS sql_text\n  FROM v$session s,\n       v$transaction t\n WHERE s.saddr = t.ses_addr;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_ffa365c234ea676c22c48ec2e3709587.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_ffa365c234ea676c22c48ec2e3709587.jpg\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_e1de4d3db5ef160320fa207169ee39b3.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_e1de4d3db5ef160320fa207169ee39b3.jpg\" alt=\"\" \/><\/a><\/p>\n<p><code>\u672a\u63d0\u4ea4\u4e8b\u52a1\u7684\u8fdb\u7a0bSID\u53f7\u6b63\u597d\u662f 126<\/code>\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"4_%E6%9F%A5%E8%AF%A2%E9%94%81\"><\/span>4. \u67e5\u8be2\u9501<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">SELECT A.OWNER \u65b9\u6848\u540d,\n       A.OBJECT_NAME \u8868\u540d,\n       B.XIDUSN \u56de\u6eda\u6bb5\u53f7,\n       B.XIDSLOT \u69fd\u53f7,\n       B.XIDSQN \u5e8f\u5217\u53f7,\n       B.SESSION_ID \u9501\u8868SESSION_ID,\n       B.ORACLE_USERNAME \u9501\u8868\u7528\u6237\u540d,\n       D.TYPE,\n       decode(D.type,\n              &#039;XR&#039;,\n              &#039;NULL&#039;,\n              &#039;RS&#039;,\n              &#039;SS(Row-S)&#039;,\n              &#039;CF&#039;,\n              &#039;SS(Row-S)&#039;,\n              &#039;TM&#039;,\n              &#039;TABLE LOCK&#039;,\n              &#039;PW&#039;,\n              &#039;TABLE LOCK&#039;,\n              &#039;TO&#039;,\n              &#039;TABLE LOCK&#039;,\n              &#039;TS&#039;,\n              &#039;TABLE LOCK&#039;,\n              &#039;RT&#039;,\n              &#039;ROW LOCK&#039;,\n              &#039;TX&#039;,\n              &#039;ROW LOCK&#039;,\n              &#039;MR&#039;,\n              &#039;S(Share)&#039;,\n              NULL) \u9501\u5b9a\u65b9\u5f0f,\n       C.MACHINE \u7528\u6237\u7ec4,\n       C.TERMINAL \u673a\u5668\u540d,\n       B.OS_USER_NAME \u7cfb\u7edf\u7528\u6237\u540d,\n       B.PROCESS \u7cfb\u7edf\u8fdb\u7a0bid,\n       DECODE(C.STATUS, &#039;INACTIVE&#039;, &#039;\u4e0d\u6d3b\u52a8&#039;, &#039;ACTIVE&#039;, &#039;\u6d3b\u52a8&#039;) \u6d3b\u52a8\u60c5\u51b5,\n       C.SERVER,\n       C.SID,\n       C.SERIAL#,\n       C.PROGRAM \u8fde\u63a5\u65b9\u5f0f,\n       C.LOGON_TIME\n  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d\n WHERE (A.OBJECT_ID = B.OBJECT_ID)\n   AND (B.PROCESS = C.PROCESS)\n   and C.sid = d.sid\n   and B.LOCKED_MODE = D.LMODE\n ORDER BY 1, 2;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_bf0c779293a6563d4c8b47d319e68c7d.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_bf0c779293a6563d4c8b47d319e68c7d.jpg\" alt=\"\" \/><\/a><\/p>\n<p>[<a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_ac150e0de3742ce7ca168efd85d1e97f.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_ac150e0de3742ce7ca168efd85d1e97f.jpg\" alt=\"\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"5_%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%BD%E8%B8%AA%E6%97%A5%E5%BF%97\"><\/span>5. \u6570\u636e\u5e93\u8ffd\u8e2a\u65e5\u5fd7<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>\u5728\u6570\u636e\u5e93\u5168\u5c40\u505a\u8ffd\u8e2a<\/p>\n<pre><code class=\"language-sql\">-- \u5f00\u542f\u6570\u636e\u5e93\u7ea7\u8ffd\u8e2a:\n alter system set events  &#039;10046 trace name context forever, level 12&#039;;\n\n-- \u67e5\u8be2\u65e5\u5fd7\u4f4d\u7f6e\nSELECT s.sid,s.serial#, p.tracefile, s.sql_id\n  FROM v$session s\n  LEFT JOIN v$process p ON s.paddr = p.addr\n WHERE s.username = &#039;EASYTONG&#039;;\n\n-- \u5173\u95ed\u6570\u636e\u5e93\u7ea7\u8ddf\u8e2a\uff1a\nalter system set events &#039;10046 trace name context off&#039;;<\/code><\/pre>\n<p>\u88ab\u963b\u585e\u7684\u4f1a\u8bdd\u65e5\u5fd7\u3002<code>\u963b\u585e\u7684\u4f1a\u8bdd\u662finactive\uff0c\u6ca1\u6709\u4ea7\u751f\u65e5\u5fd7<\/code><\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_bc78cb53f4e0cb14180867f74948bad1.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_bc78cb53f4e0cb14180867f74948bad1.jpg\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_5ecf477acab06fe8445a5a7927aeb658.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/08\/wp_editor_md_5ecf477acab06fe8445a5a7927aeb658.jpg\" alt=\"\" \/><\/a><\/p>\n<h3><span class=\"ez-toc-section\" id=\"%E6%80%BB%E7%BB%93\"><\/span>\u603b\u7ed3<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>A\u4f1a\u8bdd\u5bf9am_account\u8868\u4ea7\u751f\u4e86\u4e00\u4e2a\u9501\uff0c\u4ea4\u6613\u7684\u65f6\u5019\uff0cB\u4f1a\u8bdd\u5148\u9501\u4e86\u6700\u5927\u503c\u8868\uff0c\u7136\u540e\u7b49\u5f85A\u4f1a\u8bdd\u9501\u7684\u91ca\u653e\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u963b\u585e\u4e0e\u9501 1. \u67e5\u8be2\u963b\u585e SELECT &#038;#039&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,21],"tags":[],"class_list":["post-311","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/311","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=311"}],"version-history":[{"count":17,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/311\/revisions"}],"predecessor-version":[{"id":1561,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/311\/revisions\/1561"}],"wp:attachment":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=311"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}