title: Lock aquisition and transaction order
body: <p>In SQL Server, what is the lock acquisition order? How is it decided which transaction is run first?</p>
  <p>I know that this is a wide topic, so link to relevant material would be useful.</p>
  <p>My experiment (code below) shows, that transaction-2 (lock all rows), waits also for transaction-3, which started later.</p>
  <p>Originally, I thought, that there is some kind of FIFO principal for which transactions gets to run.<br />
  But now it seems that transactions locking many rows, could wait for very very long time.<br />
  (or maybe page/table locks act differently)</p>
  <pre><code>-- I ran 3 transactions in parallel
  
  -- transaction1 - lock ONE row
  BEGIN TRAN PRINT SYSUTCDATETIME();
  SELECT * FROM mytable WITH (UPDLOCK, ROWLOCK) WHERE id = 58;
  PRINT SYSUTCDATETIME(); WAITFOR DELAY '00:00:05'; PRINT SYSUTCDATETIME();
  COMMIT TRAN
  
  -- transaction2 - lock ALL rows
  BEGIN TRAN
  PRINT SYSUTCDATETIME();
  SELECT * FROM mytable WITH (UPDLOCK, ROWLOCK)
   PRINT SYSUTCDATETIME(); WAITFOR DELAY '00:00:05'; PRINT SYSUTCDATETIME();
  COMMIT TRAN
  
  -- transaction3 - lock ONE row
  BEGIN TRAN
  PRINT SYSUTCDATETIME();
  SELECT * FROM mytable WITH (UPDLOCK, ROWLOCK) WHERE id = 201;
  PRINT SYSUTCDATETIME(); WAITFOR DELAY '00:00:05'; PRINT SYSUTCDATETIME();
  COMMIT TRAN
  
  
  -- Results were:
  
  -- transaction1
  2022-04-22 13:16:49.0830498
  2022-04-22 13:16:49.0830498
  2022-04-22 13:16:54.0941580
  
  -- transaction2
  2022-04-22 13:16:49.6759131
  2022-04-22 13:16:55.5587416
  2022-04-22 13:17:00.5708426
  
  -- transaction3
  2022-04-22 13:16:50.5450067
  2022-04-22 13:16:50.5450067
  2022-04-22 13:16:55.5587416
  </code></pre>