2

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?

3

By default, SQL Server writers block readers and vice versa if Read Committed Snapshot Isolation is not enabled. If the application is not written exclusively for SQL Server, it may cause lots of extra locks (SQL Server can also escalate locks) when database is used more or less extensively.
Enabling Read Committed Snapshot Isolation solves this issue and improves concurrency.

On the other hand, it adds load on tempdb (never a problem from my experience at least for SQL Server 2008R2 and higher) and may potentially cause some consistency issues for applications written for SQL Server only. Note, it has nothing to do with dirty reads.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.