Shared Locks (S)

• A shared lock holds data for a single user to read. Others users cannot modify data that is held under a shared lock, but they are able to read the data. Once the data has been read, the lock is generally relinquished, with some exceptions such as if the transaction is run using the READCOMMITTED or READCOMMITTEDLOCK locking isolation level. Shared locks are used in read-only operations only, and cannot be used to modify the data.
  
  

Exclusive Locks (X)

• An exclusive lock is used to prevent a section of data that is being modified by a transaction from being modified by another transaction. By default, data under an exclusive lock cannot be read by anyone other than the locker, however this setting can be changed by using the NOLOCK hint or a READUNCOMMITTED isolation level. This type of lock would be used with operations such as INSERT, UPDATE and DELETE.
  
  

Update Locks (U)

• An update lock sits in the middle-ground between a shared lock and an exclusive lock. If some data has an exclusive lock on it, no other lock can be placed on it. An update lock can be placed on a lock which already has a shared lock on it; and when it comes time to make updates to the data, the update lock changes into an exclusive lock. Only one update lock can be placed onto a resource at any given time.
  
  

Intent Locks (I)

• An intent lock allows a transaction to notify another transaction of its intent to lock the data -- hence the name "Intent lock." They work preventing transactions from locking another transactions from locking a higher part of the database. For example, placing an intent lock on the table prevents another transaction from placing an exclusive lock the table, which would cancel a lock on a lower part such as a row or a page.
  
  

Schema Locks (Sch) and Bulk Update Locks (BU)

• Schema locks are used with an operation that requires the schema of the table is running. There are two kinds -- a schema stability lock (Sch-S), which does not block access to the object data, and a schema modification lock, which does block access to the object data. A Bulk update lock, as the name implies, is used by bulk operations such as bulk-copying data, and when the TABLOCK hint has been specified.