CODEWITHSUNDEEP
sql-serverlocking
Abiete
Abiete

Reputation: 105

SQL Server locking Issue

I have a strange locking issue and need your help to understand what is going on.

Using a query, I join few tables (EventTransaction, Contact, Event and Outbound) in Prod_DW database and save the result into a table in CustomDataExtract database.

The query is something like this :

SELECT Col1, Col2, .... INTO CustomDataExtract..A1345_Contact_15022503
FROM Prod_DW..EventTransaction with (nolock) join ....

All of the tables in Prod_DW used in the query have with (nolock) option but still I see other connections being blocked on Prod_DW tables when I run the above query. My expectation is that since I query using with (nolock), during the query run, it shouldn't any blocking lock on the prod_DW tables. Can you please shed some light on this and tell me why still I see other connections being blocked.

Here is the Lock XML during the query run:

<Database name="CustomDataExtract">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="(null)">
      <Locks>
        <Lock resource_type="DATABASE.BULKOP_BACKUP_DB" request_mode="NULL" request_status="GRANT" request_count="1" />
        <Lock resource_type="DATABASE.BULKOP_BACKUP_LOG" request_mode="NULL" request_status="GRANT" request_count="1" />
        <Lock resource_type="DATABASE.ENCRYPTION_SCAN" request_mode="S" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="A1345_Contact_15022503" schema_name="dbo">
      <Locks>
        <Lock resource_type="ALLOCATION_UNIT.BULK_OPERATION_PAGE" request_mode="S" request_status="GRANT" request_count="1" />
        <Lock resource_type="HOBT.BULK_OPERATION" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="METADATA.INDEXSTATS" request_mode="Sch-S" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="BU" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>
<Database name="Prod_DW">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="Contact" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="9" />
      </Locks>
    </Object>
    <Object name="Event" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="EventTransaction" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="2" />
        <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="8" />
        <Lock resource_type="PAGE" page_type="*" index_name="ndx_EventTransaction" request_mode="S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="Outbound" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Upvotes: 0

Views: 653

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

Schema locks always take place even with nolock. Please read this article for more information on what happens during nolock.

http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

Upvotes: 1

Related Questions