LCK_M_RIn_NL lock blocking inserts

  • Has anyone seen this specific lock contributing to a significant wait/blocking time? What could be the cause/solution?

    The description here is not very helpful: "Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL)."

    http://msdn.microsoft.com/en-us/library/ms179984.aspx

    Is it possible that multiple threads trying to insert records in the same table at the same time end up blocking each other with this lock?

    Thanks

  • you usually find more than one instance when locks are a problem - you should be able to find the object/key/page involved and drill in for more detail.

    If it's multiple threads you should be able to see them if you capture info at the point in time.

    Don't know if that helps or not?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • I'm experiencing the same problem, too. Still looking for a solution.

  • Answer to original poster is yes, LCK_M_RIn_NL probably is exactly that.

  • Try this purloined bit of SQL and see what your numbers look like.

    WITH Waits AS

    (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

    FROM sys.dm_os_wait_stats

    WHERE wait_type NOT IN ('BACKUPBUFFER','BACKUPIO','CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'

    ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'

    ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'

    ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'

    ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'--'LCK_M_IX'

    ))

    SELECT W1.wait_type,

    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

    FROM Waits AS W1

    INNER JOIN Waits AS W2

    ON W2.rn <= W1.rn

    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

    HAVING SUM(W2.pct) - W1.pct < 97.5;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply