Post reply

Deadlock occurred while trying to perform multiple inserts into the same table

  • December 5, 2021 at 12:13 pm

    #3960562

    We wrote the script to split inserting data the same table. There is an error occurred on one of the session as "ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 185) was deadlocked on lock

    resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    So, when we looked at the deadlock report. The information show as below.

    Could anyone give an advice how to resolve this problem?

    Thank you very much.

     

    *************************************************************************************

    <deadlock>

    <victim-list>

    <victimProcess id="process4a06451868" />

    </victim-list>

    <process-list>

    <process id="process4a06451868" taskpriority="0" logused="3209218004" waitresource="KEY: 5:72057595689959424 (f4b15d32f47d)" waittime="5407149" ownerId="29732226989" transactionname="INSERT" lasttranstarted="2021-12-04T22:13:16.267" XDES="0x103d4656460" lockMode="X" schedulerid="3" kpid="12692" status="suspended" spid="185" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-04T22:13:16.167" lastbatchcompleted="2021-12-04T22:13:16.167" lastattention="1900-01-01T00:00:00.167" hostpid="9320" loginname="FCFCORE" isolationlevel="read committed (2)" xactid="29732226989" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

    <executionStack>

    <frame procname="adhoc" line="1" sqlhandle="0x02000000d1697f3ba67ddcf174e9aaa7fcbabf658513a5910000000000000000000000000000000000000000">

    insert into fcfcore.fsc_cash_flow_fact select stg_trans_type_pre_load.transaction_key, stg_trans_type_pre_load.segment_id, stg_trans_type_pre_load.account_key, stg_trans_type_pre_load.country_key, stg_trans_type_pre_load.transaction_type_key, stg_trans_type_pre_load.time_key, stg_trans_type_pre_load.date_key, stg_trans_type_pre_load.transaction_status_key, stg_trans_type_pre_load.transaction_currency_key, stg_trans_type_pre_load.branch_key, stg_trans_type_pre_load.remitter_ext_party_key, stg_trans_type_pre_load.beneficiary_ext_party_key, stg_trans_type_pre_load.posted_date_key, stg_trans_type_pre_load.associate_key, stg_trans_type_pre_load.executing_party_key, stg_trans_type_pre_load.executing_ext_party_key, stg_trans_type_pre_load.currency_amount, stg_trans_type_pre_load.currency_amount_in_txn_ccy, stg_trans_type_pre_load.currency_amount_in_account_ccy, stg_trans_type_pre_load.secondary_account_key, stg_trans_type_pre_load.related_ind, stg_trans_type_pre_load.third_party_ind, stg_trans_type_pre_load.x_relate </frame>

    </executionStack>

    <inputbuf>

    insert into fcfcore.fsc_cash_flow_fact select stg_trans_type_pre_load.transaction_key, stg_trans_type_pre_load.segment_id, stg_trans_type_pre_load.account_key, stg_trans_type_pre_load.country_key, stg_trans_type_pre_load.transaction_type_key, stg_trans_type_pre_load.time_key, stg_trans_type_pre_load.date_key, stg_trans_type_pre_load.transaction_status_key, stg_trans_type_pre_load.transaction_currency_key, stg_trans_type_pre_load.branch_key, stg_trans_type_pre_load.remitter_ext_party_key, stg_trans_type_pre_load.beneficiary_ext_party_key, stg_trans_type_pre_load.posted_date_key, stg_trans_type_pre_load.associate_key, stg_trans_type_pre_load.executing_party_key, stg_trans_type_pre_load.executing_ext_party_key, stg_trans_type_pre_load.currency_amount, stg_trans_type_pre_load.currency_amount_in_txn_ccy, stg_trans_type_pre_load.currency_amount_in_account_ccy, stg_trans_type_pre_load.secondary_account_key, stg_trans_type_pre_load.related_ind, stg_trans_type_pre_load.third_party_ind, stg_trans_type_pre_load.x_relat </inputbuf>

    </process>

    <process id="process2ec732c188" taskpriority="0" logused="6268377280" waitresource="KEY: 5:72057595689959424 (7db1dde2c26a)" waittime="108" ownerId="29732226131" transactionname="INSERT" lasttranstarted="2021-12-04T22:13:14.900" XDES="0xf51a8cdc20" lockMode="X" schedulerid="19" kpid="9480" status="suspended" spid="176" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-04T22:13:14.750" lastbatchcompleted="2021-12-04T22:13:14.747" lastattention="1900-01-01T00:00:00.747" hostpid="7740" loginname="FCFCORE" isolationlevel="read committed (2)" xactid="29732226131" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

    <executionStack>

    <frame procname="adhoc" line="1" sqlhandle="0x020000006e93051b769a29b12f712fc67e887bb5d66801bd0000000000000000000000000000000000000000">

    insert into fcfcore.fsc_cash_flow_fact select stg_trans_type_pre_load.transaction_key, stg_trans_type_pre_load.segment_id, stg_trans_type_pre_load.account_key, stg_trans_type_pre_load.country_key, stg_trans_type_pre_load.transaction </frame>

    </executionStack>

    <inputbuf>

    insert into fcfcore.fsc_cash_flow_fact select stg_trans_type_pre_load.transaction_key, stg_trans_type_pre_load.segment_id, stg_trans_type_pre_load.account_key, stg_trans_type_pre_load.country_key, stg_trans_type_pre_load.transaction_ </inputbuf>

    </process>

    </process-list>

    <resource-list>

    <keylock hobtid="72057595689959424" dbid="5" objectname="fcf61.FCFCORE.FSC_CASH_FLOW_FACT" indexname="XIF12FSC_CASH_FLOW_FACT" id="lock12a03a63380" mode="X" associatedObjectId="72057595689959424">

    <owner-list>

    <owner id="process2ec732c188" mode="X" />

    </owner-list>

    <waiter-list>

    <waiter id="process4a06451868" mode="X" requestType="wait" />

    </waiter-list>

    </keylock>

    <keylock hobtid="72057595689959424" dbid="5" objectname="fcf61.FCFCORE.FSC_CASH_FLOW_FACT" indexname="XIF12FSC_CASH_FLOW_FACT" id="lock36afe89f00" mode="X" associatedObjectId="72057595689959424">

    <owner-list>

    <owner id="process4a06451868" mode="X" />

    </owner-list>

    <waiter-list>

    <waiter id="process2ec732c188" mode="X" requestType="wait" />

    </waiter-list>

    </keylock>

    </resource-list>

    </deadlock>

  • December 5, 2021 at 4:45 pm

    #3960588

    aey wrote:

    We wrote the script to split inserting data the same table.

    We probably need to see that script.

    I also cringe a bit when I see that people wrote a script to "split inserting data the same table".  My question would be WHY?  Did you do this thinking that you might improve the performance of inserting a large set of data?  And is each occurrence inserting one row at a time or a set of rows?  I can't tell from what's in the deadlock graph.

    It would also be nice for you to tell us the "ranges" of related data in the target table compared to the ranges in the "source" and to identify what the "source" of the data actually is.  Right now, we have just enough from you to say, yep... you have a deadlock issue.

    You might want to have a look at the article at the second link in my signature below.  While deadlocks and performance issues seem to have nothing to do with each other for a lot of people, they're actually quite related.  The more you can tell us, the better we can help.  Right now, all we have to go on is an incomplete couple of snippets of code from the deadlock graph.

    In any case, it's REALLY important for you to post the following...

    1. The full create table statement for the fcf61.FCFCORE.FSC_CASH_FLOW_FACT table including ALL constraints and indexes.
    2. An actual execution plan of a successful run of the "script" that runs.  This needs to be stored and posted like the article says so that we can look at the actual execution plan as if we were logged into your machine.  A "picture" isn't going to do squat for us to help you.

    And, yeah... considering the name of the tables having the word "fact" in them, I suspect that you tried to speed up what most refer to as an "ETL" to update a data warehouse.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 6, 2021 at 5:19 am

    #3960675

    Hi Jeff,

    Thanks for your reply. The thought of splitting process to insert data into the same table is request from our client's DBA. Currently, the source input data is about 24 million records everyday and it took about 1.30 -3.30 to just insert this as one batch while the target table(FSC_CASH_FLOW_FACT) will retain at least 200 million records.

    So, we are thinking to split the process by dividing data into small chunks(around 2 millions per process) and loading parallelly into the same tables. We ran this many days without any error.

    This deadlock as I noticed seemed to occur around 2 or 3 o'clock at night. I still couldn't conclude this yet since the deadlock just happened only twice after we ran more than 10 times.

    One more thing is about information in the deadlock report. I noticed that it is conflicted with index

    <keylock hobtid="72057595689959424" dbid="5" objectname="fcf61.FCFCORE.FSC_CASH_FLOW_FACT" indexname="XIF12FSC_CASH_FLOW_FACT" id="lock36afe89f00" mode="X" associatedObjectId="72057595689959424">

    So, I also asked my client to give me details of this index as the attachment. Would you mind also suggest whether its config cause the problem or not. Any advice to resolve this problem?

    16387248520921638723892796

    The following are information you request:-

    1. The script that we used to insert for each process

    *************************************************************

    insert into fcfcore.fsc_cash_flow_fact

    select stg_trans_type_pre_load.transaction_key,

    stg_trans_type_pre_load.segment_id,

    stg_trans_type_pre_load.account_key,

    stg_trans_type_pre_load.country_key,

    stg_trans_type_pre_load.transaction_type_key,

    stg_trans_type_pre_load.time_key,

    stg_trans_type_pre_load.date_key,

    stg_trans_type_pre_load.transaction_status_key,

    stg_trans_type_pre_load.transaction_currency_key,

    stg_trans_type_pre_load.branch_key,

    stg_trans_type_pre_load.remitter_ext_party_key,

    stg_trans_type_pre_load.beneficiary_ext_party_key,

    stg_trans_type_pre_load.posted_date_key,

    stg_trans_type_pre_load.associate_key,

    stg_trans_type_pre_load.executing_party_key,

    stg_trans_type_pre_load.executing_ext_party_key,

    stg_trans_type_pre_load.currency_amount,

    stg_trans_type_pre_load.currency_amount_in_txn_ccy,

    stg_trans_type_pre_load.currency_amount_in_account_ccy,

    stg_trans_type_pre_load.secondary_account_key,

    stg_trans_type_pre_load.related_ind,

    stg_trans_type_pre_load.third_party_ind,

    stg_trans_type_pre_load.x_related_transaction_number,

    stg_trans_type_pre_load.x_control_bank_code,

    stg_trans_type_pre_load.x_control_source_id,

    stg_trans_type_pre_load.x_transaction_reference_number,

    stg_trans_type_pre_load.x_rr_relationship,

    stg_trans_type_pre_load.x_rr_ref_no_for_report,

    stg_trans_type_pre_load.x_rr_objective_type,

    stg_trans_type_pre_load.x_rr_objective_desc,

    stg_trans_type_pre_load.x_rr_reference_number,

    stg_trans_type_pre_load.x_rr_exchange_rate,

    stg_trans_type_pre_load.x_rr_remark

    from (

    select

    stg_trans_type_pre_load_1.transaction_key,

    stg_trans_type_pre_load_1.segment_id,

    stg_trans_type_pre_load_1.account_key,

    stg_trans_type_pre_load_1.country_key,

    stg_trans_type_pre_load_1.transaction_type_key,

    stg_trans_type_pre_load_1.time_key,

    stg_trans_type_pre_load_1.date_key,

    stg_trans_type_pre_load_1.transaction_status_key,

    stg_trans_type_pre_load_1.transaction_currency_key,

    stg_trans_type_pre_load_1.branch_key,

    stg_trans_type_pre_load_1.remitter_ext_party_key,

    stg_trans_type_pre_load_1.beneficiary_ext_party_key,

    stg_trans_type_pre_load_1.posted_date_key,

    stg_trans_type_pre_load_1.associate_key,

    stg_trans_type_pre_load_1.executing_party_key,

    stg_trans_type_pre_load_1.executing_ext_party_key,

    stg_trans_type_pre_load_1.currency_amount,

    stg_trans_type_pre_load_1.currency_amount_in_txn_ccy,

    stg_trans_type_pre_load_1.currency_amount_in_account_ccy,

    stg_trans_type_pre_load_1.secondary_account_key,

    stg_trans_type_pre_load_1.related_ind,

    stg_trans_type_pre_load_1.third_party_ind,

    stg_trans_type_pre_load_1.x_related_transaction_number,

    stg_trans_type_pre_load_1.x_control_bank_code,

    stg_trans_type_pre_load_1.x_control_source_id,

    stg_trans_type_pre_load_1.transaction_reference_number as x_transaction_reference_number,

    stg_trans_type_pre_load_1.x_rr_relationship,

    stg_trans_type_pre_load_1.x_rr_ref_no_for_report,

    stg_trans_type_pre_load_1.x_rr_objective_type,

    stg_trans_type_pre_load_1.x_rr_objective_desc,

    stg_trans_type_pre_load_1.x_rr_reference_number,

    stg_trans_type_pre_load_1.x_rr_exchange_rate,

    stg_trans_type_pre_load_1.x_rr_remark,

    row_number() over (order by transaction_key) as record_num

    from stg.stg.stg_cash_flow_fact_pre_load as stg_trans_type_pre_load_1

    where record_num between &rn_start. and &rn_en

    ) as stg_trans_type_pre_load

    where stg_trans_type_pre_load.record_num between &rn_start. and &rn_end.

     

  • December 6, 2021 at 6:30 am

    #3960693

    xml_Deadlock_Graph

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply