Multiple Sessions Stacking and Locking

Hi @BABPAULS,

Quite many things can go wrong with client sessions that can lead to issues such as this so it would be quite difficult to come to a root cause without having a look at the system when the issues is happening. I have seen similar situations in the past where users tend to block own sessions when they forcefully close the current sessions using task manager. 

One of the scenarios that I’ve seen is as below,

1. User initiates a transaction which would take bit of time (let’s say 60 seconds in peak times).
2. The DB session for the said user starts the transaction and is waiting on enqueues since the system is busy for some reason (buffer busy waits, library cache locks waits, etc.)
3. Use gets fed up with the wait time after 40 seconds and uses the task manager to close the application. The DB session is still active since the the application was not closed gracefully. 
4. 20 seconds later, the DB session finishes the transaction. However, the locks used on the transaction are held as the commit or rollback of the transaction needs to be initiated from  client side but that application session is already closed forcibly closed by the user.
5. Now the user logs in to the application again and tries to do the same transaction. However, locks held for the previous transaction for the same data will not put him in a enqueue again sending them to a yet a another wait. There begins the vicious cycle of blocking sessions that builds up more blocks over time.

You can use a queries such as below to see what exact sessions are blocking the other sessions and what those blocking sessions are doing.

SELECT inst_id,sid,serial#,MODULE,program,event,blocking_session
FROM gv$session
WHERE username is not null

SELECT inst_id,sid,serial#,MODULE,program,event,blocking_session
FROM gv$session
WHERE sid = <blocking_session_from_above>

If the blocking sessions are in inactive state and the event is something like “SQL*Net message from client” it’s quite likely the scenario is similar to what i described. if they are active and waiting for system events such as I/O waits, the issue probably is sub optimal SQL’s , access plans or poor I/O performance. The exact issue will depend on the actual wait event you are seeing for the sessions what are blocking other sessions. 

hope this helps with your investigation into the issue.

Cheers

Thanks very much for this response, much appreciated. We’ll investigate this further

Going on from this we found that sessions were being limited to 1200 sessions. We found in the IFS Config a means of increasing this to 2400. Although we still have some work to do (one of the two application server is seeing a higher count than the other, so we are still investigating that), the sessions increased up to 1900 yesterday. We weren’t getting any record locks and users weren’t reporting performance issues.

We have a case with IFS that was raised under the initial problem - 503 errors being reported to users, as well as stacking sessions. We are looking to IFS to guide us as to optimising these config settings. We are unsure that by us increasing the session limit, does this affect other config settings. We are more in experimental mode rather than taking guided steps presently.

Hi BABPAULS,

Are you using weighted average parts by any chance as your costing method? 

Hi Infaz.

Yes, I believe we are. Does this affect what we are talking about here?

Yup, if you do use Shop order related operations there will be a cost recalculation job happening, this ultimately ends up updating the inventory cost table. When you are using weighted average costs these part related data will be locked, until the record is released you won’t be able to perform any transactions on the part. Also when a transaction is occurring Weighted average part’s cost table will be locked just to preserve the consistency of the cost read. 

Investigate sessions and check what tables are locked, if it involves Inventory_Part_Unit_Cost_TAB we both are talking about the same scenario.

Thanks Infaz. I’ll get the team here to investigate this. What you are saying certainly chimes with one of our most affected user groups - Procurement.

Much appreciated 

We checked the Inventory_Part_Unit_Cost_TAB for locking history and couldn’t find anything there.

An aspect of this issue might stem from what we have observed for a considerable time but have never been able to resolve.

We have load balancing between our two app servers, plus our three database servers. If we force connect to one of the app server, and then the other, and open a de-bug window in each, on one of them we often get a 503 error being declared for the FndWebSocketService. It polls every 20 seconds.

I’ve been monitoring this to see if there is a correlation with the 503 errors our users are getting and it seems there is. The error is present until users start reporting that they are getting 503 errors. If I leave the de-bug screen up, the errors stop and we see a positive line where the websocket service makes a connection.

Does this prompt any ideas?

Hi @BABPAULS ,

If there are no locks in that table it seems like purely a technical issue, having a debug console open is a workaround? I hope I got your idea. Not having a stable connection with the WebSocket could be an issue also where a response is not received locking could happen.

I have encountered strange locking issues some time back, due to a time mismatch between database servers once. Since you run three database servers, I ask you to look into that as well.  If none helps it's better to raise a case to IFS with all the information you gathered up to now.

Many thanks Infaz. I’ll get them to check this