There are more than 20 different lock types in SQL Server. As the first step we will look at the major ones and understand when and how SQL Server uses them.
When session inserts, updates, or deletes the row SQL Server acquires exclusive (X) lock on that row and holds it until the end of the transaction. As you can guess by the name—exclusive means exclusive—only one session can hold exclusive (X) lock on the resource at any given point of time. This would protect the object (row, for example) from being modified by other sessions. I would like to repeat one of the key points here: exclusive (X) locks are held until the ...