August 6, 2014 at 9:55 am
Hi Gurus,
i am facing deadlock issues and performance hit, this is my deadlock graph. please provide your input.
2014-08-04 01:01:00.37 spid25s deadlock-list
2014-08-04 01:01:00.37 spid25s deadlock victim=processa6b54c8
2014-08-04 01:01:00.37 spid25s process-list
2014-08-04 01:01:00.37 spid25s process id=processa6b54c8 taskpriority=0 logused=26352 waitresource=PAGE:
10:1:7882635 waittime=714 ownerId=1393474419 transactionname=user_transaction lasttranstarted=2014-08-04T01:00:59.373
XDES=0x43ff86e80 lockMode=S schedulerid=20 kpid=8428 status=suspended spid=62 sbid=0 ecid=0 priority=0 trancount=1
lastbatchstarted=2014-08-04T01:00:59.373 lastbatchcompleted=2014-08-04T01:00:59.370 clientapp=.Net SqlClient Data
Provider hostname=XXXXXXXXXXXXX hostpid=6032 loginname=XXXXXXXXXXX isolationlevel=read committed (2)
xactid=1393474419 currentdb=10 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2014-08-04 01:01:00.37 spid25s executionStack
2014-08-04 01:01:00.37 spid25s frame procname=SCHApiOrder.SCHOrder.SCHOrdersSave_v3 line=198
stmtstart=11460 stmtend=12078 sqlhandle=0x03000a0028f91643f2bf080063a300000100000000000000
2014-08-04 01:01:00.37 spid25s INSERT INTO @ReturnOrders_TEMP_TABLE
2014-08-04 01:01:00.37 spid25s SELECT so.orderLineID
2014-08-04 01:01:00.37 spid25s ,rol.[returnOrderID]
2014-08-04 01:01:00.37 spid25s ,@OrderID
2014-08-04 01:01:00.37 spid25s ,rol.returnOrderLineNumber
2014-08-04 01:01:00.37 spid25s FROM @ReturnOrderLines rol, SCHOrder.SellerOrders so
2014-08-04 01:01:00.37 spid25s WHERE so.orderID = @OrderID
2014-08-04 01:01:00.38 spid25s AND so.[OrderLineNumber] = rol.OrderLineNumber
2014-08-04 01:01:00.38 spid25s inputbuf
2014-08-04 01:01:00.38 spid25s Proc [Database Id = 10 Object Id = 1125579048]
2014-08-04 01:01:00.38 spid25s process id=process43a3948 taskpriority=0 logused=26648 waitresource=PAGE:
10:1:7882635 waittime=797 ownerId=1393474225 transactionname=user_transaction lasttranstarted=2014-08-04T01:00:59.037
XDES=0x17e94f6e80 lockMode=S schedulerid=5 kpid=5116 status=suspended spid=66 sbid=0 ecid=0 priority=0 trancount=1
lastbatchstarted=2014-08-04T01:00:59.037 lastbatchcompleted=2014-08-04T01:00:59.027 clientapp=.Net SqlClient Data
Provider hostname=XXXXXXXXXXXXXXXX hostpid=6028 loginname=XXXXXXXXXXX isolationlevel=read committed (2)
xactid=1393474225 currentdb=10 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2014-08-04 01:01:00.38 spid25s executionStack
2014-08-04 01:01:00.38 spid25s frame procname=SCHApiOrder.SCHOrder.SCHOrdersSave_v3 line=198
stmtstart=11460 stmtend=12078 sqlhandle=0x03000a0028f91643f2bf080063a300000100000000000000
2014-08-04 01:01:00.38 spid25s INSERT INTO @ReturnOrders_TEMP_TABLE
2014-08-04 01:01:00.38 spid25s SELECT so.orderLineID
2014-08-04 01:01:00.38 spid25s ,rol.[returnOrderID]
2014-08-04 01:01:00.38 spid25s ,@OrderID
2014-08-04 01:01:00.38 spid25s ,rol.returnOrderLineNumber
2014-08-04 01:01:00.38 spid25s FROM @ReturnOrderLines rol, SCHOrder.SellerOrders so
2014-08-04 01:01:00.38 spid25s WHERE so.orderID = @OrderID
2014-08-04 01:01:00.38 spid25s AND so.[OrderLineNumber] = rol.OrderLineNumber
2014-08-04 01:01:00.38 spid25s inputbuf
2014-08-04 01:01:00.38 spid25s Proc [Database Id = 10 Object Id = 1125579048]
2014-08-04 01:01:00.38 spid25s resource-list
2014-08-04 01:01:00.38 spid25s pagelock fileid=1 pageid=7882635 dbid=10
objectname=SCHApiOrder.SCHOrder.SellerOrders id=lock595c1e500 mode=IX associatedObjectId=72057594071351296
2014-08-04 01:01:00.38 spid25s owner-list
2014-08-04 01:01:00.38 spid25s owner id=process43a3948 mode=IX
2014-08-04 01:01:00.38 spid25s waiter-list
2014-08-04 01:01:00.38 spid25s waiter id=processa6b54c8 mode=S requestType=convert
2014-08-04 01:01:00.38 spid25s pagelock fileid=1 pageid=7882635 dbid=10
objectname=SCHApiOrder.SCHOrder.SellerOrders id=lock595c1e500 mode=IX associatedObjectId=72057594071351296
2014-08-04 01:01:00.38 spid25s owner-list
2014-08-04 01:01:00.38 spid25s owner id=processa6b54c8 mode=IX
2014-08-04 01:01:00.38 spid25s waiter-list
2014-08-04 01:01:00.38 spid25s waiter id=process43a3948 mode=S requestType=convert
2014-08-04 01:01:05.38 spid26s deadlock-list
2014-08-04 01:01:05.38 spid26s deadlock victim=process47b948
2014-08-04 01:01:05.38 spid26s process-list
2014-08-04 01:01:05.38 spid26s process id=process47b948 taskpriority=0 logused=20820 waitresource=PAGE:
10:1:7882635 waittime=5720 ownerId=1393474376 transactionname=user_transaction lasttranstarted=2014-08-04T01:00:59.270
XDES=0x17ebdcae80 lockMode=S schedulerid=1 kpid=11508 status=suspended spid=107 sbid=0 ecid=0 priority=0 trancount=1
lastbatchstarted=2014-08-04T01:00:59.267 lastbatchcompleted=2014-08-04T01:00:59.260 clientapp=.Net SqlClient Data
Provider hostname=XXXXXXXXXXXXX hostpid=4352 loginname=XXXXXXXXXXX isolationlevel=read committed (2)
xactid=1393474376 currentdb=10 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2014-08-04 01:01:05.38 spid26s executionStack
2014-08-04 01:01:05.38 spid26s frame procname=SCHApiOrder.SCHOrder.SCHOrdersSave_v3 line=198
stmtstart=11460 stmtend=12078 sqlhandle=0x03000a0028f91643f2bf080063a300000100000000000000
2014-08-04 01:01:05.38 spid26s INSERT INTO @ReturnOrders_TEMP_TABLE
2014-08-04 01:01:05.38 spid26s SELECT so.orderLineID
2014-08-04 01:01:05.38 spid26s ,rol.[returnOrderID]
2014-08-04 01:01:05.38 spid26s ,@OrderID
2014-08-04 01:01:05.38 spid26s ,rol.returnOrderLineNumber
2014-08-04 01:01:05.38 spid26s FROM @ReturnOrderLines rol, SCHOrder.SellerOrders so
2014-08-04 01:01:05.38 spid26s WHERE so.orderID = @OrderID
2014-08-04 01:01:05.38 spid26s AND so.[OrderLineNumber] = rol.OrderLineNumber
2014-08-04 01:01:05.38 spid26s inputbuf
2014-08-04 01:01:05.38 spid26s Proc [Database Id = 10 Object Id = 1125579048]
2014-08-04 01:01:05.38 spid26s process id=process43a3948 taskpriority=0 logused=26648 waitresource=PAGE:
10:1:7888279 waittime=5002 ownerId=1393474225 transactionname=user_transaction lasttranstarted=2014-08-04T01:00:59.037
XDES=0x17e94f6e80 lockMode=S schedulerid=5 kpid=5116 status=suspended spid=66 sbid=0 ecid=0 priority=0 trancount=1
lastbatchstarted=2014-08-04T01:00:59.037 lastbatchcompleted=2014-08-04T01:00:59.027 clientapp=.Net SqlClient Data
Provider hostname=XXXXXXXXXX hostpid=6028 loginname=XXXXXXXXXXX isolationlevel=read committed (2)
xactid=1393474225 currentdb=10 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2014-08-04 01:01:05.38 spid26s executionStack
2014-08-04 01:01:05.38 spid26s frame procname=SCHOrder.SCHOrdersSave_v3 line=198
stmtstart=11460 stmtend=12078 sqlhandle=0x03000a0028f91643f2bf080063a300000100000000000000
Cheers,
Got an idea..share it !!
DBA_Vishal
August 7, 2014 at 1:40 am
Hi Vishal
FROM @ReturnOrderLines rol, SCHOrder.SellerOrders so
WHERE so.orderID = @OrderID AND so.[OrderLineNumber] = rol.OrderLineNumber
just check the query rol table contains only order line number. my query is
1. your order line start with 1 for each order or it gets continue number
if it is continue number then there should not be issue
2. if your rol number has only OrderLineNumber and your OrderLineNumber for each order reinitiates with 1 then definitely you have to have order id column in rol table also and it should be included in the query as
FROM @ReturnOrderLines rol, SCHOrder.SellerOrders so
WHERE so.orderID = @OrderID AND so.[OrderLineNumber] = rol.OrderLineNumber
and so.orderid = rol.ordered
August 7, 2014 at 2:36 am
Can you post the definition of the procedure SCHOrdersSave_v3?
Also, where's the rest of the deadlock graph? The resources section is missing, please post that.
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
August 7, 2014 at 11:30 am
I would start looking here first: procname=SCHApiOrder.SCHOrder.SCHOrdersSave_v3 line=198
Next I would change your table variable, @ReturnOrders_TEMP_TABLE, to a real #temp_table.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply