Lock timeout - How to find blocking session

frgusto · 2007-10-23

A customer is complaining about big problems with locked tables.
"Table X is locked by another user".
Lock timeout is 10s.

Running SQL Profiler logging event lock:timeout, I do catch several timeouts and information about who was locked. But there is no information about which session that is locking the table.
By executing T-SQL command sp_who2 there is a column "blocked_by". In Navision session overview there is a column "Blocking connection ID" this information would be very useful.

Can SQL Profiler show info about "blocked_by" or is there any other logging tool that can do this?

SQL 2000, Navision 4.00 SP3, 70Gb DB, 70 active users.

Regards Fredrik

Marije_Brummel · 2007-10-27

You cannot look back in this detail by default in NAV or SQL.

Try having a look at the SQL Perform tools, this tool allows you to do this and more.

I have it running at over 30 customers and love it for analysing these kind of issues.

Link: www.sqlperform.com

stryk · 2007-10-28

Hi!

Well, first some bold advertisement: "The NAV/SQL Performance Toolbox" (PTB) also provides some pretty smart features to investigate blocks etc. (for details please visit http://www.stryk.info).

But you could do this:

1) In EM create an Alert - e.g. "SSI: Block Check" - monitoring the SQL Performance Counter "Lock Wait Time msec"; it should be raised e.g. when the value raises above 5000, means an Alert is triggered when anything is locked for longer than 5 seconds. Set a response-delay of 10 seconds.

2) As a response this Alert should trigger a new SQL Agent Job, e.g. "SSI: Block Check" - with one step executing this TSQL:

if exists (select * from master..sysprocesses where [blocked] <> 0) begin
  print getdate()
  select s1.[waitresource],
         [object_name] = 
         ( select top 1 object_name([rsc_objid]) from master..syslockinfo 
           where (rsc_text = substring( s1.[waitresource] , 
                            charindex ('(', s1.[waitresource]) , 
                            len(s1.[waitresource]) - charindex('(', s1.[waitresource]) + 1))
             and (req_spid = s1.[spid]) and (req_mode >= 5 )
         ),
         s1.[waittime],s1.[lastwaittype],s1.[spid],
         s1.[loginame],s1.[hostname],s1.[cmd],[blocked by]=s1.[blocked],
         [loginame 2]=s2.[loginame],[hostname 2]=s2.[hostname],[cmd 2]=s2.[cmd],
         s1.[status],s1.[cpu],[db]=db_name(s1.[dbid])
  from master..sysprocesses s1 (nolock)
  inner join master..sysprocesses s2 (nolock) on s2.[spid] = s1.[blocked] 
  where s1.[blocked] <> 0 and s1.[waittime] >= 5000
end;

Define an output-file for the job output.

This has implemented basic automatic block detection:

When a lock has to wait for more than 5 seconds, the Alert is raised which triggers the Job. The job writes some info about the "Who is Who" of the block into a file which you could analize afterwards.

As mentioned, the PTB could do all this in a more convenient and detailed way, but this should work for the start!

P.S.: The counter "Lock Wait Time" is not really relyable, maybe you have to adjust the threshold value. With SQL 2005 one should use the "Processes blocked" counter instead.

colin_leversuch-roberts · 2007-11-06

you can use this in SQL 2005 / SQL 2008 - I use this as part of my diagnostic reports - this shows the full blocking chain of users but doesn't show object data so well - I have another report for that.

select
t1.resource_type,
'database'=db_name(t1.resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t2.wait_type,
t2.wait_duration_ms/1000 as wait_time,
t1.request_mode,
t1.request_session_id as User_Session,t3.login_name as User_name,
t2.blocking_session_id as Blocked_by,t4.login_name as Blocker_Name
from
master.sys.dm_tran_locks as t1 with (nolock) join
master.sys.dm_os_waiting_tasks as t2 with (nolock) on t1.lock_owner_address = t2.resource_address
join master.sys.dm_exec_sessions as t3 with (nolock) on t1.request_session_id = t3.session_id
join master.sys.dm_exec_sessions as t4 with (nolock) on t2.blocking_session_id = t4.session_id
where
t1.request_session_id = isnull(null,t1.request_session_id)

kriki · 2007-11-09

Also the session table in Navision (File=>Database=>information=>tab sessions=>drill down on "Current sessions") or create a form on table "Session" can be useful to find blocking session.

And this stays in Navision. No need to to use SQL!

Lock timeout - How to find blocking session

Comments

Categories