Depending on the transaction level there are six types of locks that can be acquired on data and these are briefly explained below:
1. Shared Locks (S): This lock is useful when you are doing some read operations and no manipulations like write operations (update/delete/insert).
2. Intent Locks: Also knoen as Demand Lock, SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated.
There are basically three types of Intent Locks that are most popular:
a) Intent Shared Lock(IS)
b) Intent exclusive (IX)
c) Shared with intent exclusive (SIX)
3.Exclusive Lock (X): These locks are big possessive types. They will not work if any other locks are already there.This lock used for data-modification operations, such as INSERT, UPDATE or DELETE.
4. Update Locks (U): They are acquired just prior to modifying the data. If a transaction modifies a row, then the update lock is escalated to an exclusive lock; otherwise, it is converted to a shared lock.
5. Bulk Update Locks (BU): This lock is useful while performing BULK operation on the TABLE like BULK INSERT.
6. Schema stability locks (Sch-S): They are acquired when store procedures are being compiled.