Can you please elaborate on what do you mean by update/insert/delete to what? Are you implying that anytime a new connection is inserted/deleted, the Isolation level for all connections get reverted and is there a way to prevent that.
Since it gets reverted, when we query a databse, we end up locking the rows and causing deadlock issues?
Thanks Juliane for your explanation it makes sense. Last question hopefully. My isolation level setting in the middleware changes periodically. I have not updated that connection information in the middle ware and not sure what is causing it to be updated automatically. Is it because I am using that connection to do inserts/updates via a query tool?
I am not sure.
If your middleware works like an application server and has connection pooling, then the actual database connections will not be disconnected. So then the isolation can be changed from UR to CS when doing updates; and since the connections won't be disconnected, they will keep that until isolation is changed back to UR or disconnecting.
If it is a setting in your middleware - that should not change. Check with the vendor and documentation on what can cause such a behaviour.