SQL SERVER – TRANSACTION, DML and Schema Locks

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 (http://blog.SQLAuthority.com)

13 thoughts on “SQL SERVER – TRANSACTION, DML and Schema Locks

  1. When i placed “Truncate Table ” in place of “CREATE PROCEDURE mySP” in first session, it didn’t lock my schema ,whom the table belongs to.

    If Truncate Table is a DDL statement, why its behaving like this. But when i executed Drop Table or Create Table , schema locking occurring normally.

    Like

  2. Good article. This question could stump folks who have not paid attention to Schema locks, usually the tendency is to look for the regular locks and exclusive locks. Also this brings in the concept of understanding transactions.

    Like

    • Ramdas, could not agree with you more. There were time when I was not sure how schema locks worked. I took some time before I totally understand too.

      We all go through the phase where we learn.

      Kind Regards,
      Pinal

      Like

    • Thank you John for your kind words.

      I do follow your blog and you are very true, your Top 10 Junior DBA interview tips are good. There are few, which even Sr. DBA can get wrong too :)

      It is all about *real* experience and I am sure there are very few people like the same.

      Kind Regards,
      Pinal

      Like

  3. Pingback: SQL SERVER – Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31 « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – Locking, Blocking and Deadlock – Quiz – Puzzle – 9 of 31 « SQL Server Journey with SQL Authority

  5. Pinal,
    Can you help explain the following:

    SESSION 52:
    use tempdb
    go
    create table tableA (col1 int identity(1,1) not null primary key)
    go
    begin tran
    insert into tableA default values
    waitfor delay ‘1:00′
    rollback tran

    SESSION 53:
    USE TEMP
    GO
    alter table tableA add col2 int

    SESSION 55:
    insert into tableA default values
    /* insert is immediately successful */

    SESSION 58:
    EXEC SP_LOCK (omiited some data to make it viewable)
    spid ObjId Type Mode Status
    52 938157062 TAB IX GRANT
    52 938157062 PAG IX GRANT
    52 938157062 KEY X GRANT
    53 938157062 TAB Sch-M WAIT
    53 938157062 TAB Sch-M GRANT
    53 938157062 TAB Sch-M GRANT
    53 938157062 TAB Sch-M GRANT
    53 938157062 TAB Sch-M GRANT
    53 938157062 TAB Sch-M GRANT
    58 1131151075 TAB IS GRANT

    So, the inserts require a table level IX lock and aren’t blocked by the table level Sch-M lock.

    Let’s try this again with a DELETE.

    SESSION 52:

    USE tempdb
    GO
    drop table tableA
    GO
    create table tableA (col1 int identity(1,1) not null primary key)
    go
    insert into tableA default values
    insert into tableA default values
    insert into tableA default values
    GO

    SESSION 53:
    BEGIN TRAN
    DELETE TABLEA WHERE COL1=1
    WAITFOR DELAY ‘1:00′
    ROLLBACK TRAN

    SESSION 55:
    ALTER TABLE TABLEA ADD COL2 INT

    SESSION 57:
    DELETE TABLEA WHERE COL1=1
    /* NOW IT’S BLOCKED */

    SESSION 61:
    spid dbid ObjId Type Mode Status
    53 2 0 PAG IX GRANT
    53 2 1002157290 TAB IX GRANT
    53 2 0 KEY X GRANT
    54 7 0 DB S GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M WAIT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    55 2 1002157290 TAB Sch-M GRANT
    57 2 1002157290 TAB IX WAIT
    61 7 0 DB S GRANT
    61 1 1131151075 TAB IS GRANT
    62 7 0 DB S GRANT

    So, the insert was fine as we saw before but now with a DELETE.. it’s blocked… Note the wait for the TAB level, IX on session 57. Session 57 is blocked by 55, the alter table statement. But why didn’t this occur on the INSERT, in the previous example.

    In addition, the lock compatibility matrix (http://msdn.microsoft.com/en-us/library/ms186396.aspx) says that IX and SCH-M locks are not compatible. As a result, the ALTER should not have granted any SCH-M locks on the same table object.

    I tried this with UPDATES and the result is the same as with DELETES.

    Like

  6. Corrected some typos…

    Pinal,
    Can you help explain the following:

    SESSION 52:
    use tempdb
    go
    create table tableA (col1 int identity(1,1) not null primary key)
    go
    begin tran
    insert into tableA default values
    waitfor delay ‘1:00′
    rollback tran

    SESSION 53:
    USE TEMPDB
    GO
    alter table tableA add col2 int

    SESSION 55:
    insert into tableA default values
    /* insert is immediately successful */

    SESSION 58:
    EXEC SP_LOCK (omiited some data to make it viewable)
    spid ObjId Type Mode Status
    52 938157062 TAB IX GRANT
    52 938157062 PAG IX GRANT
    52 938157062 KEY X GRANT
    53 938157062 TAB Sch-M WAIT
    53 938157062 TAB Sch-M GRANT
    53 938157062 TAB Sch-M GRANT
    53 938157062 TAB Sch-M GRANT
    53 938157062 TAB Sch-M GRANT
    53 938157062 TAB Sch-M GRANT
    58 1131151075 TAB IS GRANT

    So, the inserts require a table level IX lock and aren’t blocked by the table level Sch-M lock.

    Let’s try this again with a DELETE.

    SESSION 52:

    USE tempdb
    GO
    drop table tableA
    GO
    create table tableA (col1 int identity(1,1) not null primary key)
    go
    insert into tableA default values
    insert into tableA default values
    insert into tableA default values
    GO

    SESSION 53:
    BEGIN TRAN
    DELETE TABLEA WHERE COL1=1
    WAITFOR DELAY ‘1:00′
    ROLLBACK TRAN

    SESSION 55:
    ALTER TABLE TABLEA ADD COL2 INT

    SESSION 57:
    DELETE TABLEA WHERE COL1=2
    /* NOW IT’S BLOCKED */

    SESSION 61:
    spid dbid ObjId Type Mode Status
    53 2 0 PAG IX GRANT
    53 2 1066157518 TAB IX GRANT
    53 2 0 KEY X GRANT
    53 2 1002157290 TAB IX GRANT
    54 7 0 DB S GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M WAIT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    55 2 1066157518 TAB Sch-M GRANT
    57 2 1066157518 TAB IX WAIT
    61 7 0 DB S GRANT
    61 1 1131151075 TAB IS GRANT
    62 7 0 DB S GRANT

    So, the insert was fine as we saw before but now with a DELETE.. it’s blocked… Note the wait for the TAB level, IX on session 57. Session 57 is blocked by 55, the alter table statement. But why didn’t this occur on the INSERT, in the previous example.

    In addition, the lock compatibility matrix (http://msdn.microsoft.com/en-us/library/ms186396.aspx) says that IX and SCH-M locks are not compatible. As a result, the ALTER should not have granted any SCH-M locks on the same table object.

    I tried this with UPDATES and the result is the same as with DELETES.

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s