Blocking
So far we have executed many queries and seen blocking as well.But I would still like to discuss blocking a bit more ...
Use sysprocesses DMV/Table to find blocking .This has 3 advanages .One that you will see only the blocking SPIDs and not all the SPIDs by using sp_lock.
Two , you will be able to see head of the blocking chain. Three , you wil be able to see on what resource the SPID is waiting for .Once you have all the knowledge above you can surely use sp_lock to get some more information .
Lab
1st User
set transaction isolation level serializable
begin tran
update t1 set c1 = 3000 where c2 < 3000
--rollback
2nd user
set transaction isolation level serializable
begin tran
select * from t1
Now check the sysprocesses DMV (I am using 2005) :
select * from sys.sysprocesses where blocked <>0
You will notice that lastwaittype is LCK_M_IS
This means that the SPID which is being blocked is actually waiting for an IS lock to grab but cannot and being blockd by another spid.
The SPID that is blocking is shown in the "Blocked" column .
You can cross check by running sp_lock
Now if you run one more query :
set transaction isolation level serializable
begin tran
select * from t1
You will notice the now there are 2 rows in the sysprocesses DMV where blocked <>0
here you will see :
SPID A is blocked by SPID B --> SPID B is blocked by SPID C
So who is the actual culprit ..yes its C and is called as the head of the blockign chain .
Now a tough question for you :
Why SPID A is blocked by SPID B and not C ? .....Think :) ..
Deadlocks
you have already seen blocking ..
Lets try our hands at creating a deadlock.I am not going to explain what is a deadlock .
For the benefit of all I will keep the Isolation level to serializable .
It will help us in creating deadlocks easily .
Lab
First do this :
select count(*) from t2
select count(*) from t1
If the number of rows are same then we are good to go .
If not, then drop the table with less number of rows and recreate it with the help of another table .
How to Do it .......check previous updates ...
First Window :
set transaction isolation level serializable
go
begin tran
update dbo.t3 set c2 = 230 where c2 > 1000 and c3 > 700
Second Window :
set transaction isolation level serializable
go
begin tran
update dbo.t2 set c3 = 230 where c2 > 1000 and c3 > 700
First Window :
begin tran
select * from t2
--rollback
Second Window :
begin tran
select * from t3
--rollback
You will see that the SPID in second window will be the deadlock victim:
Msg 1205, Level 13, State 56, Line 2
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Like this you can create many deadlocks
How to capture the deadlock Graph
There are 2 ways :
- Use Traceflag 1204 <-- for 2000 and 1222 <-- for 2005 and above.
- Use WMI class deadlock_graph to capture the graph for SQL Server 2005 and above(for 2000 its a bit tricky .Hence not mentioning it here.)
Using TraceFlag 1222
dbcc traceon(1222 , -1)
go
Create the deadlock (I am creating the same one as mentioned above)
Using WMI Class and SQL Server Agent
Please go through one of my previous post : http://ms-abhay.blogspot.com/2009/09/wmi-alert-for-deadlocking-no-need-to.html
How to read the deadlock graph
Check the Error logs and copy the deadlock information on a separate notepad window.
You can find my analysis below:
deadlock-list
deadlock victim=process968d48
process-list
process id=process968d48 taskpriority=0 logused=543996 waitresource=OBJECT: 8:629577281:0 waittime=3359 ownerId=29121 transactionname=user_transaction lasttranstarted=2009-12-28T15:40:39.240 XDES=0x3e8f838 lockMode=IS schedulerid=2 kpid=6124 status=suspended spid=52 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-12-28T15:40:56.500 lastbatchcompleted=2009-12-28T15:40:39.413 lastattention=2009-12-28T15:31:16.907 clientapp=Microsoft SQL Server Management Studio - Query hostname=abchaudh hostpid=1336 loginname=abchaudh\Abhay isolationlevel=serializable (4) xactid=29121 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=adhoc line=2 stmtstart=26 sqlhandle=0x02000000bea8742faf157cd9e8e65d380d705fefe8dfb182
select * from t3
inputbuf
begin tran
select * from t3
process id=process969018 taskpriority=0 logused=576116 waitresource=OBJECT: 8:597577167:0 waittime=10000 ownerId=28599 transactionname=user_transaction lasttranstarted=2009-12-28T15:40:34.780 XDES=0x3e8f290 lockMode=IS schedulerid=2 kpid=8140 status=suspended spid=53 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-12-28T15:40:49.873 lastbatchcompleted=2009-12-28T15:40:34.907 lastattention=2009-12-28T15:27:27.483 clientapp=Microsoft SQL Server Management Studio - Query hostname=abchaudh hostpid=1336 loginname=abchaudh\Abhay isolationlevel=serializable (4) xactid=28599 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=adhoc line=2 stmtstart=26 sqlhandle=0x02000000b7cdeb20804433e7571f29d85516ff688d005724
select * from t2
inputbuf
begin tran
select * from t2
resource-list
objectlock lockPartition=0 objid=597577167 subresource=FULL dbid=8 objectname=deadlocktest.dbo.t2 id=lockcc7f700 mode=X associatedObjectId=597577167
owner-list
owner id=process968d48 mode=X
waiter-list
waiter id=process969018 mode=IS requestType=wait
objectlock lockPartition=0 objid=629577281 subresource=FULL dbid=8 objectname=deadlocktest.dbo.t3 id=lockcc7f780 mode=X associatedObjectId=629577281
owner-list
owner id=process969018 mode=X
waiter-list
waiter id=process968d48 mode=IS requestType=wait
Analysis :
----------
Divide the output into 2 parts : Deadlock list and resource list
process968d48
--------------
logused=543996
waitresource=OBJECT: 8:629577281:0 <-- here 8 is DBID ,629577281 is the object ID and 0 is heap
lockMode=IS
spid=52
sbid=0 <-- Its the Batch ID .It will be 0 until the application is using MARS (check BOL for MARS)
ecid=0 <-- zero means no paralelism is going on (Refer BOL for Query Parallelism)
transcount=2 <-- We are running 2 transaction under this SPID (select and update)
isolationlevel=serializable
currentdb=8
procname=adhoc (select * from t3) <-- Its an adhoc query
inputbuf =
begin tran
select * from t3
process969018
--------------
logused=576116
waitresource=OBJECT: 8:597577167:0
lockMode=IS
spid=53
sbid=0
ecid=0
transcount=2
isolationlevel=serializable
currentdb=8
procname=adhoc (Select * from t2)
inputbuf=
begin tran
select * from t2
Story
---------
-> At the time of deadlock 2 queries were running (under SPID 52 and 53) .
-> The processes were process968d48 for SPID 52 and process969018 for SPID 53
-> process968d48 took X lock on table T2 and requested IS on table T3 ..which was blocked by process969018
-> process969018 took X lock on table T3 and requested IS on table T2.. which was blocked by process968d48
-> deadlock victim is process968d48
Why process968d48 is deadlock victim ?
Because it has less undo to rollback .
How do you know if it has less undo ?
See log used values .
Rest ..........in next blog ....keep looking ...
No comments:
Post a Comment