title: SQL Server, the misleading XLOCK & optimizations
body: <p>From some recent testing and reading I've done, it seems the "X" (exclusive) name part of XLOCK is misleading. It in fact doesn't lock any more than UPDLOCK. If it were exclusive, it would prevent external SELECTs, which it doesn't.</p>
  
  <p>I cannot see either from reading or from testing and difference between the two.</p>
  
  <p>The only time XLOCK creates an exclusive lock is when used with TABLOCK. <strong>My first question is "why only at this granularity?"</strong></p>
  
  <p>Further, I came across a <a href="http://www.webservertalk.com/archive132-2005-4-1039490.html" rel="nofollow noreferrer">blog</a> that states the following:</p>
  
  <blockquote>
    <p>However, watch out for XLOCK hint. SQL Server will effectively ignore XLOCK hint! There's an optimization where SQL Server check whether the data has changed since the oldest open transaction. If not, then an xlock is ignored. This makes xlock hints basically useless and should be avoided.</p>
  </blockquote>
  
  <p>Has anyone run across this phenomenon?</p>
  
  <p>Based on what I'm seeing, it seems this hint should be ignored.</p>