We didn't set any isolation level on our db(2012)we are observing blocking and deadlocks frequently.Can we set read committed isolation to avoid them. Please let me know if there any disadvantage to set these isolation level.

Could you please advice which isolation will be better to avoid these locks.

Thanks in advance.

 

Read_committed_snapshot is the database option that affects behavior of the readers in READ COMMITTED isolation level. When you enable this option on database level (requires exclusive db access to change it), all your readers in READ COMMITTED isolation level will start to use row-versioning reading old(committed) versions from the version store rather than being blocked by (S)/(X) locks incompatibility. This option does not require any code changes (assuming you readers are using READ COMMITTED). It would not change behavior of writers (writers still block each other) nor behavior of readers in any other transaction isolation level.

 

Snapshot isolation level is full blown transaction isolation level. It needs to be explicitly specified in the code. Enabling snapshot isolation level on DB level does not change behavior of queries in any other isolation levels. In that option you are eliminating all blocking even between writers (assuming they do not update the same rows) although it could lead to 3960 errors (data has been modified by other sessions).

 

Speaking of consistency, RCSI gives you statement level consistency readers ignores the data changes done after statement has been started. Snapshot transaction level consistency session deals with snapshot of the data at the moment transaction started. Again, it could lead to error 3960 in the system with volatile data.

 

Downsides:

1.       Excessive tempdb usage due to version store activity. Think about session that deletes 1M rows. All those rows must be copied to version store regardless of session transaction isolation level and/or if there are other sessions that running in optimistic isolation levels at the moment when deletion started.
2.       Extra fragmentation SQL Server adds 14-byte version tag (version store pointer) to the rows in the data files when they are modified. This tag stayed until index is rebuild
3.       Development challenges again, error 3960 with snapshot isolation level. Another example in both isolation levels trigger or code based referential integrity. You can always solve it by adding with (READCOMMITTED) hint if needed.

 

While switching to RCSI could be good emergency technique to remove blocking between readers and writers (if you can live with overhead AND readers are using read committed), I would suggest to find root cause of the blocking. Confirm that you have locking issues check if there are shared lock waits in wait stats, that there is no lock escalations that block readers, check that queries are optimized, etc.  

We didn't set any isolation level on our db(2012)we are observing blocking and deadlocks frequently.Can we set read committed isolation to avoid them. Please let me know if there any disadvantage to set these isolation level.

Could you please advice which isolation will be better to avoid these locks.

Thanks in advance.

You cannot set pessimistic Isolation level at database instance, isolation level is connection specific unless you are using row versioning bases isolation level.

So when you have not set any isolation level the default isolation level which is READ COMMITTED will work so no need to set you are good to go

If you need to execute, for example the UPDATE statement over an SQL Server table, and this table is used by another process at the same time (deadlocks occur sometimes). I would recommend you:

READ UNCOMMITTED

But that allows the process to read the data before a transaction has committed, what is known as a dirty read. You can read more here.

You may prefer to turn on row versioning, the update creates a new version of the row and any other select statements use the old version until this one has committed. To do this turn on READ_COMMITTED_SNAPSHOT mode. There is more info here. There is an overhead involved maintaining the versions of the rows but it removes UPDATE/SELECT deadlocks.

Any way, the suggestion to use READ UNCOMMITTED is good. However, you need to figure out why you're getting a deadlock. If you don't care about dirty reads then that is OK, but if you need to benefits of isolation (consistency, etc) then I recommend figuring out a proper locking strategy in your application.

Also you may have a look into snapshot isolation. This level of isolation is a good compromise between consistency and speed. 

• Edited by Andrey Langovoy 18 minutes ago