Library Cache: Lock, Pin and Load Lock

What is "Library cache lock" ?

This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:

• One client can prevent other clients from accessing the same object.
• The client can maintain a dependency for a long time (for example, so that no other client can change the object).

This lock is also obtained to locate an object in the library cache.
Library cache lock will be obtained on database objects referenced during parsing or compiling of SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym). The lock will be released at the end of the parse or compilation.

Cursors (SQL and PL/SQL areas), pipes and any other transient objects do not use this lock.
Library cache lock is not deadlock sensitive and the operation is synchronous.

Parameters:
 

• handle address
  Address of the object being loaded.
  
  
• lock address
  Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object.
  
  
• Mode
  Indicates the data pieces of the object which need to be loaded.
  
  
• Namespace
  The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view

What is "Library cache pin"?

This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock. Pin can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered as a special form of lock. A wait for a "library cache pin" implies some other session holds that PIN in an incompatible mode.

Library cache pin will be obtained on a database object that is currently cached in the library cache (table, view, procedure, function, package, package body, trigger, index, cluster, synonym). In the library cache, a database object is cached in 2 parts: "handle" and "object". Library cache pin is only held when the "object" part is cached.

It is not deadlock sensitive and the operation is synchronous