Translated into the SQL Server language, this means that when a transaction imposes the lock on an object, all other transactions that require the access to that object will be forced to wait until the lock is released and that wait will be registered with the adequate wait type SQL Server locks can be specified via the lock modes and lock granularity Lock mode considers various lock types that can be applied to a resource that has to be locked: for the transaction that imposed the shared lock, as long as the transaction holds the lock.
The exclusive lock will be imposed by the transaction when it wants to modify the page or row data, which is in the case of DML statements DELETE, INSERT and UPDATE.
Once the transaction that holds the update lock is ready to change the data, the update lock (U) will be transformed to an exclusive lock (X).
It is important to understand that update lock is asymmetrical in regards of shared locks.
More details about this is available in Guidelines for Optimizing Bulk Import SQL Server has introduced the locking hierarchy that is applied when reading or changing of data is performed.
The lock hierarchy starts with the database at the highest hierarchy level and down via table and page to the row at the lowest level Essentially, there is always a shared lock on the database level that is imposed whenever a transaction is connected to a database.
An update lock can be imposed on a record that already has a shared lock.
In such a case, the update lock will impose another shared lock on the target row.
Without escalation, locks could require a significant amount of memory resources.
Let’s take an example where a lock should be imposed on the 30,000 rows of data, where each row is 500 bytes in size, to perform the delete operation.
While objects are locked, SQL Server will prevent other transactions from making any change of data stored in objects affected by the imposed lock.
Once the lock is released by committing the changes or by rolling back changes to initial state, other transactions will be allowed to make required data changes.
After the transaction acquires an SIU lock on the table, the query with the PAGELOCK hint will acquire the shared (S) lock while the update query will acquire intent update (IU) lock Update with intent exclusive (UIX) – when update lock (U) and intent exclusive (IX) locks are acquired at lower hierarchy resources in the table simultaneously, the update with intent exclusive lock will be acquired at the table level as a consequence Schema locks (Sch) – The SQL Server database engine recognizes two types of the schema locks: Schema modification lock (Sch-M) and Schema stability lock (Sch-S) Bulk Update locks (BU) – this lock is designed to be used by bulk import operations when issued with a TABLOCK argument/hint.