November 13, 2008 at 1:04 pm
*** SEE BELOW ***
Why can't I see the resource owned by the deadlock victim in the resource-list?
November 13, 2008 at 1:24 pm
This one?
deadlock victim=processe3dc48
pagelock fileid=1 pageid=534789 dbid=5 objectname=LVTS.dbo.allocations id=lock1d81d0c0 mode=SIU associatedObjectId=72057596482748416
owner-list
owner id=processe3dc48 mode=S
waiter-list
waiter id=processd145c8 mode=IX requestType=convert
Looks like it had a lock on page 534789 and wanted a lock on page 534790 (which process processd145c8 had a lock on)
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
November 13, 2008 at 1:29 pm
I'm an idiot. I copied/pasted the wrong trace. Here's the one I'm having trouble with. Sorry about that.
deadlock-list
deadlock victim=processff4988
process-list
process id=processd145c8 taskpriority=0 logused=98004 waitresource=PAGE: 5:1:534788 waittime=4312 ownerId=168821761 transactionname=user_transaction lasttranstarted=2008-11-13T10:13:18.393 XDES=0x764984d8 lockMode=IX schedulerid=4 kpid=6868 status=suspended spid=86 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2008-11-13T10:13:18.393 lastbatchcompleted=2008-11-13T10:13:18.393 hostpid=6800 loginname=BLAIRNET\LVADMIN isolationlevel=read committed (2) xactid=168821761 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=119840
executionStack
frame procname=LVTS.dbo.equity_step_in line=437 stmtstart=25052 stmtend=28612 sqlhandle=0x03000500126a5d2e6e0200004f9b00000100000000000000
update allocations
set
quantity = allocations.quantity + #order_allocations.quantity,
accrued_income = allocations.accrued_income + #order_allocations.accrued_income,
commission = allocations.commission + #order_allocations.commission,
taxes = allocations.taxes + #order_allocations.taxes,
local_commission = allocations.local_commission + #order_allocations.local_commission,
exchange_fee = allocations.exchange_fee + #order_allocations.exchange_fee,
stamp_tax = allocations.stamp_tax + #order_allocations.stamp_tax,
levy = allocations.levy + #order_allocations.levy,
other_taxes_fees = allocations.other_taxes_fees + #order_allocations.other_taxes_fees,
other_charges = allocations.other_charges + #order_allocations.other_charges,
modified = 1,
modified_by = @current_user,
modified_time = getdate(),
primary_confirmed = case when (allocations.upload_count = 0 and allocations.primary_confirmed = 1) then 0 else allocations.prima
inputbuf
Proc [Database Id = 5 Object Id = 777873938]
process id=processe3cf28 taskpriority=0 logused=0 waitresource=KEY: 5:72057596546514944 (01008dce9dfe) waittime=8718 ownerId=168822669 transactionname=SELECT lasttranstarted=2008-11-13T10:13:18.893 XDES=0x5fcc45b8 lockMode=S schedulerid=6 kpid=4360 status=suspended spid=109 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2008-11-13T10:13:18.893 lastbatchcompleted=2008-11-13T10:13:16.580 hostpid=6848 loginname=BLAIRNET\LVADMIN isolationlevel=read committed (2) xactid=168822669 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=119840
executionStack
frame procname=LVTS.dbo.get_blotter_changes line=20 stmtstart=1270 stmtend=12848 sqlhandle=0x0300050092866f28350200004f9b00000100000000000000
select
security_id = blocked_orders.security_id,
block_id = blocked_orders.block_id,
dir = blocked_orders.side_code,
order_broker_id = blocked_orders.directed_broker_id,
price_limit_type_ordered = blocked_orders.limit_type_code_ordered,
best_price_1_ordered = blocked_orders.best_price_1_ordered,
best_price_2_ordered = blocked_orders.best_price_2_ordered,
worst_price_1_ordered = blocked_orders.worst_price_1_ordered,
worst_price_2_ordered = blocked_orders.worst_price_2_ordered,
time_in_force = blocked_orders.time_in_force_code,
trader_id = blocked_orders.trader_id,
order_counter = blocked_orders.event_counter,
blk_qty_ordered = blocked_orders.quantity_ordered,
blk_mv_ordered = blocked_orders.market_value_ordered,
quantity_held = blocked_orde
inputbuf
Proc [Database Id = 5 Object Id = 678397586]
process id=processe3dc48 taskpriority=0 logused=0 waitresource=PAGE: 5:1:534789 waittime=4937 ownerId=168824219 transactionname=SELECT lasttranstarted=2008-11-13T10:13:22.657 XDES=0x45ecedc0 lockMode=S schedulerid=6 kpid=7028 status=suspended spid=68 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2008-11-13T10:13:22.610 lastbatchcompleted=2008-11-13T10:13:22.610 hostpid=6848 loginname=BLAIRNET\LVADMIN isolationlevel=read committed (2) xactid=168824219 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=119840
executionStack
frame procname=LVTS.dbo.get_xref_info_by_account line=98 stmtstart=7060 stmtend=7858 sqlhandle=0x03000500ad5a7678040600004f9b00000100000000000000
select
allocations.account_id,
allocations.side_code,
allocations.security_id,
allocations.quantity,
allocations.price,
allocations.accrued_income,
allocations.settlement_currency_id,
allocations.order_id
from
allocations,
#account
where
allocations.account_id = #account.account_id and
allocations.deleted = 0 and
allocations.primary_confirmed = 0
frame procname=LVTS.dbo.get_xref_info line=41 stmtstart=1990 stmtend=2232 sqlhandle=0x030005001fa35e7a120600004f9b00000100000000000000
execute get_xref_info_by_account
@last_refresh_time,
@account_id,
@security_id,
@current_user
inputbuf
Proc [Database Id = 5 Object Id = 2053022495]
process id=processff4988 taskpriority=0 logused=0 waitresource=PAGE: 5:1:534789 waittime=6203 ownerId=168824034 transactionname=SELECT lasttranstarted=2008-11-13T10:13:21.393 XDES=0x48291240 lockMode=S schedulerid=8 kpid=4504 status=suspended spid=134 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2008-11-13T10:13:21.393 lastbatchcompleted=2008-11-13T10:13:21.393 hostpid=6800 loginname=BLAIRNET\LVADMIN isolationlevel=read committed (2) xactid=168824034 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=119840
executionStack
frame procname=LVTS.dbo.get_xref_info_by_account line=98 stmtstart=7060 stmtend=7858 sqlhandle=0x03000500ad5a7678040600004f9b00000100000000000000
select
allocations.account_id,
allocations.side_code,
allocations.security_id,
allocations.quantity,
allocations.price,
allocations.accrued_income,
allocations.settlement_currency_id,
allocations.order_id
from
allocations,
#account
where
allocations.account_id = #account.account_id and
allocations.deleted = 0 and
allocations.primary_confirmed = 0
frame procname=LVTS.dbo.get_xref_info line=41 stmtstart=1990 stmtend=2232 sqlhandle=0x030005001fa35e7a120600004f9b00000100000000000000
execute get_xref_info_by_account
@last_refresh_time,
@account_id,
@security_id,
@current_user
inputbuf
Proc [Database Id = 5 Object Id = 2053022495]
resource-list
pagelock fileid=1 pageid=534789 dbid=5 objectname=LVTS.dbo.allocations id=lock1d81d0c0 mode=IX associatedObjectId=72057596482748416
owner-list
owner id=processd145c8 mode=IX
waiter-list
waiter id=processff4988 mode=S requestType=wait
waiter id=processe3dc48 mode=S requestType=wait
pagelock fileid=1 pageid=534788 dbid=5 objectname=LVTS.dbo.allocations id=lock3c114b40 mode=SIU associatedObjectId=72057596482748416
owner-list
owner id=processe3dc48 mode=S
waiter-list
waiter id=processd145c8 mode=IX requestType=convert
keylock hobtid=72057596546514944 dbid=5 objectname=LVTS.dbo.blocked_orders indexname=blocked_orders_pk id=lock76529940 mode=X associatedObjectId=72057596546514944
owner-list
owner id=processd145c8 mode=X
waiter-list
waiter id=processe3cf28 mode=S requestType=wait
November 13, 2008 at 11:34 pm
Any idea why SQL Server does not display it?
Thanks, Dave
November 14, 2008 at 12:50 am
Not offhand.
Do you need to see it? I know it's interesting from an academic point, but from what I can see of this one and the previous graph, there's more than enough info there to find and fix the deadlock, even with the missing resource.
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
November 14, 2008 at 8:21 am
It's more out of curiosity, plus I was going to write a script to read the deadlock WMI data and send an automated report to the developers to eliminate the need for me to constantly analyze the log results. Perhaps just wishful thinking on my part. 😀 The resource-list, according to documentation, should have showed me the resources owned by all processes involved in the deadlock so my script was going to check the list. Now that doesn't appear to be a good option. I will give Microsoft a call to see if they can explain why it didn't appear in the list.
In this particular case the victim held a page lock on a resource that another process wanted to exclusively access. At least that's how I see it.
Thanks, Dave
November 14, 2008 at 8:49 am
It could be that for some reason SQL couldn't identify the locked resource. I have seen that from time to time. If you get an answer, please let us know.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply