One of my readers asked me if I could write about locking and blocking in the database, more specifically about the following error:

ORA-04021: TIMEOUT OCCURRED WHILE WAITING TO LOCK OBJECT

The library cache, which is part of the shared pool in the Oracle instance, contains the definition of different objects, cursors, indexes, tables, functions, packages. When these objects are in use, they cannot be changed.

If someone is updating a table, you cannot change the definition of the table.
If someone is using a procedure, you cannot compile the procedure.
If someone is using an index, you cannot drop the index.

You get the picture.

Why can’t you alter these objects, while in use?

These objects are locked in the library cache, by a mechanism based on library locks and library pins.
When a user wants to use an object, the session has to obtain a library cache lock in null, shared, or exclusive mode on the objects, depending on the operation.

If you want to drop a table, you need an exclusive lock. This lock will prevent other sessions from executing the same operation, or other disruptive operations (dropping the object).

After the lock is acquired on the object, the session also must obtain a pin in a specific mode: null, shared, exclusive, depending on the operation.

A session that is waiting on an object already locked/pinned in the library cache, that is incompatible with the requested mode, will wait on events similar to library cache pin or library cache lock, for a limited time, at which point a timeout occurs.

Usually the timeout occurs after 5 minutes, and the waiting user will receive the ORA-4021 message TIMEOUT OCCURRED WHILE WAITING TO LOCK OBJECT.

You most likely want to troubleshoot and see who is holding a lock on your object. The problem is that the troubleshooting has to happen during the wait period, before the ORA-4021 occurs. I recommend that you start investigating when you see that your session is waiting, or hanging, and the session cursor is not returned to you right away.

Views that you can use to troubleshoot:

V$ACCESS
DBA_DDL_LOCKS
V$LOCKED_OBJECT

You can start your troubleshooting by checking regular blocking/locking issues. I wrote about that in a previous blog post, Who Is Holding The Lock On The Table?

I was not able to reproduce the error in my test environment, the above information is from my research.