注: 本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

原文链接：【Oracle】存储对象的LIBRARY CACHE LOCK/PIN实验(一) - 课程体系 - 云贝教育

实验环境

操作系统：Red Hat Enterprise Linux release 8.8 (Ootpa)

数据库：oracle Version 19.3.0.0.0

一、存储对象的LOCK和PIN

在生产环境上，我们碰到的大部分library cache pin等待主要源头是存储过程。那该现象底层原理是什么？接下来通过实验来把这个等待链梳理清晰。

该实验的逻辑：

1、会话1执行某个运行时间很长的存储过程

2、会话2尝试编绎、修改、删除该存储过程

备注：以下操作在PDB下操作

dump library_cache 命令参考

1.1 创建存储过程

test_library_cache_lock_pin在内存中属于存储类型

1.2 dump库缓存

此时存储过程刚创建编绎完成，查看此时存储过程的dump信息。

1）查看存储过程的dump信息

接下来针对以上dump进行解析

2）解析dump信息

1.library cache对象信息

重点关注存储过程的library cache类型、句柄和堆上持有的锁类型。

• 存储过程只是编绎，并未执行，所以对象上的PIN和LOCK都是0
• 没有子游标信息

2.依赖信息

可以看到，存储过程有4个依赖对象

• DBMS_RANDOM
• STANDARD
• LIBRARY_CACHE_LOCK_PIN
• SYS_STUB_FOR_PURITY_ANALYSIS

3.底层调用的表信息

以表对象LIBRARY_CACHE_LOCK_PIN为例，查看 Handle=0x70b57c08的对象信息

• 表对象的类型Namespace=TABLE/PROCEDURE(01) Type=TABLE(02)
• 持有的锁LockMode=0 PinMode=0

4.存储过程的heap信息

可以看到存储过程包括

• heap0
• heap1
• heap2
• heap4
• heap7
• heap13

可以看到，存储过程没有heap6

1.3 调用存储过程

通过调用存储过程，看下执行存储过程中，调用的锁信息

1）在调用存储过程之前，调整一下存储过程内存，让它延时100000秒执行完成。

2）查看修改之后的存储过程对象信息

相比之前的dump，多了一个依赖对象信息。

3）调用该存储过程

看看执行存储过程的时候，对象上的pin和lock

1.执行语句的pin和lock

存储过程的类型也是游标，和SQL不一样，它属于存储对象

注：SQL类型是游标，属于瞬时对象，瞬时对象的LOCK锁只有0和N。

2.父游标heap0信息

可以看到父游标heap 0上的pins=0，表示heap上未加pin锁。

同时可以看到子游标句柄也在父游标heap 0中。这里的ChildTable,表示有几个子游标，这里id=0，表示只有一个子游标。

3.子游标信息

观察如下

• 子游标句柄上的申请的锁为LockMode=N PinMode=S，因为该游标正在执行，PIN是保护这个游标的OBJECT部分，也就是堆的部分。子游标的对象信息被保护，此时如果想修改这个对象，需要获取X模式的PIN锁，无法获取到
• 可以观察到，只有子游标下的HEAP0和HEAP6的Flags=I/-/P/A/-/-/-。这里P表示被PIN住。

4.通过依赖对象Handle=0x76a09550找到存储过程的信息

这里的对象信息就是存储过程调用的存储过程。

存储过程本身的 LockMode=N PinMode=S ，因为它在执行过程中需要申请的锁。

5.NamespaceDump 命名空间

Child Cursor：记录了子游标下的heap0和heap6的生成信息。Heap0=0x73c1f2f0 Heap6=0x6af3b320和DataBlocks中heap0、heap6的Pointer信息对应。

总结

创建创建过程，未执行

执行存储过程的时候

二、library cache pin竞争

从上面的实验我们知道，存储过程在执行过程中，针对TEST_LIBRARY_CACHE_LOCK_PIN持有的LOCK=N,PIN=S。

2.1 设想生产环境场景

设想场景1：在另一个会话中执行删除存储过程的命令

设想场景2：修改存储过程定义

设想场景3：编绎存储过程

通过实验发现，会话1在执行存储过程的时候，如果另外一个会话针对存储过程执行DDL操作，例如修改、 删除或编绎，会等待library cache pin。

2.2 通过dump验证

执行存储过程BEGIN test_library_cache_lock_pin; END;

执行语句的信息ORCLPDB1.SYS.TEST_LIBRARY_CACHE_LOCK_PIN

2.3 在执行compile之后，查看dump信息

这里我们看编绎命令的句柄

子游标句柄上的锁

#这里可以看LockMode=N PinMode=X，因为是修改操作，所以compile在子游标句柄上申请X模式的PIN琐，N模式的LOCK琐。PIN住的目的是为了获得句柄上的独占锁，为后面修改做准备。

2.4 存储对象上的dump

2.5 对象上的琐

存储过程对象上能申请到LockMode=X ，表示complie语句已经获得TEST_LIBRARY_CACHE_LOCK_PIN上模式X的LOCK锁。为什么能获取到？因为执行compile之前对象上的LockMode=N，N锁不阻塞X锁获取。

PinMode=S，表示此时execute语句是分析阶段，所以需要获取共享pin锁。那如果compile进入执行阶段，需要获取x模式的pin锁,与execute阶段锁冲突，所以无法获取X模式的pin锁。

这也是为什么compile会话只等待libary cache pin，而不等待libarary cache lock的原因。

如果此时在表上执行DDL，会报资源ORA-00054

修改DDL锁参数

发现此时的锁已经不是library cache上的，而是enq表锁，阻塞源是正在执行的存储过程，该存储过程调用了表,而表上的操作是dml，如果是dql还会报错吗？

实验证明，依然报错

三、分析验证

通过等待事件可以确认产生等待的对象，那如何确认等待链关系？

3.1 hangalayze

生成日志

1) 查看等待链关系

PL/SQL lock timer堵塞了library cache pin，那哪个会话是源头，哪个会话是受害者？

看下被堵塞会话session id: 426（等待libraray cache pin）的调用关系

继续查看等待链关系

LEAF是阻塞源头，NLEAF是被阻塞者

2) 对比动态性能视图结果

SID=426

1.查看历史等待情况

v$session_event

session id: 426中wait history中排名前3等待一致

2.查看会话当前等待情况

v$session

或

v$session_Wait

与waiting for 'library cache pin' with wait info信息一致

3.查看等待事件的P1,P2,P3

查看等待事件详情

P1RAW=00000000769C3C80，就是存储过程的handle地址

P3RAW=00011D5500010003

00011D55表示ojbect_id

0001表示namespace,从dump中得知01对应的Namespace=TABLE/PROCEDURE(01)

003表示pinmode，3表示x模式的pin锁

00011D55转换十进制73045，对象TEST_LIBRARY_CACHE_LOCK_PIN的OBJECT_ID

四、总结

通过以上实验，我们对存储对象相关操作产生的library cache pin有了很清晰的认识，那如果是library cache lock相关竞争，又该如何模拟验证？接下来的实验暂时留给读者，期待我们下一篇文章。