Today we will be going over a simple but interesting concept. Many a time, I have come across the lack of understanding on how the transactions work in SQL Server. Today we will go over a small but interesting observation. One of my clients had recently invited me to help them out with an interview for their senior developers. I had interviewed nearly 50+ candidates in a single day. There were many different questions, but the following question was incorrectly answered most of the time.
The question was to create a scenario where you can see the SCHEMA LOCK. The interview panel initially thought that this might be a very easy question for this particular interview. I, however, insisted them to keep this question for time being and then remove it from the list of interview questions only when at least 10 candidates got it right. Contrary to our expectations, we never reached a point where we had to remove this question from the list!
Let us see a simple example regarding how to create a schema lock. The answer I was looking for is as follows: create a situation where the Schema is modified in the transaction and check the status of the object or session before the transactions are committed or rolled back.
Run the following code in Query Session 1:
USE AdventureWorks
GO
BEGIN TRANSACTION
GO
CREATE PROCEDURE mySP
AS
SELECT 1
GO
SELECT OBJECT_ID('mySP') ObjectID
GO
The above script will give us the objectID of the created stored procedure. In this case, the received ObjectID is 1300199682; this can be different for your execution.
Run the following code in Query Session 2:
USE AdventureWorks
GO
SELECT *
FROM sys.procedures
GO
This query will never finish running as in Session 1, where we have created the Stored Procedure. The name is already listed in the sys.procedures, but the transactions in Session1 are not yet committed.
If you run the following code, it will also not return any results even though we have received the ObjectID in Session 1.
USE AdventureWorks
GO
SELECT OBJECT_NAME(1300199682)
GO
Run the following code in Query Session 3:
Now to confirm that a schema lock is created, we can check the dynamic management views dm_tran_locks.
USE AdventureWorks
GO
SELECT *
FROM sys.dm_tran_locks
GO
We can clearly see from the example that there is a Sch-M (schema modify) lock over our ObjectID.
You can specify the where condition to this DMV as we are know the ObjectID here.
USE AdventureWorks
GO
SELECT request_type, request_mode, resource_associated_entity_id, request_type
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id = 1300199682
GO
From above example, it is very clear that running DML code in the transactions create a schema modification lock until the transactions are over.
If you run the COMMIT or ROLLBACK statement in Session 1, the Queries in Session 2 will complete right away.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
