THREE COMMON LOCKING MODES V2K
SQL Server uses an extensive locking mechanism that is quite complex. 
The three most common locking modes that SQL uses to manage 
consistency and concurrency are the Exclusive, Shared, and Update 
locks.

EXCLUSIVE LOCKS 
Exclusive locks are exactly what the term implies. A process obtains an 
exclusive lock when it needs to modify data, i.e. insert, update, or 
delete. These locks are held until the transaction is committed or 
rolled back. No other process can access the data unless it uses a 
query hint.

SHARED LOCKS 
Shared locks can occur at the table, index, page, or row level. These 
types are acquired by a process that reads data. Any number of 
processes can acquire shared locks on the same data. No exclusive lock 
can occur if a shared lock is currently accessing data. The exception 
to this is the process that has the shared lock can escalate to 
exclusive.

UPDATE LOCKS 
A cross between the Shared and Exclusive types, the Update 
lock occurs when a data modification action is issued and the 
SQL Server must first locate the data to be modified. This is 
best thought of as a precursor to the eventual Exclusive lock 
that needs modification.

There are other, more complex locking modes as well as differing levels 
of lock granularity. By setting certain transaction isolation levels, 
you can affect behaviors of the processes that interact with the 
locking mechanism and its modes.

Check out Microsoft Press Inside SQL Server 2000 for more information 
on locking modes.

-------------------------------------------