SQL Server Locks – A refresher on Locks : Part -2

Hi Friends,

I hope you enjoyed my first post on A refresher on Locks  Part 1 where I discussed on Shared SQL Server locks, Update locks and Exclusive locks. Today in Part 2, we will cover following lock types with simple examples under READ COMMITTED isolation level;

  1. Schema Locks (Sch)
  2. Intent Locks (I)
  3. Key-range locks

Schema Locks (Sch) : Schema locks are acquired on object levels in general to protect metadata. There are two types of schema locks;

  • SCH-S – Schema stability locks: These are a kind of shared locks and are acquired while compiling queries/generating execution plans. These locks are equivalent to shared locks on object definition. It will block DDL operations on the resource however won’t block normal processing including write operations.
  • SCM-M – Schema modification lock: These kinds of locks are obtained during DDL operations such as ALTER TABLE, DELETE TABLE. These locks are incompatible with any other locks like exclusive locks. It blocks access to the object data since structure is being modified.

I’ll create a table for demo and check status on sys.dm_tran_locks DMV, let’s go;

1_SQL_Server_A_refresher_on_Locks_Part2

2_SQL_Server_A_refresher_on_Locks_Part2

We can observe that Sch-S and Sch-M locks are acquired on system as well as tblTestSchemaLocks with many other locks on the system tables.

Intent Locks (I) : In simple words, intent locks notifies other transactions that it intends to lock data. By saying that in simple words it means that let’s say, I issue a SELECT statement inside any database SQL Server not only places shared locks on the row(s) that is being read but it will also place an Intent Shared (IS) lock on those pages and the object itself. These locks prevent any other sessions from modifying or dropping the object(s)/page(s) while session is reading that row in question. In my example below, you will observe that Intent Exclusive (IX) locks being placed on the page and the table where the key is to protect data from being locked by any other transactions.

3_SQL_Server_A_refresher_on_Locks_Part2

   

4_SQL_Server_A_refresher_on_Locks_Part2

Key-range locks : This lock is used in SERIALIZABLE isolation level to prevent phantom reads. It also prevents phantom insertions/deletions into a set of records accessed by a transaction. With this, SQL Server guarantees rows returned by SELECT statement during a transaction will be same irrespective of number of times they are the SELECT statement is run within that transaction. Let’s try this;

5_SQL_Server_A_refresher_on_Locks_Part2

6_SQL_Server_A_refresher_on_Locks_Part2

As you see, in my example RangeS-U is placed i.e. shared lock on the interval between the keys and update lock on the last key on the range.

I strongly recommend to read Lock compatibility matrix on MSDN to determine how locks are compatible to each other in SQL Server.

Not to forget, locking depends on transaction isolation levels so you will observe variations when you run them on different environments. I hope refresher Part 1 and Part 2 shed some light how locking operates on SQL Server and help to understand the basics.

 

Regards

Kanchan Bhattacharyya

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

Follow me on TwitterFollow me on FaceBook

   

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.