Can't determine resource owned by deadlock victim

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Can't determine resource owned by deadlock victim

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2008-11-13 : 15:21:26

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-13 : 18:13:47

Have you tried using the Deadlock Graph in SQL Profiler rather than the trace flags?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2008-11-13 : 19:44:28

I'm running Profiler, but that's something I don't want to keep running all of the time. I prefer to use the trace flags.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-14 : 11:43:24

See this how to interpret if you have enabled T-1222

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2008-11-14 : 12:00:01

I've seen that before and it is a nice blog, but that doesn't explain why the resource-list does not contain the resource owned by the victim. It's supposed to. I'll call Microsoft for an explanation.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-14 : 12:24:40

you might try this:
http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources