SQL Server Locks – What are Conversion Locks?

Hi Friends,

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;

 

  1. Shared with Intent Update (SIU) : A transaction which holds a Shared lock also has some pages or rows locked with an Update lock.
  2. Shared with Intent Exclusive (SIX) : A transaction which holds a Shared lock also has some pages or rows locked with an Exclusive lock.
  3. 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;

1_SQL_Server_What_are_Conversion_Locks

2_SQL_Server_What_are_Conversion_Locks

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.

 

Regards

Kanchan Bhattacharyya

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *