title: Explain locking behavior in SQL Server
body: <p>Why is that, with default settings on Sql Server (so transaction isolation level = read committed), that this test:</p>
  
  <pre><code>CREATE TABLE test2 (
  ID bigint,
  name varchar(20)
  )
  </code></pre>
  
  <p>then run this in one SSMS tab:</p>
  
  <pre><code>begin transaction SH
  insert into test2(ID,name) values(1,'11') 
  waitfor delay '00:00:30'
  commit transaction SH
  </code></pre>
  
  <p>and this one simultaneously in another tab:</p>
  
  <pre><code>select * from test2
  </code></pre>
  
  <p>requires the 2nd select to wait for the first to complete before returning??</p>
  
  <p>We also tried these for the 2nd query:</p>
  
  <pre><code>select * from test2 NOLOCK WHERE ID = 1
  </code></pre>
  
  <p>and tried inserting one ID in the first query and selecting a different ID in the second.</p>
  
  <p>Is this the result of page locking? When running the 2 queries, i've also ran this:</p>
  
  <pre><code>select object_name(P.object_id) as TableName, resource_type, resource_description
  from
  sys.dm_tran_locks L join sys.partitions P on L.resource_associated_entity_id = p.hobt_id
  </code></pre>
  
  <p>and gotten this result set:</p>
  
  <blockquote>
    <p>test2  RID 1:12186:5<br>
    test2  RID 1:12186:5<br>
    test2  PAGE    1:12186<br>
    test2  PAGE    1:12186                                                                                                                                                                                                                                                          </p>
  </blockquote>