Isolation level read committed

SQL Server Administration
1

I am looking at going from Isolation Level Read Committed to Read Committed Snapshot. I noticed in a different system this change reduced both Blocking and Deadlocks.

If anyone has ever decided making this change would not be a good idea, I would be interested in why? Just in case any concerns come up when I suggest this.

2

You need to be aware of the overhead involved.

  1. Old row versions must be kept in tempdb as long as they are needed. Could be a little, could be a lot, could be a short time, could be a long time. Depends on your particular environment.
  2. Updated rows require 14 additional bytes per row. If you have, say, 20 rows on a page, that could be 280 bytes of overhead per page. If you don't have much free space per page, this could result in lots of page splits, which would be extremely bad for performance.
1 Like
3

With Read Committed Snapshot, readers won't block writers and vice-versa.

So I'm wondering if a Select can still be involved in a Deadlock somehow?

4

Yes, for anything that requires an exclusive lock. For example, a schema changes, such as trying to change a column from varchar(10) to varchar(12).

1 Like
5

Thanks. We are seeing some deadlocks involving schema changes.

But we are also seeing these when no schema changes are happening:

  • Deadlocks where the victim is a Select Statement
  • Select statements being blocked
  • Select statements blocking other sessions

I want to tell the team that all of that will go away with Read Committed Snapshot. But I wanted to double-check first...

6

1 could still happen, although it will be vastly more rare.
2 will not happen barring a schema change taking place, or some other exclusive lock already existing on the table
3 will not happen, again except for attempted schemas changes or other things that require an exclusive lock

7

Good point, such as if anyone specifies XLOCK Select query. I don't think that's the case here. But I better double-check for that...