EBS R12 - Workflow mailer ORA-00054 due to bug 18723483 / Lock issues in Approval Workflows

This blog post is about a lock issue encountered in EBS R12.  

The environment was an EBS 12.1.3, and workflow mailer was configured both for outbound and inbound. ( 1 process for outbound, 1 process for inbound.. The IMAP account used by the mailer was dedicated to it.. So no other environment was using that IMAP account other than the wf mailer of this problematic environment. Having dedicated IMAP accounts for each EBS environment is a must bytheway.)

In this post, we will see how workflow mailer can lock some records and prevent EBS forms operations from taking actions on those records..

Our story is about an approval workflow and the problem was escalated to me with the following decription : "Users are unable to give approval for certain requests. They get ORA-00054 while trying to approve those requests" .

First thing I checked wast the locks in the EBS database.. It could easily be seen that, we had some TX locks on WF_NOTIFICATIONS and WF_NOTIFICATIONS_OUT.. 

The Workflow mailer seemed the owner of the session that was holding the locks. But why? How could workflow mailer hold those locks for so long? Could there be a SMTP or IMAP problem, an unexpected an error in mailer's log? 

The answer was No. Everyting was clear and that made me revisit the dynamics of the worklow mailer..

--note that we had a lock on OUT queue, so probably the cause was related with the outbound..

So, here is the general process that is executed by the workflow mailer while sending an email;

WF Mailer dequeues a given notification from WF_NOTIFICATION_OUT queue. This places a lock on that message in WF_NOTIFICATION_OUT queue (1st LOCK here)

Next, it builds a MIME message for that notification and sends it as e-mail. (If it fails here(a fatal failure), the lock it got in the first step may not be released)

Then, it locks that given notification in WF_NOTIFICATIONS table. updates the STATUS and MAIL_STATUS columns. (2nd LOCK here..)

Finally, GSC layer issues a commit (COMMIT is here)

Well, this made me check 2 things;

*"processor close on read timeout" checkbox should be checked.

*WF background engine may cause those locks somehow.. So it is better to optimize it using the following recommendation;

-Run a background engine to handle only deferred activities every 5 to 60 minutes.

-Run a background engine to handle only timed out activities every 1 to 24 hours as needed.

-Run a background engine to handle only stuck processes once a week to once a month, when the     load on the system is low.

However; "processor close on read timeout" was already checked and background engine configuration was already optimized according to the recommendation.. 

It was the WF mailer that was causing this lock issue and it seemed that we were dealing with an undocumented behaviour.. So I checked the bugs..

Anyway let me come to the conclusion now.. 

Well, the cause was a bug.. Bug 18723483... Do not worry, the development is working on it :) 

Besides, we have a quick and easy workaround.. 

We just restart the workflow mailer and that's it.. Lock is released and we continue our work .. :)

That's it for today. Have a nice weekend.