This week I posted two articles on Refresher on SQL Server Locks, Part1 and Part2 however realized that conversion lock was left out on both the articles which I should cover immediately part of another post.
In simple words, Conversion locks are resultant of converting one lock type to another. We can have three types of conversion locks and are listed below with short explanation;
- Shared with Intent Update (SIU) : A transaction which holds a Shared lock also has some pages or rows locked with an Update lock.
- Shared with Intent Exclusive (SIX) : A transaction which holds a Shared lock also has some pages or rows locked with an Exclusive lock.
- Update with Intent Exclusive (UIX) : A transaction which holds an Update lock also has some pages or rows locked with an Exclusive lock.
Let us check this with an example where you will observe Update with Intent Exclusive (UIX) is applied on the page;
Locking in SQL Server closely related to proper application and database design/operation; we cannot discount database design and application behavior as they are a major contributor to overall functionality.