Between the shared and exclusive locks, there is the update lock (U), which can be thought of as an in-between mode between shared and exclusive locks.The main purpose of the update lock is to prevent deadlocks where multiple users simultaneously try to update data.Intent locks are used to indicate that a certain lock will be later placed on a certain database object.
At the other end of the spectrum, the shared locks (S) is put to a database object whenever it is being read (using the SELECT statement), depending on the selected transaction isolation mode.
Key-range locks are used with the SERIALIZABLE transaction isolation level to prevent phantom reads, for instance.
In addition to singular locking modes, certain locking modes can be combined with one another.
This means that locks can be placed on different levels, depending on the situation.
Intent locks prevent the potential situation where a newly-acquired lock might invalidate locks on a lower level in the hierarchy.
Finally, schema locks (SCH-M and SCH-S) are used to prevent changes to object structure, bulk update locks (BU) are used when updating or inserting multiple rows using a bulk update, and key-range locks (R) are used to lock ranges of keys in an index.