title: Why lock in Testdatabase but not in Devdatabase
body: <p>I have just setup a testing clone of the database we use for development. However, when I run our application against this new database it creates a lock and in the end timeout waiting forever.</p>
  
  <p>The SQL that creates my problem: </p>
  
  <pre><code>-- In one connection it runs this
  set transaction isolation level read committed
  begin tran
  INSERT INTO [Test].[dbo].[Status] ([TransactionID], [Status]) VALUES (6122481, 1)
  </code></pre>
  
  <p>Then before that transaction is committed another connection tries to run:</p>
  
  <pre><code>set transaction isolation level read committed
  begin tran
  SELECT * FROM [Test].[dbo].[Status] with(rowlock) WHERE [Test].[dbo].[Status].[TransactionID] = 6122482 
  </code></pre>
  
  <p>However, this second transaction will never run as it waits for the first one to complete forever until the application timeouts. </p>
  
  <ol>
  <li>SQL Server 2008</li>
  <li>I have not much control over the actual SQL, we use LLBLGen (ORM) for it</li>
  <li>It works fine on our development and production databases. </li>
  <li>The test database was created by restore of a new backup of the dev database</li>
  <li>I think the major difference is the number of rows in the table (&lt;100 in test compared to > 100000 for dev)</li>
  </ol>
  
  <p>My guess is that SQL server does things differently when there's a lot of rows in the table. Question is how to fix the test database without adding lots and lots of data to it if thats the problem, or what else I should do?</p>
  
  <p>The table has some indexes, and also a FK on the transaction table on TransactionID:</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [idx1] ON [dbo].[Status] 
  (   [TransactionID] ASC,
      [Status] ASC
  ) INCLUDE ( [Created]) 
  
  CREATE NONCLUSTERED INDEX [idx2] ON [dbo].[Status] 
  (   [Status] ASC,
      [Created] ASC
  ) INCLUDE ( [TransactionID]) 
  
  ALTER TABLE [dbo].[Status] ADD  CONSTRAINT [PK_Statuses] PRIMARY KEY CLUSTERED 
  (   [StatusID] ASC
  )
  </code></pre>