ORA-00054: Resource Busy and Acquire with NOWAIT Specified ORA-06512 Approving Workflows (Doc ID 750724.1)

Last updated on FEBRUARY 02, 2022

Applies to:

Symptoms

Users are facing the following error when they are trying to approve POs, PRs, Expense report both through email notifications and Worklist.

This is happening with all modules and with all users.

User is attempting to respond to a notification (this updates the STATUS of the given notification id in WF_NOTIFICATIONS table) and they face an error that the resource is busy. This means that another session is holding a lock on the object that this user is attempting to modify.

The only other possible session that could hold locks for the same notification id is the mailer. Mailer goes through in cycles as follows.
- dequeues a given notification from WF_NOTIFICATION_OUT queue. This places a lock on that message in WF_NOTIFICATION_OUT queue
- builds a MIME message for that notification and sends it as e-mail.
- locks that given notification in WF_NOTIFICATIONS table. updates the STATUS and MAIL_STATUS.
- the GSC layer issues a commit

During this process, for a very short period mailer holds the lock on that notification.

Locking exception comes when user tries to DO SOME ACTION ITEM on a notification from worklist UI while that has been locked by any other process ( Mailer / Agent Listener etc.). If a notification is being locked for a while then it is fine, but if it has been locked for a long time and lock is not getting released then there is some issue with that process which acquired lock on that notification.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.