How to Lock SP?

Question

Dear Mahesh Pls help me in solving this issue. Iam using VB.NET and Oracle in my application. In the front end  when I click a button iam calling an SP in the backend since that calling process takes longer time( SP keeps running in the backend) and the application hangs. Later when I recompile the SP it says Timeout occured while waiting for lock. So we need to restart the IIS and if we compile again it works. So how to avoid SP Locking? (SP itself is getting locked not the Transaction) Pls help me. Regards deepa

Amit Kumar · Answer

The same problem faced by me in one of my project. For this you need to create the table and trigger on this table. The entry in the table contain the flag which store the value Yes/No. On the basis of this flag your trigger will call the required sp.