First, you can see the following code:
Blocked-process is a blocked process, and the executed SQL is select * From temp1
Blocking-process is a blocking process, and the executed SQL is select * From temp1 with (nolock)
It would be strange if you caught such a report occasionally. Why? Theoretically, the following SQL statements do not block the preceding SQL statements.
<Blocked-process-Report>
<Blocked-process>
<Process id = "process3d92550" taskpriority = "0" logused = "0" waitresource = "RID: 2: 1: 218: 0 "waittime =" 16975 "ownerid =" 3047491 "transactionname =" select "lasttranstarted =" 2011-11-25t14: 24: 52.210 "xdes =" 0x78ae138 "lockmode =" S "schedulerid =" 4 "kpid =" 4676 "status =" suincluded "spid =" 69 "sbid =" 0 "ECID =" 0 "priority =" 0 "trancount =" 0 "lastbatchstarted =" 2011-11-25t14: 24: 52.210 "lastbatchcompleted =" 2011-11-25t14: 12: 08.507 "clientapp =" Microsoft SQL Server Management studio-query "hostname =" r192249 "hostpid =" 9808 "loginname =" My \ Name "isolationlevel =" Read committed (2) "xactid =" 3047491 "currentdb =" 2 "locktimeout =" 4294967295 "clientoption1 =" 671090784 "clientoption2 =" 390200 ">
<Executionstack>
<Frame line = "1" sqlhandle = "0x0200000088baad31046d031f04c8e7293882ce1_21d893f"/>
</Executionstack>
<Inputbuf>
Select * From temp1 </inputbuf>
</Process>
</Blocked-process>
<Blocking-process>
<Process status = "sleeping" spid = "68" sbid = "0" ECID = "0" priority = "0" trancount = "1" lastbatchstarted = "2011-11-25t14: 25: 00.617 "lastbatchcompleted =" 2011-11-25t14: 25: 00.617 "clientapp =" Microsoft SQL Server Management studio-query "hostname =" r192249 "hostpid =" 9808 "loginname =" My \ Name "isolationlevel =" Read committed (2) "xactid =" 3047423 "currentdb =" 2 "locktimeout =" 4294967295 "clientoption1 =" 671090784 "clientoption2 =" 390200 ">
<Executionstack/>
<Inputbuf>
Select * From temp1 with (nolock) </inputbuf>
</Process>
</Blocking-process>
</Blocked-process-Report>
The blocking will be reproduced below:
1. First set blocked process Shreshold to 5 seconds, so that you can trace it:
sp_configure 'show advanced options',1 ;
GO
RECONFIGURE;
GO
sp_configure 'blocked process threshold',5 ;
GO
RECONFIGURE;
GO
2. Create a trace to capture the block:
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
exec @rc = sp_trace_create @TraceID output, 2, N'D:\Tmp\1', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
3. Create a test table and insert data:
use tempdb
create table temp1
(rowid int)
insert into temp1 values (1)
go
4. Create two session queries and execute them in session 1:
begin tran
update temp1
set rowid = rowid + 1
--select * from temp1 with(nolock)
--rollback
5. Run the following command in session 2:
select * from temp1
6. Then go to session 1 for execution.
select * from temp1 with(nolock)
7. At this time, you will find that the SQL statement in session 2 is always in the waiting state. Wait a dozen seconds and then go to session 1 to execute rollback, so that Session 2 can query the result.
8. Run the following SQL statement to view the blocked Process report:
select cast(TextData as xml), SPID, EndTime, Duration/1000/1000
from fn_trace_gettable(N'D:\Tmp\1.trc', default)
where eventclass = 137
At this time, the above blocked Process report will appear in this article. It is mistaken that select * From temp1 with (nolock) blocks select * From temp1.
9. delete test data and stop trace:
drop table temp1
--stop the trace
exec sp_trace_setstatus 2, 0
--delete the trace but leaves the file on the drive
exec sp_trace_setstatus 2, 2
10. to sum up this problem, because the two SQL statements in the same transaction are executed twice, the blocked Process report records only the last SQL statement, which leads to another misunderstanding, it should be noted that the problem is not solved.
Reference code: http://www.sqlservercentral.com/articles/Blocking/64474/