January 21, 2009 at 3:34 am
I am facing deadlock problem. Details are as follows:
Table Details -
q_group_id - int (identity) - PK
q_number - int (FK)
name - varchar(20)
2 indexes on table
a.Unique Index on q_number and name
b.Index on q_number
When we delete and insert into this table, it creates a deadlock.
Queries are as follows
Delete from A where q_number =
Insert into A
(q_number,name)
values
()
I tried removing 2nd Index on q_number. But still it creates a deadlock.
How can this issue be resolved ?
January 21, 2009 at 6:50 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
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
January 27, 2009 at 12:24 am
Hi,
I have come across this forum in search for help pertaining my deadlock problem. I've tried to enable the trace indicated but unfortunately I can't seem to understand the log quite well. Can somebody please help me with this log?
2009-01-26 16:22:26.17 spid24s deadlock-list
2009-01-26 16:22:26.17 spid24s deadlock victim=processaa8b68
2009-01-26 16:22:26.17 spid24s process-list
2009-01-26 16:22:26.17 spid24s process id=processaa8b68 taskpriority=0 logused=0 waitresource=KEY: 21:72057624669192192 (78010a5b299d) waittime=1468 ownerId=2043071 transactionname=SELECT lasttranstarted=2009-01-26T16:22:24.473 XDES=0x40b33d58 lockMode=S schedulerid=2 kpid=6764 status=suspended spid=368 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-01-26T16:22:24.473 lastbatchcompleted=2009-01-26T16:22:24.473 clientapp=.Net SqlClient Data Provider hostname=ZSQLCL2 hostpid=5360 loginname=autotrakker isolationlevel=read committed (2) xactid=2043071 currentdb=21 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128056
2009-01-26 16:22:26.17 spid24s executionStack
2009-01-26 16:22:26.17 spid24s frame procname=ATDBSQL.dbo.SpSQLrptVehicleKeys line=20 stmtstart=944 sqlhandle=0x03001500e721c34f7e590701379b00000100000000000000
2009-01-26 16:22:26.17 spid24s SELECT
2009-01-26 16:22:26.17 spid24s v.ChassisNo,
2009-01-26 16:22:26.17 spid24s Customer = c.CompanyName,
2009-01-26 16:22:26.17 spid24s c.Country,
2009-01-26 16:22:26.17 spid24s s.DateShip,
2009-01-26 16:22:26.17 spid24s shipStatus = CASE WHEN getdate() > s.DateShip THEN CASE WHEN s.IDShip = 1 THEN'Unassigned' ELSE 'Assigned' END ElSE 'Shipped' END,
2009-01-26 16:22:26.17 spid24s Dealer = D.DealerName,
2009-01-26 16:22:26.17 spid24s Location = ISNULL(dbo.udfGetTransportStatus(DateH4, DateKawasaki, DateOsaka, dateNagoya, DateH1, DateWH1, vdate.datKobe, v.Transport, vdate.datKisarazu), ''),
2009-01-26 16:22:26.17 spid24s Destination = PortDischarge,
2009-01-26 16:22:26.17 spid24s Updated = vdate.strVKUser
2009-01-26 16:22:26.17 spid24s FROM
2009-01-26 16:22:26.17 spid24s dbo.Vehicle v
2009-01-26 16:22:26.17 spid24s JOIN dbo.tblShip s WITH (NOLOCK) ON s.idShip = v.IDShip
2009-01-26 16:22:26.17 spid24s JOIN dbo.tblshipVoyage sv WITH(NOLOCK) ON sv.IDShipVoyage = s.IDShipVoyage
2009-01-26 16:22:26.17 spid24s JOIN dbo.Dealer d WITH (NOLOCK) ON d.IDDealer = v.IDDealer
2009-01-26 16:22:26.17 spid24s JOIN dbo.Customer c WITH (NOLOCK) ON c.IDCustomer = v.IDCustomer
2009-01-26 16:22:26.17 spid24s JOIN dbo.tblVehicleDate vdate WITH (NOLOCK) ON vdate.IDVehicle = v.IDVehicle
2009-01-26 16:22:26.17 spid24s WHERE
2009-01-26 16:22:26.17 spid24s (@DateFrom IS NULL OR s.DateShip BETWEEN CONVERT(VARCHAR(10), @DateFrom, 101) AND dbo.getEOD(@DateTo))
2009-01-26 16:22:26.17 spid24s AND vdate.datVehicleKeys IS NOT NULL
2009-01-26 16:22:26.17 spid24s AND c.IDCustomer LIKE @Customer
2009-01-26 16:22:26.17 spid24s inputbuf
2009-01-26 16:22:26.17 spid24s Proc [Database Id = 21 Object Id = 1338188263]
2009-01-26 16:22:26.17 spid24s process id=processaa9d38 taskpriority=0 logused=1712 waitresource=KEY: 21:72057624669782016 (a20086168c38) waittime=1468 ownerId=2043010 transactionname=user_transaction lasttranstarted=2009-01-26T16:22:24.380 XDES=0x40b32958 lockMode=X schedulerid=2 kpid=340 status=suspended spid=323 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-01-26T16:22:24.663 lastbatchcompleted=2009-01-26T16:22:24.380 clientapp=.Net SqlClient Data Provider hostname=CITCSC074 hostpid=3464 loginname=autotrakker isolationlevel=serializable (4) xactid=2043010 currentdb=21 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2009-01-26 16:22:26.17 spid24s executionStack
2009-01-26 16:22:26.17 spid24s frame procname=ATDBSQL.dbo.spVehicleUpdate line=272 stmtstart=15208 stmtend=22142 sqlhandle=0x030015000dc0551fac83fd008a9b00000100000000000000
2009-01-26 16:22:26.17 spid24s UPDATE vehicle
2009-01-26 16:22:26.17 spid24s SET
2009-01-26 16:22:26.17 spid24s DateStock = @DateStock,
2009-01-26 16:22:26.17 spid24s IDDealer = @IDDealer,
2009-01-26 16:22:26.17 spid24s VehicleYear = @VehicleYear,
2009-01-26 16:22:26.17 spid24s ChassisNo = @ChassisNo,
2009-01-26 16:22:26.17 spid24s Cleaning = @IsPreSold,
2009-01-26 16:22:26.17 spid24s blnNoNZ = @IsNoNZ,
2009-01-26 16:22:26.17 spid24s VehicleMake = @VehicleMake,
2009-01-26 16:22:26.17 spid24s VehicleModel = @VehicleModel,
2009-01-26 16:22:26.17 spid24s Grade = @Grade,
2009-01-26 16:22:26.17 spid24s VehicleTransmission = @Transmission,
2009-01-26 16:22:26.17 spid24s VehicleKMS = @Mileage,
2009-01-26 16:22:26.17 spid24s blnUnknownMileage = @IsUnknownMileage,
2009-01-26 16:22:26.17 spid24s VehicleEngineDisplacement = @CCRating,
2009-01-26 16:22:26.17 spid24s VehicleDoors = @Doors,
2009-01-26 16:22:26.17 spid24s VehicleColorExterior = @ExteriorColor,
2009-01-26 16:22:26.17 spid24s VehicleColorInterior = @InteriorColor,
2009-01-26 16:22:26.17 spid24s VehicleInteriorType = @InteriorType,
2009-01-26 16:22:26.17 spid24s IDVehicleCategory = @IDVehicleCategory,
2009-01-26 16:22:26.17 spid24s strReferenceNo = @ReferenceNo,
2009-01-26 16:22:26.17 spid24s strShupin = @Shuppin,
2009-01-26 16:22:26.17 spid24s ConditionGrade = @ConditionGrade,
2009-01-26 16:22:26.17 spid24s VehicleFuel = @VehicleFuel,
2009-01-26 16:22:26.17 spid24s --DateTohonActual = @DateTohonActual,
2009-01-26 16:22:26.17 spid24s --DateCheckList = @DateCheckList,
2009-01-26 16:22:26.17 spid24s /*
2009-01-26 16:22:26.17 spid24s Specification
2009-01-26 16:22:26.17 spid24s */
2009-01-26 16:22:26.17 spid24s VehiclePowerSteering = @IsPS,
2009-01-26 16:22:26.17 spid24s VehicleAC = @IsAC,
2009-01-26 16:22:26.17 spid24s VehiclePowerWindow = @IsPW,
2009-01-26 16:22:26.17 spid24s VehicleSunRoof = @IsSunRoof,
2009-01-26 16:22:26.17 spid24s blnIsLeftHandDrive = @IsLeftHand,
2009-01-26 16:22:26.17 spid24s blnABS = @IsABS,
2009-01-26 16:22:26.17 spid24s blnAir
2009-01-26 16:22:26.17 spid24s inputbuf
2009-01-26 16:22:26.17 spid24s Proc [Database Id = 21 Object Id = 525713421]
2009-01-26 16:22:26.17 spid24s resource-list
2009-01-26 16:22:26.17 spid24s keylock hobtid=72057624669192192 dbid=21 objectname=ATDBSQL.dbo.Vehicle indexname=vehicle_idxVehicleChassisNo id=lock2f7a88c0 mode=X associatedObjectId=72057624669192192
2009-01-26 16:22:26.17 spid24s owner-list
2009-01-26 16:22:26.17 spid24s owner id=processaa9d38 mode=X
2009-01-26 16:22:26.17 spid24s waiter-list
2009-01-26 16:22:26.17 spid24s waiter id=processaa8b68 mode=S requestType=wait
2009-01-26 16:22:26.17 spid24s keylock hobtid=72057624669782016 dbid=21 objectname=ATDBSQL.dbo.Vehicle indexname=Vehicle_pkyIDVehicle id=lock3665fec0 mode=U associatedObjectId=72057624669782016
2009-01-26 16:22:26.17 spid24s owner-list
2009-01-26 16:22:26.17 spid24s owner id=processaa8b68 mode=S
2009-01-26 16:22:26.17 spid24s waiter-list
2009-01-26 16:22:26.17 spid24s waiter id=processaa9d38 mode=X requestType=convert
thanks in advance for the helping hands.
regards,
Neil
<hr noshade size='1' width='250' color='#BBC8E5'>Regards,
Neil
January 27, 2009 at 3:57 am
I'll take a look when I get a few min.
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
January 29, 2009 at 9:13 pm
Bart Duncan has an excellent writeup on interpreting this output
http://blogs.msdn.com/bartd/archive/2006/09/13/Deadlock-Troubleshooting_2C00_-Part-2.aspx
I'll take a crack at interpreting the output.
1) SQL Server sees things as processes and those processes use resources
While I wish they listed processes as spids in the dump, they don't so we have use the available terminology.
2) The deadlock victim is found in the second line labeled
deadlock victim=processaa8b68
3) Now we know the victim, that means another process wanted some of the victims resources and was chosen as the winner. So what resources did the winner want? Go to the bottom and find "resource-list"
4) indexname=vehicle_idxVehicleChassisNo shows the owner and all the waiters. The owner
owner id=processaa9d38 mode=X had and exclusive lock.
Comparing this to the deadlock victim above means he was a deadlock winner.
5) Below that is waiter-list with waiter id=processaa8b68 (who is the poor dead lock victim)
6) Looking further down we see the resource indexname=Vehicle_pkyIDVehicle id=lock3665fec0 mode=U
The owner-list shows the owner as owner id=processaa8b68 (the soon to be victim)
Below that it shows the waiters all lined up
and guess who is the top (and only) waiter? waiter id=processaa9d38 mode=X
Thats right - the winner.
Since a deadlock is process A has resource G and wants resource H
but process B has resource H and wants resource G , something has to give.
In this senario: A is processaa9d38 and B is processaa8b68
processaa9d38 (A)
has resource indexname=vehicle_idxVehicleChassisNo with an exclusive lock but wants indexname=Vehicle_pkyIDVehicle
processaa8b68 (B)
has resource indexname=Vehicle_pkyIDVehicle
but wants indexname=vehicle_idxVehicleChassisNo
SQL Server had to play the Judge and ruled against B
Poor processaa8b68 (B). He went to the big bit bucket much to early.
Clear as mud?
January 29, 2009 at 10:44 pm
Thanks for taking the time in reading my logs. I hope I could learn the skills soon on understanding those. Can I ask for your suggestion on how I should go about to resolve this problem? This particular deadlock has been causing me nightmares.
thanks again!
<hr noshade size='1' width='250' color='#BBC8E5'>Regards,
Neil
January 29, 2009 at 10:48 pm
That was a good explanation. But how to avoid this problem in future ? Is there a problem with indexes which processes wanted to acquire or there is a problem in queries ?
January 29, 2009 at 11:09 pm
Whenever this deadlock occurs, most of our users are getting timeout errors when running our applications. And every time I try to kill the blocking process, it becomes Killed/Rollback which would still causes the other transactions to timeout. The only thing I can do is to restart the SQL service. What steps should I take into resolving this issue. I am quite weak with SQL performance troubleshooting.
I am hoping to learn and improve on this that's why I am trying to find as much help as I could. Hopefully, I'd be able to improve quickly and be able to help others as well later on.
Many thanks!
<hr noshade size='1' width='250' color='#BBC8E5'>Regards,
Neil
January 31, 2009 at 9:15 pm
Bart's recommendation is to run the queries through the database tuning advisor. To do that, use SQL Profiler and capture the queries to a file. Then play back the file in the Database Tuning Advisor and see what recommendations it offers.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply