How to tell who has a table locked up

Reply

lovejazz Posted: 10-28-2008 16:44

rated by 0 users

One of our users is trying to do a consumption Journal and she say's Locked out by another user, please try again later. She's been trying for over a 1/2 hour and keeps getting the same message. Is there anyway to tell who is locking her out???

Thanks in advance,

Greg

Reply

Jörg A. Stryk replied on 10-29-2008 10:51

rated by 0 users

Hi! 

Which Platform do you use? C/SIDE or SQL Server?

With SQL I would like to refer to my BLOG here: http://dynamicsuser.net/blogs/stryk/archive/2008/05/12/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

WIth "native" NAV you could check out the "Perfromance Troubleshootig Tools", specifically the "Session Monitor" feature ...

Regards,

Jörg

Reply

lovejazz replied on 10-29-2008 15:30

rated by 0 users

Thanks. We use the SQL version 5.1.

 I'll check your blog out now..

Greg

Reply

Jörg A. Stryk replied on 10-29-2008 16:36

rated by 0 users

I have to admit that the code example on my BLOG are somewhat buggy (it's a version somewhere between SQL 2000 and 2005 compatibility);

I actually have updated version available, but have not updated my BLOG so far; please find here the BETA version of the new stuff (SQL Server 2005 only):

The new Table:

create table [dbo].[ssi_BlockLog]
 (
  [entry_no] bigint identity constraint [ssi_BlockLog$pk_ci] primary key clustered,
  [timestamp] datetime,
  [db] varchar(128) collate database_default,
  [waitresource] varchar(128),
  [table_name] varchar(128) collate database_default,
  [index_name] varchar(128) collate database_default,
  [waittime] bigint,
  [lastwaittype] varchar(128),
  [spid] int,
  [loginame] varchar(128) collate database_default,
  [hostname] varchar(128) collate database_default,
  [program_name] varchar(128) collate database_default,
  [cmd] nvarchar(max) collate database_default,
  [status] varchar(128) collate database_default,
  [cpu] bigint,
  [lock_timeout] int,
  [blocked by] int,
  [loginame 2] varchar(128) collate database_default,
  [hostname 2] varchar(128) collate database_default,
  [program_name 2] varchar(128) collate database_default,
  [cmd 2] nvarchar(max) collate database_default,
  [status 2] varchar(128) collate database_default,
  [cpu 2] bigint,
  [block_orig_id] int,
  [block_orig_loginame] varchar(128) collate database_default
  )
go

The new Procedure:

create procedure dbo.ssi_blockdetection
  @mode varchar(10) = 'loop',         -- "loop" or "once"
  @threshold int = 1000,              -- Block threshold in milliseconds
  @frequency int = 3,                 -- Check frequency in milliseconds
  @save tinyint = 0                   -- save output to table ssi_BlockLog (0 = no, 1 = yes)
with encryption
as

if @mode <> 'once' begin
  print '*********************************************************'
  print '***              STRYK System Improvement             ***'
  print '***    Performance Optimization & Troubleshooting     ***'
  print '***  (c) 2008, STRYK System Improvement, Jörg Stryk   ***'
  print '***                   www.stryk.info                  ***'
  print '*********************************************************'
  print '              Version 4.00, Date: 24.10.2008             '
  print ''
end

if (@mode not in ('loop', 'once')) begin
  raiserror ('ERROR: Invalid Parameter @mode: %s', 15, 1, @mode)
  return
end
if (@threshold < 1) begin
  raiserror ('ERROR: Invalid Parameter @threshold: %i', 15, 1, @threshold)
  return
end
if (@frequency < 1) begin
  raiserror ('ERROR: Invalid Parameter @frequency: %i', 15, 1, @frequency)
  return
end
if (@save not in (0,1)) begin
  raiserror ('ERRor: Invalid Parameter @save: %i', 15, 1, @save)
  return
end

if @mode <> 'once' begin
  print 'Block Detection Mode  : ' + @mode
  print 'Block Threshold (msec): ' + convert(varchar(15), @threshold)
  print 'Check Frequency (sec) : ' + convert(varchar(10), @frequency)
  print 'Save Output to table  : ' + convert(varchar(10), @save)
  print ''
  print 'Searching for blocked processes ...'
  print ''
end

set nocount on
set statistics io off
declare @spid int, @spid2 int, @loginame varchar(128), @blocked_by int, @blocked_by_name varchar(128), @orig_id int, @orig_name varchar(128), @timestmp datetime, @i int

if @mode = 'once'
  goto start_check

while 1 = 1 begin

  start_check:

  if exists (select * from sys.dm_exec_requests where [blocking_session_id] <> 0) begin
    print 'Checkpoint ' + convert(varchar(30), getdate())
      
    if @save = 0 begin

     select distinct
             [db] = db_name(s1.[database_id]),
             [waitresource] = ltrim(rtrim(s1.[wait_resource])),
             [table_name] = object_name(sl.rsc_objid),           
             [index_name] = si.[name],
             s1.[wait_time],
             s1.[last_wait_type],
             s1.[session_id],
             session1.[login_name],
             session1.[host_name],
             session1.[program_name],
             [cmd] = isnull(st1.[text], ''),
             session1.[status],
             session1.[cpu_time],
             s1.[lock_timeout],
             [blocked by] = s1.[blocking_session_id],            
             [login_name 2] = session2.[login_name],
             [hostname 2] = session2.[host_name],
             [program_name 2] = session2.[program_name],
             [cmd 2] = isnull(st2.[text], ''),
             session2.[status],
             session2.[cpu_time]         
       -- Process Requests
       from sys.dm_exec_requests (nolock) s1
       left join sys.dm_exec_requests (nolock) s2 on s2.[session_id] = s1.[blocking_session_id]
       -- Sessions
       left join sys.dm_exec_sessions (nolock) session1 on session1.[session_id] = s1.[session_id]
       left join sys.dm_exec_sessions (nolock) session2 on session2.[session_id] = s1.[blocking_session_id]
       -- Connections
       left join sys.dm_exec_connections (nolock) sc1 on sc1.[session_id] = s1.[session_id]
       cross apply sys.dm_exec_sql_text(sc1.most_recent_sql_handle) st1
       left join sys.dm_exec_connections (nolock) sc2 on sc2.[session_id] = s2.[session_id]
       cross apply sys.dm_exec_sql_text(sc2.most_recent_sql_handle) st2
       -- Lock-Info
       left outer join  master.dbo.syslockinfo (nolock) sl on s1.[session_id] = sl.req_spid
       -- Indexes
       left outer join sys.indexes (nolock) si on sl.rsc_objid = si.[object_id] and sl.rsc_indid = si.[index_id]
       where s1.[blocking_session_id] <> 0
             and (sl.rsc_type in (4,5,6,7)) and sl.rsc_indid <> 0 and sl.req_status = 3
             and s1.[wait_time] >= @threshold

    end else begin

      set @timestmp = getdate()

      insert into [ssi_BlockLog]
      select distinct
             @timestmp,
             [db] = db_name(s1.[database_id]),
             [waitresource] = ltrim(rtrim(s1.[wait_resource])),
             [table_name] = object_name(sl.rsc_objid),           
             [index_name] = si.[name],
             s1.[wait_time],
             s1.[last_wait_type],
             s1.[session_id],
             session1.[login_name],
             session1.[host_name],
             session1.[program_name],
             [cmd] = isnull(st1.[text], ''),
             session1.[status],
             session1.[cpu_time],
             s1.[lock_timeout],
             [blocked by] = s1.[blocking_session_id],            
             [login_name 2] = session2.[login_name],
             [hostname 2] = session2.[host_name],
             [program_name 2] = session2.[program_name],
             [cmd 2] = isnull(st2.[text], ''),
             session2.[status],
             session2.[cpu_time],
             [block_orig_id] = null,
             [block_orig_id] = null
       -- Process Requests
       from sys.dm_exec_requests (nolock) s1
       left join sys.dm_exec_requests (nolock) s2 on s2.[session_id] = s1.[blocking_session_id]
       -- Sessions
       left join sys.dm_exec_sessions (nolock) session1 on session1.[session_id] = s1.[session_id]
       left join sys.dm_exec_sessions (nolock) session2 on session2.[session_id] = s1.[blocking_session_id]
       -- Connections
       left join sys.dm_exec_connections (nolock) sc1 on sc1.[session_id] = s1.[session_id]
       cross apply sys.dm_exec_sql_text(sc1.most_recent_sql_handle) st1
       left join sys.dm_exec_connections (nolock) sc2 on sc2.[session_id] = s2.[session_id]
       cross apply sys.dm_exec_sql_text(sc2.most_recent_sql_handle) st2  
       -- Lock-Info
       left outer join  master.dbo.syslockinfo (nolock) sl on s1.[session_id] = sl.req_spid
       -- Indexes
       left outer join sys.indexes (nolock) si on sl.rsc_objid = si.[object_id] and sl.rsc_indid = si.[index_id]
       where s1.[blocking_session_id] <> 0
             and (sl.rsc_type in (4,5,6,7))  and sl.rsc_indid <> 0 and sl.req_status = 3
             and s1.[wait_time] >= @threshold
   
      update [dbo].[ssi_BlockLog] set [table_name] = '- unknown -' where [table_name] is null

      -- get block originator
      declare originator_cur cursor for select [blocked by], [loginame 2]
        from [dbo].[ssi_BlockLog]
        where [timestamp] = @timestmp
        for update
      open originator_cur
      fetch next from originator_cur into @blocked_by, @blocked_by_name
      while @@fetch_status = 0 begin
        set @i = 0
        set @orig_id = @blocked_by  
        set @orig_name = @blocked_by_name
        set @spid2 = @blocked_by
        while (@spid2 <> 0) and (@i < 100) begin
          if exists(select top 1 [blocked by] from [dbo].[ssi_BlockLog] where ([timestamp] = @timestmp) and ([spid] = @spid2)) begin
            select top 1 @spid = [blocked by], @loginame = [loginame 2] from [dbo].[ssi_BlockLog] where ([timestamp] = @timestmp) and ([spid] = @spid2)
            set @orig_id = @spid
            set @orig_name = @loginame                      
            set @spid2 = @spid        
          end else
            set @spid2 = 0
          set @i = @i + 1   -- "Emergency Exit", to avoid recursive loop
        end
        update [dbo].[ssi_BlockLog] set [block_orig_id] = @orig_id, [block_orig_loginame] = @orig_name where current of originator_cur
        fetch next from originator_cur into @blocked_by, @blocked_by_name
      end
      close originator_cur
      deallocate originator_cur

    end
  end

  end_check:

  if @mode = 'once'
    return

  waitfor delay @frequency
end

The Alert is the same; the Job step should be

exec ssi_blockdetection
  @mode = 'once'
 ,@threshold = 1
 ,@frequency = 1
 ,@save = 1

Reply

lovejazz replied on 10-29-2008 22:04

rated by 0 users

Thanks Jorg...

Reply

Jörg A. Stryk replied on 11-03-2008 11:04

rated by 0 users

Correction of "Procedure", better using all OUTER JOIN and OUTER APPLY (sorry about the inconvenience):

create procedure dbo.ssi_blockdetection
  @mode varchar(10) = 'loop',         -- "loop" or "once"
  @threshold int = 1000,              -- Block threshold in milliseconds
  @frequency int = 3,                 -- Check frequency in milliseconds
  @save tinyint = 0                   -- save output to table ssi_BlockLog (0 = no, 1 = yes)
with encryption
as

if @mode <> 'once' begin
  print '*********************************************************'
  print '***              STRYK System Improvement             ***'
  print '***    Performance Optimization & Troubleshooting     ***'
  print '***  (c) 2008, STRYK System Improvement, Jörg Stryk   ***'
  print '***                   www.stryk.info                  ***'
  print '*********************************************************'
  print '              Version 4.00, Date: 24.10.2008             '
  print ''
end

if (@mode not in ('loop', 'once')) begin
  raiserror ('ERROR: Invalid Parameter @mode: %s', 15, 1, @mode)
  return
end
if (@threshold < 1) begin
  raiserror ('ERROR: Invalid Parameter @threshold: %i', 15, 1, @threshold)
  return
end
if (@frequency < 1) begin
  raiserror ('ERROR: Invalid Parameter @frequency: %i', 15, 1, @frequency)
  return
end
if (@save not in (0,1)) begin
  raiserror ('ERRor: Invalid Parameter @save: %i', 15, 1, @save)
  return
end

if @mode <> 'once' begin
  print 'Block Detection Mode  : ' + @mode
  print 'Block Threshold (msec): ' + convert(varchar(15), @threshold)
  print 'Check Frequency (sec) : ' + convert(varchar(10), @frequency)
  print 'Save Output to table  : ' + convert(varchar(10), @save)
  print ''
  print 'Searching for blocked processes ...'
  print ''
end

set nocount on
set statistics io off
declare @spid int, @spid2 int, @loginame varchar(128), @blocked_by int, @blocked_by_name varchar(128), @orig_id int, @orig_name varchar(128), @timestmp datetime, @i int

if @mode = 'once'
  goto start_check

while 1 = 1 begin

  start_check:

  if exists (select * from sys.dm_exec_requests where [blocking_session_id] <> 0) begin
    print 'Checkpoint ' + convert(varchar(30), getdate())
      
    if @save = 0 begin

     select distinct
             [db] = db_name(s1.[database_id]),
             [waitresource] = ltrim(rtrim(s1.[wait_resource])),
             [table_name] = object_name(sl.rsc_objid),           
             [index_name] = si.[name],
             s1.[wait_time],
             s1.[last_wait_type],
             s1.[session_id],
             session1.[login_name],
             session1.[host_name],
             session1.[program_name],
             [cmd] = isnull(st1.[text], ''),
             session1.[status],
             session1.[cpu_time],
             s1.[lock_timeout],
             [blocked by] = s1.[blocking_session_id],            
             [login_name 2] = session2.[login_name],
             [hostname 2] = session2.[host_name],
             [program_name 2] = session2.[program_name],
             [cmd 2] = isnull(st2.[text], ''),
             session2.[status],
             session2.[cpu_time]         
       -- Process Requests
       from sys.dm_exec_requests (nolock) s1
       outer apply sys.dm_exec_sql_text(s1.sql_handle) st1
       left outer join sys.dm_exec_requests (nolock) s2 on s2.[session_id] = s1.[blocking_session_id]
       outer apply sys.dm_exec_sql_text(s2.sql_handle) st2
       -- Sessions
       left outer join sys.dm_exec_sessions (nolock) session1 on session1.[session_id] = s1.[session_id]
       left outer join sys.dm_exec_sessions (nolock) session2 on session2.[session_id] = s1.[blocking_session_id]
       -- Lock-Info
       left outer join  master.dbo.syslockinfo (nolock) sl on s1.[session_id] = sl.req_spid
       -- Indexes
       left outer join sys.indexes (nolock) si on sl.rsc_objid = si.[object_id] and sl.rsc_indid = si.[index_id]
       where s1.[blocking_session_id] <> 0
             and (sl.rsc_type in (4,5,6,7,8,9)) and sl.rsc_indid <> 0 and sl.req_status = 3
             and s1.[wait_time] >= @threshold

    end else begin

      set @timestmp = getdate()

      insert into [ssi_BlockCheck_Tab]
      select distinct
             @timestmp,
             [db] = db_name(s1.[database_id]),
             [waitresource] = ltrim(rtrim(s1.[wait_resource])),
             [table_name] = object_name(sl.rsc_objid),           
             [index_name] = si.[name],
             s1.[wait_time],
             s1.[last_wait_type],
             s1.[session_id],
             session1.[login_name],
             session1.[host_name],
             session1.[program_name],
             [cmd] = isnull(st1.[text], ''),
             session1.[status],
             session1.[cpu_time],
             s1.[lock_timeout],
             [blocked by] = s1.[blocking_session_id],            
             [login_name 2] = session2.[login_name],
             [hostname 2] = session2.[host_name],
             [program_name 2] = session2.[program_name],
             [cmd 2] = isnull(st2.[text], ''),
             session2.[status],
             session2.[cpu_time],
             [block_orig_id] = null,
             [block_orig_id] = null
       -- Process Requests
       from sys.dm_exec_requests (nolock) s1
       outer apply sys.dm_exec_sql_text(s1.sql_handle) st1
       left outer join sys.dm_exec_requests (nolock) s2 on s2.[session_id] = s1.[blocking_session_id]
       outer apply sys.dm_exec_sql_text(s2.sql_handle) st2
       -- Sessions
       left outer join sys.dm_exec_sessions (nolock) session1 on session1.[session_id] = s1.[session_id]
       left outer join sys.dm_exec_sessions (nolock) session2 on session2.[session_id] = s1.[blocking_session_id]
       -- Lock-Info
       left outer join  master.dbo.syslockinfo (nolock) sl on s1.[session_id] = sl.req_spid
       -- Indexes
       left outer join sys.indexes (nolock) si on sl.rsc_objid = si.[object_id] and sl.rsc_indid = si.[index_id]
       where s1.[blocking_session_id] <> 0
             and (sl.rsc_type in (4,5,6,7,8,9)) and sl.rsc_indid <> 0 and sl.req_status = 3
             and s1.[wait_time] >= @threshold
   
      update [dbo].[ssi_BlockCheck_Tab] set [table_name] = '- unknown -' where [table_name] is null

      -- get block originator
      declare originator_cur cursor for select [blocked by], [loginame 2]
        from [dbo].[ssi_BlockCheck_Tab]
        where [timestamp] = @timestmp
        for update
      open originator_cur
      fetch next from originator_cur into @blocked_by, @blocked_by_name
      while @@fetch_status = 0 begin
        set @i = 0
        set @orig_id = @blocked_by  
        set @orig_name = @blocked_by_name
        set @spid2 = @blocked_by
        while (@spid2 <> 0) and (@i < 100) begin
          if exists(select top 1 [blocked by] from [dbo].[ssi_BlockCheck_Tab] where ([timestamp] = @timestmp) and ([spid] = @spid2)) begin
            select top 1 @spid = [blocked by], @loginame = [loginame 2] from [dbo].[ssi_BlockCheck_Tab] where ([timestamp] = @timestmp) and ([spid] = @spid2)
            set @orig_id = @spid
            set @orig_name = @loginame                      
            set @spid2 = @spid        
          end else
            set @spid2 = 0
          set @i = @i + 1   -- "Emergency Exit", to avoid recursive loop
        end
        update [dbo].[ssi_BlockCheck_Tab] set [block_orig_id] = @orig_id, [block_orig_loginame] = @orig_name where current of originator_cur
        fetch next from originator_cur into @blocked_by, @blocked_by_name
      end
      close originator_cur
      deallocate originator_cur

    end
  end

  end_check:

  if @mode = 'once'
    return

  waitfor delay @frequency
end