352df2e6   
   From: esquel@sommarskog.se   
      
   migurus (migurus@yahoo.com) writes:   
   > SQL 2005, my connection is under default READ COMMITED level.   
   >   
   > I'd like to know whether locks are placed atomically when I say:   
   >   
   > select T1.Col1, T2.Col2 from TBL1 T1, TBL2 T2 where T1.Key=T2.Key   
   > etc...   
   >   
   > In other words, does db acquire locks for resources T1 and T2   
   > atomically (all or none), or there is a possibility that it would   
   > acquire lock on T1 resource and wait for T2 resource to become   
   > available.   
      
   First of all, SQL Server can lock on lower level than on the table level; by   
   default locks are on row level, but SQL Server can escalate to higher levels   
   if needed.   
      
   Locks are acquired and released as needed. That is, if you run:   
      
      SELECT * FROM tbl WHERE OrderID > 1000000 ORDER BY OrderID   
      
   and there is a clustered index on OrderID, SQL Server will lock the rows as   
   it reads them, and once a row has been passed to the client, the lock will   
   be released, even if SQL Server is still reading more rows. If towards the   
   end there is a row which is locked, the SELECT process will stall.   
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Books Online for SQL Server 2005 at   
   http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx   
   Books Online for SQL Server 2000 at   
   http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)