When working with SQL Server, it is important to have a good understanding of how transactions work. One concept that often causes confusion is schema locks. In this article, we will explore a simple example to help clarify this concept.
Recently, I was invited by a client to assist with interviews for their senior developers. One of the questions I asked was to create a scenario where a schema lock could be observed. Surprisingly, most candidates struggled to answer this question correctly. Let’s dive into the example I provided during the interview.
First, we need to create a situation where the schema is modified within a transaction. Consider the following code:
USE AdventureWorks GO BEGIN TRANSACTION GO CREATE PROCEDURE mySP AS SELECT 1 GO SELECT OBJECT_ID('mySP') ObjectID
In the above script, we create a stored procedure called “mySP” within a transaction. We then retrieve the object ID of the created stored procedure. Note that the object ID may vary in your execution.
Next, let’s run the following code in a separate query session:
USE AdventureWorks GO SELECT * FROM sys.procedures
When executing this query, you will notice that it never finishes running. This is because the stored procedure “mySP” is already listed in the sys.procedures view, but the transaction in the previous session has not been committed yet.
Now, if we try to retrieve the object name using the object ID obtained earlier, we will not get any results:
USE AdventureWorks GO SELECT OBJECT_NAME(1300199682)
Similarly, if we check the dynamic management views dm_tran_locks, we can confirm that a schema lock has been created:
USE AdventureWorks GO SELECT * FROM sys.dm_tran_locks
From the example above, we can clearly see that there is a Sch-M (schema modify) lock on our object ID. By specifying the object ID in the WHERE condition of the sys.dm_tran_locks view, we can retrieve specific information about the lock.
It is important to note that running DML (Data Manipulation Language) code within transactions creates schema modification locks until the transactions are completed. Once the COMMIT or ROLLBACK statement is executed, the queries in other sessions can proceed.
Understanding schema locks is crucial for optimizing database performance and avoiding conflicts in multi-user environments. By being aware of how transactions and locks work, developers can design more efficient and scalable database solutions.
Hopefully, this example has provided you with a better understanding of schema locks in SQL Server. If you have any questions or would like to share your own experiences, feel free to leave a comment below.