SQL Server Performance

Why doesn't the resource-list show the resource owned by the deadlock victim?

Discussion in 'SQL Server 2005 General DBA Questions' started by DBADave, Nov 13, 2008.

  1. DBADave New Member

    I am unable to determine what resource a deadlock victim owned based upon the deadlock dump below. I thought the resource-list is supposed to contain this information. I know I can use Profiler to find it, but I want to know why the trace flags didn't provide the information.
    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=BLAIRNETLVADMIN 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=BLAIRNETLVADMIN 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=BLAIRNETLVADMIN 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=BLAIRNETLVADMIN 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

Share This Page