October 15, 2011 at 1:05 am
Hi all,
Currently, I suspect that there are records deadlock issue in my application. So, I use this sql stmt:
select distinct object_name(a.rsc_objid), a.req_spid, b.loginame, b.hostname
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
where object_name(a.rsc_objid) is not null;
it returns:
Table name req_spid loginame hostname cmd
TBL_A501prrm sERVER1 UPDATE
TBL_A443prrm sERVER1 UPDATE
TBL_A454prrm sERVER1 UPDATE
TBL_A456prrm sERVER2 UPDATE
TBL_A409prrm sERVER2 SELECT
TBL_A453prrm sERVER2 UPDATE
TBL_A450prrm sERVER2 UPDATE
TBL_A232prrm sERVER3UPDATE
TBL_B454prrm sERVER1 UPDATE
TBL_B501prrm sERVER1 UPDATE
TBL_B443prrm sERVER1 UPDATE
TBL_B450prrm sERVER2 UPDATE
TBL_B456prrm sERVER2 UPDATE
TBL_B453prrm sERVER2 UPDATE
TBL_B409prrm sERVER2 SELECT
TBL_B232prrm sERVER3UPDATE
TBL_c443prrm sERVER1 UPDATE
TBL_c501prrm sERVER1 UPDATE
TBL_c454prrm sERVER1 UPDATE
TBL_c453prrm sERVER2 UPDATE
TBL_c450prrm sERVER2 UPDATE
TBL_c409prrm sERVER2 SELECT
TBL_c456prrm sERVER2 UPDATE
TBL_c232prrm sERVER3UPDATE
How can I select the actual sql statement that there are executing so that I can analyse which one caused the deadlock.
Thanks.
October 15, 2011 at 8:23 am
When SQL Server encounters a deadlock, it automatically resolves it by killing one of the deadlock participants (victim). Enable trace flag 1222 which will log deadlock information in the error logs.
The query you are running gives you the resources on which locks are placed.
if you know the spid, you can know the sql that particular executing by using dbcc inputbuffer
dbcc inputbuffer(spid)
Alternatively, you can use the dmv sys.dm_exec_sql_text to find the query being executed by a spid.
select text from sys.dm_exec_sql_text(sql_handle)
October 16, 2011 at 7:35 pm
I am using MS SQL Server 2005 SP3.
In my case, I don't see that my db automatically resolve the deadlock. Is it that I must turn on something for this feature to work?
When I executed the sql that I put in my post, there are 100+ records, and I am not sure which records cause the deadlock, so is it that i need to run dbcc inputbuffer for all the spid?
October 17, 2011 at 1:44 am
SQL will always detect and resolve deadlocks. You don't need to enable anything or do anything.
Are you sure it's a deadlock and not just a long blocking chain?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2011 at 4:21 am
As Cindy and Gail have said, SQL Server resolves deadlock and kills off one of the transactions involved. You can get error log records about this bty setting trace flag 122.
It seems more likely that you have a long wait somewhere, not a deadlock. In that case you can find out something useful with a query like
select session_id, blocking_session_id
from master.sys.dm_os_waiting_tasks
where blocking_session_id is not null
which will tell you which task is waiting for each other tasks; see which tasks are shown in the blocking_session_id column but not in the session_id column - these are the ultimate sources of the delays - if there are a lot of blosked tasks it is easier to do this by code than by eye:
select wait_duration_ms, session_id primary key, blocking_session_id
into #TempBlockList
from master.sys.dm_os_waiting_tasks
where blocking_session_id is not null
select T1.wait_duration_ms, T1.blocking_session_id, T1.session_id
from #TempBlockList T1
where not exists (select 1 from #TempBlockList T2 where T2.session_id = T1.blocking_session_id)
and the wait_duration_ms columns gives an indication of how long these sessions have been blocking other sessions, so you have a good idea of which sessions could be the culprits. (If your delays are very long - several seconds - running it twice several seconds apart and ignoring blocking_session_ids that don't show up in both runs might - or might not - give a better indication).
Then you can use dbcc inputbuffer to see what the suspect sessions were doing and what the sessions waiting for them were doing.
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply