{"id":3281,"date":"2020-04-06T15:42:08","date_gmt":"2020-04-06T15:42:08","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=3281"},"modified":"2021-02-11T16:59:58","modified_gmt":"2021-02-11T16:59:58","slug":"deadlock-row-cache-lock-library-cache-lock","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/","title":{"rendered":"Deadlock Caused by Row Cache and Library Cache Locks"},"content":{"rendered":"<h1>Deadlock<\/h1>\n<p>I&#8217;ve been occasionally seeing deadlocks like the following one:<\/p>\n<pre><code>Resolving deadlock by signaling ORA-00060 to 'instance: 1, os id: 27612, session id: 277'\n  dump location: \/u00\/oracle\/orabase\/diag\/rdbms\/db1_site1\/DB1\/trace\/DB1_m007_27612.trc<\/code><\/pre>\n<pre><code>Oracle session identified by:\n    {\n                instance: 1 (db1_site1.db1)\n                   os id: 27612\n              process id: 55, oracle@server1 (M007)\n              session id: 277\n        session serial #: 13710\n             module name: 0 (<span style=\"color:blue\">MMON_SLAVE<\/span>Cliente Manager.Metric Engine)\n    }\n    is waiting for <span style=\"color:blue\">'row cache lock'<\/span> with wait info:\n    {\n                      <span style=\"color:blue\">p1: 'cache id'=0x10<\/span>\n                      p2: 'mode'=0x0\n                      p3: 'request'=0x5\n            time in wait: 2.004491 sec\n           timeout after: never\n                 wait id: 5324\n                blocking: 1 session\n          current sql_id: 1675298304\n             current sql: <span style=\"color:blue\">alter table WRH$_ACTIVE_SESSION_HISTORY split partition<\/span> WRH$_ACTIVE_SESSION_HISTORY_1246382628_3941 at (1246382628,3965) into (partition WRH$_ACTIVE_SESSION_HISTORY_1246382628_3941, partition WRH$_ACTIVE_SESSION_HISTORY_1246382628_3965 tablespace SYSAUX) update indexes\n            wait history:\n              * time between current wait and wait #1: 0.001227 sec\n              1.       event: <span style=\"color:blue\">'library cache lock'<\/span>\n                 time waited: 0.128808 sec\n                     wait id: 5323             p1: 'handle address'=0x87d123c8\n                                               p2: 'lock address'=0x818f8638\n                                               p3: '100*mode+namespace'=0x2a7900010003\n              * time between wait #1 and #2: 0.000176 sec\n              2.       event: 'direct path write'\n                 time waited: 0.000472 sec\n                     wait id: 5322             p1: 'file number'=0x2\n                                               p2: 'first dba'=0x62e1\n                                               p3: 'block cnt'=0x1\n              * time between wait #2 and #3: 0.000057 sec\n              3.       event: 'direct path write'\n                 time waited: 0.000729 sec\n                     wait id: 5321             p1: 'file number'=0x2\n                                               p2: 'first dba'=0x62b5\n                                               p3: 'block cnt'=0x3\n    }\n    and is blocked by\n\n =&gt; Oracle session identified by:\n    {\n                instance: 1 (db1_site1.db1)\n                   os id: 7428\n              process id: 62, oracle@server1 (J000)\n              session id: 311\n        session serial #: 52586\n             module name: 0 (<span style=\"color:blue\">DBMS_SCHEDULER<\/span>ntNS V1-V3)seetric Engine)\n    }\n    which is waiting for <span style=\"color:blue\">'library cache lock'<\/span> with wait info:\n    {\n                      p1: 'handle address'=0x87d123c8\n                      p2: 'lock address'=0x863522d0\n                      p3: '100*mode+namespace'=0x2a7900010003\n            time in wait: 2.005735 sec\n           timeout after: 14 min 57 sec\n                 wait id: 652\n                blocking: 1 session\n          current sql_id: 3010140026\n             current sql: <span style=\"color:blue\">call dbms_stats.gather_database_stats_job_proc (  )<\/span>\n            wait history:\n              * time between current wait and wait #1: 0.170488 sec\n              1.       event: 'buffer busy waits'\n                 time waited: 0.000193 sec\n                     wait id: 651              p1: 'file#'=0x1\n                                               p2: 'block#'=0x52b5\n                                               p3: 'class#'=0x1\n              * time between wait #1 and #2: 0.033917 sec\n              2.       event: 'latch: shared pool'\n                 time waited: 0.000057 sec\n                     wait id: 650              p1: 'address'=0x602e2348\n                                               p2: 'number'=0x250\n                                               p3: 'why'=0x0\n              * time between wait #2 and #3: 0.031947 sec\n              3.       event: 'buffer busy waits'\n                 time waited: 0.000059 sec\n                     wait id: 649              p1: 'file#'=0x3\n                                               p2: 'block#'=0x4a7\n                                               p3: 'class#'=0x22\n    }\n    and is blocked by the session at the start of the chain.<\/code><\/pre>\n<p>Here&#8217;s is a short intepretation of the diagnostic information above:<\/p>\n<ul>\n<li>MMON_SLAVE, which was executing <i>alter table split partition<\/i> on a workload repository table was waiting on a row cache lock after acquiring a library cache lock.<\/li>\n<li>The row cache lock was held by a job queue process, which was gathering statistics.<\/li>\n<li>This job queue process was waiting on the library cache lock which was held by MMON_SLAVE.<\/li>\n<li>MMON_SLAVE was a deadlock victim.<\/li>\n<\/ul>\n<h1>Column statistics<\/h1>\n<p>Further, <i>p1<\/i> of the row cache lock wait holds the information about the cache id in the row cache. We can use it for querying <i>v$rowcache<\/i>.<\/p>\n<pre><code>\u2026\nis waiting for 'row cache lock' with wait info:\n{\n\t\t\t\t  p1: <span style=\"color:blue\">'cache id'=0x10<\/span>\n...<\/code><\/pre>\n<pre><code>select distinct parameter from v$rowcache where <span style=\"color:blue\">cache#=16<\/span> ;\n\nPARAMETER\n--------------------------------\n<span style=\"color:blue\">dc_histogram_data\ndc_histogram_defs<\/span><\/code><\/pre>\n<p>As we can see, the row cache lock was related to the column statistics.<\/p>\n<h1>Known bugs<\/h1>\n<p>The fingerprint matches the following known bugs:<\/p>\n<ul>\n<li>26440681: ORACLE 12.2 DEADLOCK BETWEEN GATHER STATS JOB AND MMON SLAVE<\/li>\n<li>8605337 : DEADLOCK BETWEEN GATHER STATS JOB AND MMON SLAVE<\/li>\n<\/ul>\n<p>I&#8217;ve been seeing this issue after upgrading to 18c, but I&#8217;ve managed to reproduce it on 12c and 19c as well. In fact, the purpose of this blog post is to provide a reproducable test case which might be useful for fixing the bug and explaining the circumstances leading to the problem. Interestingly, the Oracle Support documents state that the bug isn&#8217;t reproducable.<\/p>\n<h1>Call stacks<\/h1>\n<p>Luckily, the call stack traces were spooled into the trace files for both processes.<\/p>\n<p>Below is the stack for <i>split partition<\/i> (I cut out some irrelevant parts):<\/p>\n<pre><code>\n----- Current SQL Statement for this session (sql_id=67qs60289jx3k) -----\nalter table WRH$_ACTIVE_SESSION_HISTORY split partition WRH$_ACTIVE_SESSION_HISTORY_1246382628_3941 at (1246382628,3965) into (partition WRH$_ACTIVE_SESSION_HISTORY_1246382628_3941, partition WRH$_ACTIVE_SESSION_HISTORY_1246382628_3965 tablespace SYSAUX) update indexes\n\n*** 2020-03-21T23:07:24.536095+01:00\n\n----- Call Stack Trace -----\ncalling              call     entry                argument values in hex\nlocation             type     point                (? means dubious value)\n-------------------- -------- -------------------- ----------------------------\nksedst1()+410        call     skdstdst()           7FFFBFFAB890 ?\n                                                   2DD66E46FF12FB ?\n                                                   7FFFBFFAB880 ? 7FFFBFF85778 ?\n                                                   7FFFBFF88D10 ? 7FFFBFF84F58 ?\nksdhng_dumpcb_hung_  call     ksedst1()            000000000 ? 000000001 ?\nproc_int()+843                                     7FFFBFFAB880 ? 7FFFBFF85778 ?\n                                                   7FFFBFF88D10 ? 7FFFBFF84F58 ?\nksdhng_action_execu  call     ksdhng_dumpcb_hung_  000000001 ? 0B6B3B310 ?\nte()+1165                     proc_int()           7FFFBFFAB880 ? 7FFFBFF85778 ?\n                                                   7FFFBFF88D10 ? 7FFFBFF84F58 ?\nksuintract_exec_pen  call     ksdhng_action_execu  008D62128 ? 0B6B3B310 ?\nding()+352                    te()                 7FFFBFFAB880 ? 7FFFBFF85778 ?\n                                                   7FFFBFF88D10 ? 7FFFBFF84F58 ?\nksuitr()+6419        call     ksuintract_exec_pen  008D62128 ? 0B6B3B310 ?\n                              ding()               7FFFBFFAB880 ? 0B6B65C58 ?\n                                                   7FFFBFF88D10 ? 7FFFBFF84F58 ?\nksu_dispatch_tac()+  call     ksuitr()             000000039 ? 0B6B3B310 ?\n2384                                               7FFFBFFAB880 ? 0B6B65C58 ?\n                                                   7FFFBFF88D10 ? 7FFFBFF84F58 ?\n<span style=\"color:blue\">kqrget()<\/span>+4920        call     ksu_dispatch_tac()   000000039 ? 0B6B3B310 ?\n                                                   7FFFBFFAB880 ? 0B6B65C58 ?\n                                                   7FFFBFF88D10 ? 7FFFBFF84F58 ?\nkqrLockPo()+272      call     kqrget()             0868B7810 ? 000000005 ?\n                                                   0001EC682 ? 0832EC860 ?\n                                                   7FFFBFF88D10 ? 7FFFBFF84F58 ?\nkqrpre2()+1950       call     kqrLockPo()          000000010 ? 0832EC780 ?\n                                                   09670BF20 ? 000000005 ?\n                                                   000000000 ? 094510000 ?\nkqrpre()+44          call     kqrpre2()            000000010 ? 7FFFBFFACDA0 ?\n                                                   7FFFBFFACDA8 ? 000000005 ?\n                                                   09670BF20 ? 000000000 ?\nqoshdd()+155         call     kqrpre()             000000010 ? 7FFFBFFACDA0 ?\n                                                   7FFFBFFACDA8 ? 000000005 ?\n                                                   09670BF20 ? 000000000 ?\n<span style=\"color:blue\">kkpodp_del_stats()<\/span>+  call     qoshdd()             000030E3D ? 7FFFBFFACDA0 ?\n199                                                7FFFBFFACDA8 ? 000000005 ?\n                                                   09670BF20 ? 000000000 ?\nctcfmo_fragment_mai  call     kkpodp_del_stats()   000000000 ? 0812A66B8 ?\nnt_oper()+1783                                     000000000 ? 087D123C8 ?\n                                                   087405A68 ? 000000000 ?\nctcdrv()+32784       call     ctcfmo_fragment_mai  08BEC5008 ?\n                              nt_oper()            FFFFFFFF8D31A750 ?\n                                                   000000000 ? 0840DA650 ?\n                                                   000000000 ? 7FFFBFFAD790 ?\n...<\/code><\/pre>\n<p>As we can see, the function that was executing was <i><span style=\"color:blue\">kqrget<\/span><\/i>, which is the function for getting a row cache lock (see <a href=\"http:\/\/orafun.info\/\">Frits Hoogland&#8217;s Oracle C functions annotations<\/a>). This is consistent with the deadlock diagram, as the process was waiting on a row cache lock. The row cache lock was acquired for deleting the statistics on the partitioned dictionary table. We know that, because <i><span style=\"color:blue\">kkpodp_del_stats<\/span><\/i> is higher up on the stack.<\/p>\n<p>Next, let&#8217;s check the stack for gather statistics:<\/p>\n<pre><code>----- Call Stack Trace -----\ncalling              call     entry                argument values in hex\nlocation             type     point                (? means dubious value)\n-------------------- -------- -------------------- ----------------------------\nksedst1()+410        call     skdstdst()           7FFFBFFE7C30 ?\n                                                   2DD66E470C094B ?\n                                                   7FFFBFFE7C20 ? 7FFFBFFC1B18 ?\n                                                   7FFFBFFC50B0 ? 7FFFBFFC12F8 ?\nksdhng_dumpcb_hung_  call     ksedst1()            000000000 ? 000000001 ?\nproc_int()+843                                     7FFFBFFE7C20 ? 7FFFBFFC1B18 ?\n                                                   7FFFBFFC50B0 ? 7FFFBFFC12F8 ?\nksdhng_action_execu  call     ksdhng_dumpcb_hung_  000000001 ? 0B6B3B310 ?\nte()+148                      proc_int()           7FFFBFFE7C20 ? 7FFFBFFC1B18 ?\n                                                   7FFFBFFC50B0 ? 7FFFBFFC12F8 ?\nksuintract_exec_pen  call     ksdhng_action_execu  000000000 ? 0B6B3B310 ?\nding()+352                    te()                 7FFFBFFE7C20 ? 7FFFBFFC1B18 ?\n                                                   7FFFBFFC50B0 ? 7FFFBFFC12F8 ?\nksuitr()+6419        call     ksuintract_exec_pen  000000000 ? 0B6B3B310 ?\n                              ding()               7FFFBFFE7C20 ? 0B6B6D6A0 ?\n                                                   7FFFBFFC50B0 ? 7FFFBFFC12F8 ?\nksu_dispatch_tac()+  call     ksuitr()             000000039 ? 0B6B3B310 ?\n2384                                               7FFFBFFE7C20 ? 0B6B6D6A0 ?\n                                                   7FFFBFFC50B0 ? 7FFFBFFC12F8 ?\nksfgti()+108         call     ksu_dispatch_tac()   000000039 ? 0B6B3B310 ?\n                                                   7FFFBFFE7C20 ? 0B6B6D6A0 ?\n                                                   7FFFBFFC50B0 ? 7FFFBFFC12F8 ?\n<span style=\"color:blue\">kglLockWait()<\/span>+881    call     ksfgti()             000000039 ? 0B6B3B310 ?\n                                                   7FFFBFFE7C20 ? 0B6B6D6A0 ?\n                                                   7FFFBFFC50B0 ? 7FFFBFFC12F8 ?\nkgllkal()+1394       call     kglLockWait()        7FFFBDB7DA10 ? 7FFFBFFE9D30 ?\n                                                   087D123C8 ? 0863522D0 ?\n                                                   87D1244800000003 ?\n                                                   7FFFBFFC12F8 ?\nkglLock()+19861      call     kgllkal()            7FFFBDB7DA10 ? 7FFFBFFE9480 ?\n                                                   087D123C8 ? 000000003 ?\n                                                   0863522D0 ? 7FFFBFFE9D30 ?\nkglget()+1920        call     kglLock()            7FFFBDB7DA10 ? 7FFFBFFE9D30 ?\n                                                   000000003 ? 000000001 ?\n                                                   7FFFBD83CF98 ? 7FFFBFFE9D30 ?\nqostobkglrel()+510   call     kglget()             7FFFBDB7DA10 ? 7FFFBFFE9D30 ?\n                                                   000000003 ? 000000001 ?\n                                                   7FFFBD83CF98 ? 7FFFBFFE9D30 ?\nqostobctxrel()+311   call     qostobkglrel()       7FFFBD83CD88 ? 7FFFBFFE9D30 ?\n                                                   000000003 ? 08D62EAF8 ?\n                                                   7FFFBD83CF98 ? 7FFFBFFE9D30 ?\n<span style=\"color:blue\">qospscs()<\/span>+8574       call     qostobctxrel()       7FFFBFFEA4F0 ? 7FFFBFFE9D30 ?\n                                                   000000003 ? 08D62EAF8 ?\n                                                   7FFFBD83CF98 ? 7FFFBFFE9D30 ?\nspefcmpa()+1156      call     qospscs()            7FFFBFFECA60 ? 7FFFBD3BC540 ?\n                                                   000000149 ? 000000210 ?\n                                                   7FFFBD8863A8 ?\n                                                   BD8863A800000000 ?\nspefmccallstd()+233  call     spefcmpa()           7FFFBD8864D8 ? 00000000A ?\n                                                   000000000 ? 000000210 ?\n                                                   7FFFBD8863A8 ?\n                                                   BD8863A800000000 ?\n...<\/code><\/pre>\n<p>Also here, the stack is consistent with the deadlock diagram &#8211; the process is waiting on a library cache lock (<i><span style=\"color:blue\">kglLockWait<\/span><\/i>). The caller is <i><span style=\"color:blue\">qospscs<\/span><\/i> &#8211; the Oracle C function underpinning DBMS_STATS.SET_COLUMN_STATS_CALLOUT.<\/p>\n<h1>Debugging<\/h1>\n<p>Based on this information in the call stacks, we can use GNU debugger for reproducing the deadlock. Mind that the described procedure heavily relies on Oracle internals, so it might not be valid for all software versions. I tested it on a 19.6.0.0.200114 database.<\/p>\n<p>I modelled the problem with a table having a similar, yet much simpler, structure:<\/p>\n<pre><code>\ncreate table t ( \n  n number, constraint t_pk primary key (n) using index local\n  (\n    PARTITION t_10 ,\n    PARTITION t_20\n  ) enable\n)\n  PARTITION BY RANGE (n)\n  (\n    PARTITION t_10 VALUES LESS THAN (10),\n    PARTITION t_20 VALUES LESS THAN (MAXVALUE) \n  )\n;\n\ninsert into t select level n from dual connect by level &lt; 20 ;\ncommit ;\nexec dbms_stats.gather_table_stats (null, 'T', cascade =&gt; true ) ;\n<\/code><\/pre>\n<p>The relevant features are: partitions and a local index. Besides that, the table must be created in the SYS schema.<\/p>\n<p>By using the information in the stack trace and gdb, it&#8217;s easy to reproduce the problem. Simply, attach to the process that will be gathering statistics, set the following breakpoint and continue the execution:<\/p>\n<pre><code>b qostobkglrel\nc<\/code><\/pre>\n<pre><code>exec dbms_stats.gather_table_stats (null, 'T', cascade =&gt; true ) ;<\/code><\/pre>\n<p>When statistics gathering reached the breakpoint, run <i>split partition<\/i> until it hits the breakpoint on <i>qoshdd<\/i>:<\/p>\n<pre><code>b qoshdd\nc<\/code><\/pre>\n<pre><code>alter table t split partition t_10 at (5) into (partition t_5, partition t_10 ) update indexes ;<\/code><\/pre>\n<p>After <i>split partition<\/i> stops at the breakpoint, continue the execution of gather statistics until <i>kglget<\/i>:<\/p>\n<pre><code>b kglget\nc<\/code><\/pre>\n<p>Resume <i>split partition<\/i> until the following breakpoint:<\/p>\n<pre><code>b kqrpre if $rdi==16\nc<\/code><\/pre>\n<p>After both breakpoints have been reached, continue the process executions:<\/p>\n<pre><code>c<\/code><\/pre>\n<p>We can clearly see in the active session history that two sessions are waiting on each other:<\/p>\n<pre><code>@<a href=\"https:\/\/github.com\/tanelpoder\/tpt-oracle\/blob\/master\/ash\/ash_wait_chains2.sql\">ash_wait_chains2<\/a> event2||sql_opname 1=1 &amp;1min\n\n-- Display ASH Wait Chain Signatures script v0.5 BETA by Tanel Poder ( http:\/\/blog.tanelpoder.com )\n\n%This     SECONDS     AAS WAIT_CHAIN                                                                                                                                                                                                                                                                                                   FIRST_SEEN          LAST_SEEN\n------ ---------- ------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------- -------------------\n  48%          59     1.0 -&gt; <span style=\"color:blue\">row cache lock CREATE TABLE -&gt; library cache lock PL\/SQL EXECUTE<\/span> -&gt; [idle blocker 1,1405,54879 (sqlplus@svdbp01i (TNS V1-V3))]                                                                                                                                                                            2020-04-01 18:36:25 2020-04-01 18:37:24\n  48%          59     1.0 -&gt; <span style=\"color:blue\">library cache lock PL\/SQL EXECUTE -&gt; row cache lock CREATE TABLE<\/span> -&gt; [idle blocker 1,2159,25346 (sqlplus@svdbp01i (TNS V1-V3))]                                                                                                                                                                            2020-04-01 18:36:25 2020-04-01 18:37:24\n<\/code><\/pre>\n<p>After detaching the debugger from both processes, Oracle resolved this deadlock:<\/p>\n<pre><code>detach<\/code><\/pre>\n<pre><code>alter table t split partition t_10 at (5) into (partition t_5, partition t_10 ) update indexes ;\nalter table t split partition t_10 at (5) into (partition t_5, partition t_10 ) update indexes\n*\nERROR at line 1:\nORA-00060: deadlock detected while waiting for resource<\/code><\/pre>\n<p>Curiously, the deadlock doesn&#8217;t occur if the table is created outside the SYS shema.<\/p>\n<p>So, what&#8217;s the difference?<\/p>\n<h1>Library cache lock<\/h1>\n<p>We can explore library cache locks when gather statistics reaches the breakpoint on <i>qostobkglrel<\/i> and <i>split partition<\/i> is waiting on the library cache lock:<\/p>\n<pre><code>\nset pagesize 20\ncolumn  sql_text format a20\ncolumn KGLNAOBJ format a10\n\nselect ses.sid, ses.serial#,lck.kgllkcnt, lck.kgllkmod,lck.kgllkreq, lck.kglnaobj, s.sql_text\n  from x$kgllk lck , v$session ses, v$sqlarea s\n  where kgllkhdl in\n     (select kgllkhdl from x$kgllk where kgllkreq &gt;0)\n  and lck.KGLLKUSE = ses.saddr\n  and ses.sql_id = s.sql_id\n;<\/code><\/pre>\n<p>(The query above is from <a href=\"https:\/\/orainternals.wordpress.com\/2016\/05\/21\/library-cache-lock-on-build-object\/#more-1602\">Riyaj Shamsudeen&#8217;s blog post library cache lock on BUILD$ object<\/a>; I enriched it with the sql_text.)<\/p>\n<p>For the table in a non-SYS schema gather statistics acquires a shared lock (<span style=\"color:red\">2<\/span>):<\/p>\n<pre><code>       SID    SERIAL#   KGLLKCNT   KGLLKMOD   KGLLKREQ KGLNAOBJ   SQL_TEXT\n---------- ---------- ---------- ---------- ---------- ---------- --------------------\n      1405      42006          0          0          3 T          alter table t split\n                                                                  partition t_10 at (5\n                                                                  ) into (partition t_\n                                                                  5, partition t_10 )\n                                                                  update indexes\n\n      2159      13084          1          <span style=\"color:red\">2<\/span>          0 T          BEGIN dbms_stats.gat\n                                                                  her_table_stats (nul\n                                                                  l, 'T', cascade =&gt; t\n                                                                  rue ) ; END;<\/code><\/pre>\n<p>In contrast, the gather statistics process grabs the exclusive lock (<span style=\"color:green\">3<\/span>) when the table is outside the SYS schema:<\/p>\n<pre><code>       SID    SERIAL#   KGLLKCNT   KGLLKMOD   KGLLKREQ KGLNAOBJ   SQL_TEXT\n---------- ---------- ---------- ---------- ---------- ---------- --------------------\n      1405      18584          0          0          2 T          alter table t split\n                                                                  partition t_10 at (5\n                                                                  ) into (partition t_\n                                                                  5, partition t_10 )\n                                                                  update indexes\n\n      2159      13084          1          1          0 T          BEGIN dbms_stats.gat\n                                                                  her_table_stats (nul\n                                                                  l, 'T', cascade =&gt; t\n                                                                  rue ) ; END;\n\n      2159      13084          1          <span style=\"color:green\">3<\/span>          0 T          BEGIN dbms_stats.gat\n                                                                  her_table_stats (nul\n                                                                  l, 'T', cascade =&gt; t\n                                                                  rue ) ; END;<\/code><\/pre>\n<p>The exclusive lock acqured by the statistics gathering prevents <i>split partition<\/i> entering the critical code path where deadlock is possible. <i>Split partition<\/i> will, instead, simply wait until gather statistics releases the lock.<\/p>\n<h1>Summary<\/h1>\n<p>In summary, both split partition and gather statistics change the column statistics. Getting a library cache lock on the table and a row cache lock on the <i>dc_histograms<\/i> cache is a prerequisite for changing the column statistics. For tables in the SYS schema only a shared library lock is acquired at some critical point. Shared lock can lead to concurrency problems, because the execution of both processes can progress so that they acquire library cache and row cache locks in such a way that they block each other. This is the reason why a deadlock sometimes happens when the partition maintenance on workload repository tables is running concurrently with the statistics gathering.<\/p>\n<p>In contrast, the library cache lock in exclusive mode prevents concurrency problems on tables outside the SYS schema .<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Reproducing Oracle deadlock bug with debugger <a href=\"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/\" class=\"more-link\">Continue Reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[27,39,5],"tags":[],"class_list":["post-3281","post","type-post","status-publish","format-standard","hentry","category-gdb","category-library-cache","category-oracle"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Deadlock Caused by Row Cache and Library Cache Locks - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Reproducing Oracle deadlock bug with debugger\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Deadlock Caused by Row Cache and Library Cache Locks - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Reproducing Oracle deadlock bug with debugger\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2020-04-06T15:42:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-02-11T16:59:58+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Deadlock Caused by Row Cache and Library Cache Locks\",\"datePublished\":\"2020-04-06T15:42:08+00:00\",\"dateModified\":\"2021-02-11T16:59:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/\"},\"wordCount\":834,\"commentCount\":3,\"articleSection\":[\"gdb\",\"library cache\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/\",\"name\":\"Deadlock Caused by Row Cache and Library Cache Locks - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-04-06T15:42:08+00:00\",\"dateModified\":\"2021-02-11T16:59:58+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Reproducing Oracle deadlock bug with debugger\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/deadlock-row-cache-lock-library-cache-lock\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Deadlock Caused by Row Cache and Library Cache Locks\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Deadlock Caused by Row Cache and Library Cache Locks - All-round Database Topics","description":"Reproducing Oracle deadlock bug with debugger","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/","og_locale":"en_US","og_type":"article","og_title":"Deadlock Caused by Row Cache and Library Cache Locks - All-round Database Topics","og_description":"Reproducing Oracle deadlock bug with debugger","og_url":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/","og_site_name":"All-round Database Topics","article_published_time":"2020-04-06T15:42:08+00:00","article_modified_time":"2021-02-11T16:59:58+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Deadlock Caused by Row Cache and Library Cache Locks","datePublished":"2020-04-06T15:42:08+00:00","dateModified":"2021-02-11T16:59:58+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/"},"wordCount":834,"commentCount":3,"articleSection":["gdb","library cache","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/","url":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/","name":"Deadlock Caused by Row Cache and Library Cache Locks - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2020-04-06T15:42:08+00:00","dateModified":"2021-02-11T16:59:58+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Reproducing Oracle deadlock bug with debugger","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/deadlock-row-cache-lock-library-cache-lock\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Deadlock Caused by Row Cache and Library Cache Locks"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=3281"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3281\/revisions"}],"predecessor-version":[{"id":3299,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3281\/revisions\/3299"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=3281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=3281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=3281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}