Update Table Based on concurrent join

Question

I have a below query which runs perfectly but then i run it on the complete data the Table (Export_SKU_Data_v2) get's locked. The lock is Row-X lock. Surprisingly the count is only somewhere around 30,000 rows and i don't think the issue is with performance. The issue is definitely with the query.

update Export_SKU_Data_v2 tgt
    set tgt.seo_url
    = (select src.seo_url--,src.sku_id,src.site_id
            from (
  WITH prd_sites AS 
   (   
          select wo.Product_id, WO.Site_id
           FROM
             (select decode(substr(translation_id, 1, 1), '2', 'BuyBuyCaby', '3', 'CedCathCanada', null) site_id,
                                product_id
                              from bbb_prod_site_translations
                              where translation_id in ('2_en_US_webOffered_Y', '3_en_US_webOffered_Y')
               )wo,  
              (select decode(substr(translation_id, 1, 1), '2', 'BuyBuyCaby', '3', 'BedCathCanada', null) site_id, 
                                          product_id
                                        from bbb_prod_site_translations
                                        where translation_id in ('2_en_US_prodDisable_N', '3_en_US_prodDisable_N')                                  
                )da                              
              Where wo.product_id = da.product_id and wo.site_id = da.site_id              
              UNION
              Select Product_id, 'BedCathUS' from bbb_product
              where web_offered_flag= 1 and disable_flag = 0
   )select distinct sku_id,site_id,seo_url as seo_url from (                      
                    select k.sku_id,bp.product_id,bp.web_offered_flag,bp.disable_flag,bp.seo_url,site_id,
                    dense_rank() over(partition by k.sku_id,site_id order by bp.product_id desc) as rnk
                    from (
                    select bs.sku_id,count(*) 
                    from bbb_sku bs
                    inner join DCS_PRD_CHLDSKU dpcs on bs.sku_id=dpcs.sku_id
                    group by bs.sku_id
                    having count(*)>1
                    ) k inner join DCS_PRD_CHLDSKU dpcs1 on k.sku_id=dpcs1.sku_id
                    inner join bbb_product bp on dpcs1.product_id=bp.product_id
          inner join prd_sites pst on bp.product_id=pst.product_id
                    where bp.seo_url is not null
                    ) tab where rnk=1
    )src where tgt.sku_id = src.sku_id and tgt.site_id=src.site_id )
    where tgt.seo_url is null ;--and tgt.sku_id='10920540';

Note : Please allow me to ask if this is the correct way to update a table based on the above logic. The logic is pretty simple and i am not able to find where i'm screwing it up.

I'm not sure what you mean by the issue being with the query but not performance, or what locking has to do with it. You would expect to see locking when updating a table, so that sounds normal. — William Robertson, Jun 05 '18 at 14:01
Please formulate clear, what is your problem. Try to simplify your example while keeping the behaviour. Provide more information as described [here](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) including the used execution plan, cardinality of the tables and the expected update count. — Marmite Bomber, Jun 05 '18 at 14:03

score 0 · Answer 1 · answered Jun 05 '18 at 15:45

This update command:

   update Export_SKU_Data_v2 tgt
        set tgt.seo_url
    .....
     where tgt.seo_url is null ;

automatically acquire two kind of locks:

A row share lock (RS) -also called a subshare table lock (SS) - at table level
row locks, also called a TX locks, on all rows which are being update by the command, that is all rows that meet the condition: where tgt.seo_url is null

This is not an issue - this is a normal and expected behavior.
You can find more on this topic in the documentation: Automatic Locks in DML Operations

Update Table Based on concurrent join

1 Answers1