In SQL Server 2000, if you execute a query affecting a row, then that
particular row will be locked so that no other query can access the
same row till the transaction completes. The other rows are available
to update queries. This is row-level locking. Similarly, if the update
query will be affecting multiple rows, SQL Server applies a lock
depending upon the memory available. If these rows are present in
contiguous pages, then SQL Server might lock those pages, extents or
even the entire table. If pages are locked, then the rest of the rows
in other data pages can be accessed. If the entire table is locked,
then the other simultaneous queries will have to wait until the
transaction is completed. So, as you can see SQL Server chooses the
type of locking for each transaction-it all depends on the memory
availability. If an update query is being executed, the select query
will have to wait till the update transaction completes.
However, SQL does give us some kind of control over the type of
locking. You can still specify a ROWLOCK in an update query. Similary
a select query applies a read lock on the tables. However, you can
specify not to let this happen by using WITH NOLOCK in your select
queries.
This is not the end of story-there is something called Transaction
Isolation Levels. Refer to the SQL Server Books online and you can read
in detail about them.
There is a Microsoft fix to the problem when SQL Server applies a lock
when an Update query is fired and a Select query is fired at the same
time. As per this fix, you experience this problem when you run a
SELECT statement that uses the TOP clause and the READPAST locking
hint.
Refer to this:
http://support.microsoft.com/?kbid=867746
HTH.
Sankalp