SQL Server locks resources by using different locking modes that determine how the resources can be accessed by concurrent transactions. SQL Server uses several resource locking modes:
Shared locks allow concurrent transactions to read a resource. No other transactions can modify the data while shared locks exist on the resource. Shared locks are released as soon as the data on a resource has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared locks for the duration of the transaction.
Update locks are used on resources that can be updated. Update locks prevent a common form of deadlock. If two transactions acquire shared locks on a resource and then attempt to update data concurrently, an attempted conversion to exclusive locks occurs. Both transactions are waiting for the other to release its shared lock, and a deadlock occurs. Update locks eliminate this problem, because only one transaction at a time can obtain an update lock to a resource.
Exclusive locks are used for data-modification operations, such as updates, inserts, or deletes. Exclusive locks ensure that multiple updates cannot be made to the same resource at the same time (concurrent transactions). No other transactions can read or modify data locked with an exclusive lock.
Intent locks indicate that SQL Server is attempting to acquire a shared or exclusive lock on some of the resources lower in the hierarchy. Intent locks improve performance because SQL Server must examine intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the need to examine every row or page lock on the table to determine if a transaction can lock the entire table.
There are two types of schema locks: Sch-M and Sch-S. Sch-M locks are taken when a table data definition language (DDL) operation is being performed (such as adding a column or dropping a table). Sch-S locks are taken while compiling queries. Sch-S locks do not block out any transactional locks, including exclusive locks. Therefore, other transactions can run while a query is being compiled, even transactions with exclusive locks on a table. However, DDL operations cannot be performed on the table.