About This Blog

A copy of my content originally hosted on SQLBlog.com
Showing posts with label Locking. Show all posts
Showing posts with label Locking. Show all posts

Monday, 1 November 2010

The Case of the Missing Shared Locks

The Case of the Missing Shared Locks

This post covers a little-known locking optimization that provides a surprising answer to the question:

If I hold an exclusive lock on a row, can another transaction running at the default read committed isolation level read it?

Most people would answer ‘no’, on the basis that the read would block when it tried to acquire a shared lock. Others might respond that it depends on whether the READ_COMMITTED_SNAPSHOT database option was in effect, but let’s assume that is not the case, and we are dealing simply with the default (locking) read committed isolation level.