title: What makes SQL Server decide what lock level to use (row, page or table lock?)
body: <p>SQL Server has many ways of <a href="https://stackoverflow.com/questions/9784172/what-are-row-page-and-table-locks-and-when-they-are-acquired">locking resource</a>. I am trying to understand what make SQL Server pick what level of locks it will choose. I want to know when will it use Page or table lock over row lock?</p>
  
  <p><strong>Problem</strong></p>
  
  <p>I have a PHP application that uses transaction with every http request to ensure all queries are executed before a commit. One issue that is puzzling me is when many (5+) people use the application the app seems to be hanging (spinning for a long periods of time)! Nothing I can think of will cause such a behaviors except for database locks! The scenario that I am thinking it happening is that SQL Server is choosing to pick Page or Table lock over rowlock for some reason. I am trying to ensure that SQL Server is doing a row lock not Page or table lock. I am using an ORM so I can't use ROWLOCK hint in my queries.</p>
  
  <p>Is there a way for me to run queries explain plan to see what lock level will be used?</p>