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.

Thanks in advance.

migurus (migurus (AT) yahoo (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx