Help get this topic noticed by sharing it on Twitter, Facebook, or email.

UNABLE_TO_LOCK_ROW error on upsert of Opportunity

I'm currently getting this error message when trying to upload using tableloader. Tableloader has been much better and faster than bulkops, and works for this object and set of data in a sandbox when using a smaller dataset.

In our fullcopy org, I'm trying to upsert 213515 opportunities.

One of the record messages:
UNABLE_TO_LOCK_ROW:unable to obtain exclusive access to this record or 162 records: 0011q0000037FcfAAE,0011q0000037LLKAA2,0011q0000037JiFAAU,0011q0000037JT5AAM,0011q0000037Kn2AAE,0011q0000037HDcAAM,00

I'm upserting, using this:

EXEC [dbo].[SF_TableLoader]
'Upsert',
'SFDC_CRONERI_FULLCOPY', --Linked server, connection tested and working.
'Opportunity_LoadStage7',
'Legacy_System_ID__c' --External ID.

I can confirm:
-The opportunities have parent account ids, and the accounts do already exist.
-Some opportunities lock, others do not. It is not the same opportunities each time.
-I have tried this with ALL processes, triggers and installed packages disabled.
-There is a sort identity column added to the tableloader table.
-I've reviewed the locking cheat sheet and apex dev guides on this, but they don't really apply here.

I'm looking for ideas to try, even if you don't have a specific solution (as far as I can see there is no way to tell what has actually caused the lock). Even a way to try failed records would be appreciated!

Any ideas?
1 person has
this problem
+1
Reply
  • I’m frustrated
    Just to add to this, one of the suggestions in the salesforce docs is to sort by the parent ID, but I can't do this in my opportunity creation script as I populate Account ID AFTER creation...and SQL tables have no inherent order.

    I don't see a way to pass a specific sort column to Tableloader, as far as I can tell we only make sure that there's an Identity column in the table?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Can I see the complete message output from the SF_TableLoader command?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Certainly, it's my logging at the start and end.:

    Opportunity_LoadStage7 START:Sep 5 2018 8:31AM
    (217966 row(s) affected)
    --- Starting SF_TableLoader for Opportunity_LoadStage7 V3.6.5
    08:31:29: Run the DBAmpNet2.exe program.
    08:31:29: DBAmpNet2 3.6.5.0 (c) Copyright 2015-2017 forceAmp.com LLC
    08:31:29: Parameters: upsert Opportunity_LoadStage7 DEVMANSQL01\APPDB03 SalesforceCroneriMigration_Full SFDC_CRONERI_FULLCOPY
    08:31:29: Using the Salesforce bulkapi2 API.
    08:31:32: Sort column will be used to order input rows.
    08:31:32: Warning: Column Id ignored because it not creatable in the opportunity object.
    08:31:32: Warning: Column SQLNote ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column DQNote ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column DQScore ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column MigrationCheck_IsValid ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column AGRZR ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column payment_method_source ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column VBELN ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column AUART ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column Account_LegacyID ignored because it does not exist in the opportunity object.
    08:31:32: Warning: Column Sort ignored because it does not exist in the opportunity object.
    08:31:41: Drop Opportunity_LoadStage7_Result if it exists.
    08:31:41: Create Opportunity_LoadStage7_Result with new structure.
    08:31:42: Drop Id column.
    08:31:42: Add Id column.
    08:31:42: Update Error column in Opportunity_LoadStage7_Result.
    08:31:43: 217966 rows read from SQL Table.
    08:31:43: Job 7501q000000OkilAAC created on salesforce.
    08:31:49: Using the bulkapi with polling every 60 seconds
    08:32:05: Job still running.
    08:32:20: Job still running.
    08:33:20: Job still running.
    08:34:20: Job still running.
    08:35:20: Job still running.
    08:36:20: Job still running.
    08:37:20: Job still running.
    08:38:21: Job still running.
    08:39:21: Job still running.
    08:40:21: Job still running.
    08:41:21: Job still running.
    08:42:21: Job still running.
    08:43:21: Job still running.
    08:44:22: Job still running.
    08:45:22: Job Complete.
    08:45:52: 6185 rows successfully processed.
    08:46:28: 211781 rows failed. See Error column of row for more information.
    08:46:28: Errors occurred. See Error column of row for more information.
    08:46:29: 0 rows unprocessed.
    08:46:29: DBAmpNet2 Operation FAILED.
    08:46:35: Percent Failed = 97.200.
    08:46:35: Error: DBAmpNet2.exe was unsuccessful.
    08:46:35: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsBulk "Upsert" "Opportunity_LoadStage7" "DEVMANSQL01\APPDB03" "SalesforceCroneriMigration_Full" "SFDC_CRONERI_FULLCOPY" "Legacy_System_ID__c" " "
    --- Ending SF_TableLoader. Operation FAILED.
    Msg 50000, Level 16, State 1, Procedure SF_TableLoader, Line 308 [Batch Start Line 7]
    SF_TableLoader Error: 08:31:29: DBAmpNet2 3.6.5.0 (c) Copyright 2015-2017 forceAmp.com LLC08:31:29: Parameters: upsert Opportunity_LoadStage7 DEVMANSQL01\APPDB03 SalesforceCroneriMigration_Full SFDC_CRONERI_FULLCOPY08:31:29: Using the Salesforce bulkapi2 API.08:31:32: Sort column will be used to order input rows.08:31:32: Warning: Column Id ignored because it not creatable in the opportunity object.08:31:32: Warning: Column SQLNote ignored because it does not exist in the opportunity object.08:31:32: Warning: Column DQNote ignored because it does not exist in the opportunity object.08:31:32: Warning: Column DQScore ignored because it does not exist in the opportunity object.08:31:32: Warning: Column MigrationCheck_IsValid ignored because it does not exist in the opportunity object.08:31:32: Warning: Column AGRZR ignored because it does not exist in the opportunity object.08:31:32: Warning: Column payment_method_source ignored because it does not exist in the opportunity object.08:31:32: Warning: Column VBELN ignored because it does not exist in the opportunity object.08:31:32: Warning: Column AUART ignored because it does not exist in the opportunity object.08:31:32: Warning: Column Account_LegacyID ignored because it does not exist in the opportunity object.08:31:32: Warning: Column Sort ignored because it does not exist in the opportunity object.08:31:41: Drop Opportunity_LoadStage7_Result if it exists.08:31:41: Create Opportunity_LoadStage7_Result with new structure.08:31:42: Drop Id column.08:31:42: Add Id column.08:31:42: Update Error column in Opportunity_LoadStage7_Result.08:31:43: 217966 rows read from SQL Table.08:31:43: Job 7501q000000OkilAAC created on salesforce.08:31:49: Using the bulkapi with polling every 60 seconds08:32:05: Job still running.08:32:20: Job still running.08:33:20: Job still running.08:34:20: Job still running.08:35:20: Job still running.08:36:20: Job still running.08:37:20: Job still running.08:38:21: Job still running.08:39:21: Job still running.08:40:21: Job still running.08:4...
    Opportunity_LoadStage7 END: Time taken (ms): 935293

    Thanks,

    Fraser
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Did every record that failed, fail with the same Salesforce error of UNABLE_TO_LOCK_ROW? Also, can I see the create script for the Opportunity_LoadStage7 table?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Yes, failures are only due to unable to lock. I can't provide that as I've overwritten with an attempted resolution since, I'd kept the log in case I needed it.

    Will add script below here. If this is just to see the datatypes/fields, I'll provide screenshots of the output/design table instead if that's easier?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Yes, I just want to see all of the columns and datatypes for those columns in the table.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • ALTER PROC [dbo].[Migration_Transform_Opportunity]
    AS
    BEGIN

    IF EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[Opportunity_LoadStage7]') AND type in (N'U'))
    BEGIN
    DROP TABLE dbo.Opportunity_LoadStage7
    END

    DECLARE @DBNAME varchar(50) = DB_NAME()
    DECLARE @STAGENAME varchar(50)
    DECLARE @RECORDTYPEID_ONEOFF varchar(50)
    DECLARE @RECORDTYPEID_SUBSCRIP varchar(50)
    DECLARE @PRICEBOOKID varchar(50)

    --deployment specific ids.
    SET @STAGENAME =
    CASE @DBNAME
    WHEN 'SalesforceCroneriMigration' THEN 'Implemented'
    WHEN 'SalesforceCroneriMigration_Full' THEN 'Delivery & Billing'
    WHEN 'SalesforceCroneriMigration_Live' THEN 'Closed Won' --LIVE PLACEHOLDER
    END

    SET @RECORDTYPEID_ONEOFF =
    CASE @DBNAME
    WHEN 'SalesforceCroneriMigration' THEN '0120E000000KxbP'
    WHEN 'SalesforceCroneriMigration_Full' THEN '0121q00000006dZ'
    WHEN 'SalesforceCroneriMigration_Live' THEN 'XXX' --LIVE PLACEHOLDER
    END

    SET @RECORDTYPEID_SUBSCRIP =
    CASE @DBNAME
    WHEN 'SalesforceCroneriMigration' THEN '0120E000000KxaW'
    WHEN 'SalesforceCroneriMigration_Full' THEN '0121q00000006da'
    WHEN 'SalesforceCroneriMigration_Live' THEN 'XXX' --LIVE PLACEHOLDER
    END

    --fullcopy
    --one off 0121q00000006dZ
    --sub 0121q00000006da

    --sample
    -- Croner-i Renewal -- 0120E000000KxaW
    -- Croner-i One-off -- 0120E000000KxbP

    SET @PRICEBOOKID =
    CASE @DBNAME
    WHEN 'SalesforceCroneriMigration' THEN '01s0E000000U8z2QAC' --1
    --WHEN 'SalesforceCroneriMigration' THEN '01s0E000000U8z2QAC' --2 --01s0E000000U8z2

    WHEN 'SalesforceCroneriMigration_Full' THEN '01s1q0000004OJP' --wk legacy 01s58000001tciZ ??
    WHEN 'SalesforceCroneriMigration_Live' THEN 'XXX' --LIVE PLACEHOLDER
    END

    SELECT
    --TOP (50) --Uncomment this to speed up during development.
    --Opportunity ID is generated when Opportunity is inserted.

    --DBAmp required columns
    CAST('' as NCHAR(18)) as Id
    ,CAST('' as nvarchar(255)) as Error

    --Internal Data Quality Helpers
    ,CAST('' as nvarchar(255)) as SQLNote --Notes for resolving SQL queries in transform.
    ,CAST('' as nvarchar(255)) as DQNote --Record level note on the next step to improve quality of the record, if possible.
    ,CAST(0 as DECIMAL(10,2)) as DQScore --Arbitrary score used to rank more serious issues higher.

    -- AccountId != ''
    --AND E_billing_Address__c != ''
    --AND Legacy_System_ID__c != ''
    --AND Account_LegacyID != ''

    ,CAST('' as nvarchar(255)) as MigrationCheck_IsValid

    --Foreign Keys (Salesforce IDs of Other Objects)
    ,CAST('' as NVARCHAR(18)) as AccountId

    ,CAST('' as NVARCHAR(18)) as Ship_To_Organisation__c
    ,CAST('' as NVARCHAR(18)) as SBQQ__PrimaryQuote__c
    --,CAST('' as nvarchar(255)) as Student_Id__c

    ,CAST('' as NVARCHAR(18)) as Ship_To_Person__c --Ship To Person
    ,CAST('' as NVARCHAR(18)) as Payer__c --Payer (Account)
    ,CAST('' as NVARCHAR(18)) as Sold_To__c --Sold To (Contact)
    ,CAST('' as NVARCHAR(18)) as Bill_To__c --Bill To (Account)

    ,CAST('' as NVARCHAR(18)) as Bill_To_Contact__c --Bill To (Contact)
    ,CAST('' as NVARCHAR(18)) as Primary_Contact__c --Primary Contact

    --,CAST('' as NVARCHAR(18)) as Product2Id -- Pricebook ID

    --Fixed values:
    , 'SAP' as 'Data_Source__c'
    ,@STAGENAME as 'StageName' --Will be 'Implemented'
    ,'00558000000rn5O' as "OwnerId" --Temporarily using Data Processor account.
    , 'Croner-i' as 'Business_Area__c' --Will be Croner-i
    --, @PRICEBOOKID as Pricebook2 -- Pricebook ID
    , @PRICEBOOKID as Pricebook2Id -- Pricebook ID
    --, @PRICEBOOKID as SBQQ__PriceBook__c -- Pricebook ID
    --, @PRICEBOOKID as SBQQ__PricebookId__c -- Pricebook ID

    --Fixed, temporary: (SAP Expert on site WK Tues).
    --,'Monthly' as 'Billing_Frequency__c' --If DD, monthly.

    ,[VBAK].AGRZR
    ,[VBRK].[ZLSCH] as payment_method_source
    --payment method . zlsch - d = DD, m = credit card, 8 = repeat credit card, blank = invoice.

    ,CAST( ( CASE
    WHEN [VBRK].[ZLSCH] ='D' THEN 'Direct Debit'
    WHEN [VBRK].[ZLSCH] ='M' THEN 'Credit Card'
    WHEN [VBRK].[ZLSCH] ='8' THEN 'Credit Card'
    WHEN [VBRK].[ZLSCH] ='' THEN 'Invoice'
    ELSE 'Invoice'
    END) as varchar(20)) as 'Payment_Method__c'
    --,'Direct Debit' as 'Payment_Method__c'
    ,CAST( ( CASE
    WHEN [VBAK].AGRZR = 0 THEN 'On Publication Date (one-time billing)'
    WHEN [VBAK].AGRZR = 12 and [VBRK].[ZLSCH] ='D' THEN 'Monthly'
    WHEN [VBAK].AGRZR = 12 THEN 'Annual'
    WHEN 'true' = 'false' THEN 'Quarterly' --James not sure if these exist.
    WHEN 'true' = 'false' THEN 'Biannual' --James not sure if these exist.
    ELSE 'On Publication Date (one-time billing)'
    END) as varchar(20)) as 'Billing_Frequency__c'

    --Check record type (sub/one off) ;

    -- (3) Delete Opportunities not Matched to Accounts.
    DELETE FROM Opportunity_LoadStage7
    WHERE [AccountId] IS NULL;

    END
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Is this a one time upsert for these 217,000 rows or are you going to be upserting this amount of records on a regular basis?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • This will be a done a few times as part of a migration project, but isn't a long term integration.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Then rather than take the time to present the input, just force tableloader to run using Soap by changing the operation to upsert:soap. It will run longer but avoid the locking issues.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • I’m thankful
    Excellent, thank you. So loading using (default batch size of 200)?:

    EXEC [dbo].[SF_TableLoader]
    'Upsert:SOAP', -- < -- MODIFIED HERE
    'SFDC_CRONERI_FULLCOPY', --Linked server, connection tested and working.
    'Opportunity_LoadStage7',
    'Legacy_System_ID__c' --External ID.

    As far as I can see, since I'll have disabled processes/triggers/packages, thus no apex running, I shouldn't need to drop batch size?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated