August 28, 2013 at 7:36 am
Hello guys,
don't you know how to find out the reason why the update statement isn't able to finish a job? It seems to me that lock is wait to be granted. But why? The blocking_session_id = '-2' means it's ME?
resource_typeresource_subtyperesource_database_idresource_descriptionresource_associated_entity_idresource_lock_partitionrequest_moderequest_typerequest_statusrequest_reference_countrequest_lifetimerequest_session_idrequest_exec_context_idrequest_request_idrequest_owner_typerequest_owner_idrequest_owner_guidrequest_owner_lockspace_idlock_owner_address
RID51:26915:5 720575940671569920ULOCKWAIT105700TRANSACTION16002700000000-0000-0000-0000-0000000000000x000000017F3DE1C0:1:10x000000017BB16600
resource_typeresource_database_idresource_associated_entity_idrequest_moderequest_session_idblocking_session_id
RID572057594067156992U7157
RID572057594067156992U6757
RID572057594067156992U57-2
Really thanks for help! I tried to make everything possible, but...I am still in troubles.
August 28, 2013 at 7:54 am
by the way I'm making update statement:
use TBCDEV
GO
Begin Transaction
update GFO.GFO_TRANSACTION set DESCRIPTION=null, TRANSACTIONNUMBER=null, REGISTRATIONDATE='20120713 12:53:08.644', DISCRIMINATOR='cz.bsc.g6.components.loan.domain.CreditLimitChangeTransaction', REMARK=null, ENVELOPE=null, STATUS='CS', BUSINESSOBJECTTYPE='3.09.11.01', REGISTEREDBY=2635, SIGNEDCONTENT=null, EXTERNALSYSTEMRESULTCODE=' DCM_30021', EXTERNALSYSTEMRESULTDESC='The request was not found.', CHANNEL='IBS' where ID=369935;
so I am the session 57. And I am waiting, but I don't know for which reason.
August 28, 2013 at 12:58 pm
I think -2 is an orphaned distributed transaction. Do some investigations along that line.
Also, I see that you are doing a parallel update (same spid multiple times) and using RIDs to boot. Possible tuning opportunities due to a) suboptimal indexing and b) HEAP table in play.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 29, 2013 at 9:25 am
Thanks Kevin, you've right. It's great that you see a problem just from few rows.
August 29, 2013 at 2:31 pm
redy007 (8/29/2013)
Thanks Kevin, you've right. It's great that you see a problem just from few rows.
I suspect that if you had been playing around with SQL Server for over 40000 man hours like I have you would probably have caught it too. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply