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

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 Infaz.

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

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 

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.