none
LCK_M_S blocking

    Question

  • Hi,

    I have 2 tables that are partitioned (they do NOT share the same partition scheme and function). There are 2 processes running (simplified for better understanding):

    Process 1:

    begin tran

    slide out partition 3 from table1

    drop indexes from table1_stage

    load data into table1_stage

    create indexes on table1_stage

    slide table1_stage into partition 3 of table1

    commit tran

     

    Process 2:

    begin tran

    slide out partition 3 from table2

    drop indexes from table2_stage

    load data into table2_stage

    create indexes on table2_stage

    slide table2_stage into partition 3 of table2

    commit tran

     

    So all seems nice. However, when we run them in paralel, whatever runs first blocks the other! Why the heck is that? They are not touching same tables, not same indexes, not same partitioning schemes, not same partitioning functions!*&%*%!! (me being upset)

    Waiting type is LCK_M_S, and resource description is:

    keylock hobtid

    =281474978938880 dbid=9 id=lock3d049e00 mode=X associatedObjectId=281474978938880

    associatedObjectId=281474978938880 DOES NOT exist in database with id=9.

    Thanks in advance for your help!

    Ed


    Pedja
    Thursday, December 01, 2011 8:53 PM

All replies

  • This value should be an allocation unit id so search for that to get the object name.

    Is there any thing in common between the 2 tables: i.e. FK TR? or a procedure that accesses both tables?

     

    Also see this link explaining about schema locks: http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx

    Schema Locks

    Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed.

    Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.

     

     

    SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SELECT 
         object_name(i.object_id) AS table_name
        ,i.name AS index_name
        ,i.index_id
        ,i.type_desc as index_type
        ,partition_id
        ,partition_number AS pnum
        ,[rows]
        ,allocation_unit_id AS au_id
        ,a.type_desc AS page_type_desc
        ,total_pages AS pages
    FROM sys.indexes i
    INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    -- INNER JOIN sys.system_internals_allocation_units a ON p.partition_id = a.container_id -- undocumented DMV
    WHERE allocation_unit_id = 281474978938880
    --AND i.object_id=object_id('dbo.MD_Caplet_CollarStrangle');
    



    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    Thursday, December 01, 2011 10:31 PM
  • First of all, thanks for your reply.

    Things that are in common are database, filegroup (PRIMARY), and stored procedures that do all these steps of processes 1/2. For example, same stored procedure to slide out partition, same stored procedure to drop staging table indexes, etc.. Of course, to achieve it, they had to be based on dynamic sql (not sure if it is important for this issue). Everything else, partition functions/schemes, are different.

    When I ran your sql, I got this:

    table_name index_name index_id index_type  partition_id              pnum rows au_id page_type_desc pages
    sysschobjs   clst              1             CLUSTERED 281474978938880 1        1287 281474978938880 IN_ROW_DATA 33

     

    So blocking on metadata??? But what? Also, when I ran the same 2 processes this morning, NO BLOCKING!!! Scary (I don't dare to go into production with this). Any idea?

     

    Thanks again,


    Pedja
    Friday, December 02, 2011 2:50 PM
  • Hello,

    Are you sure there is no foreign key relationship between the two tables?  If so, is casade delete or insert enabled?  To verify the relationship for inserts and deletes, it may be necessary to SELECT from the other to make sure we are enforcing the relationship.

    I would also suggest capturing the event with Profiler and include the execution plan.  This may help with determining why they have a common resource that the other is waiting on.

    You would definitely want to test with load before going to production. 

    Hope this helps.

    Sincerely,

    Rob Beene, MSFT

    Monday, December 12, 2011 4:46 PM