We have an application from JD Edwards, for which the database runs on SQL Server 2005 or 2008R2.
They have a new application vendor and he suggested to change isolation level to Read Committed Snapshot Isolation (RCSI) instead of default locking Read Committed to resolve a blocking issue:
Long story short, client was complaining about getting error & we involve application team to look logs on app server & at that same time we see blocking on database so then we send the SPID & app team for rollback or kill that connection & issue will disappear so they have few API cursors & users searching for data every now & then. As a maintenance we are doing rebuild index & update stats on weekly basis.
We see hundreds of cursors running on server & I give the SPID of first blocking happened & then application team find out rest, but what i am seeing is below:
FETCH API_CURSOR0000000000A4804D FETCH API_CURSOR0000000000A467E6 FETCH API_CURSOR00000000006AF7B0 Microsoft SQL Server JDBC Driver set transaction isolation level read committed
I told them by changing to RCSI, each update will hold a pointer if data is reading on that page or row, we also can get dirty reads and will use more tempdb in SQL Server. We have had a back and forth conversation and can't decide whether to make this change on the server.
Can anyone point out the pros and cons of using RCSI, or tell me if you have experience with the JD Edwards application running on SQL Server and best practices for the isolation level?
