forked from docs/doc-exports
luhuayi
177cd61a57
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com> Co-authored-by: luhuayi <luhuayi@huawei.com> Co-committed-by: luhuayi <luhuayi@huawei.com>
23 lines
4.9 KiB
HTML
23 lines
4.9 KiB
HTML
<a name="EN-US_TOPIC_0000002100586270"></a><a name="EN-US_TOPIC_0000002100586270"></a>
|
|
|
|
<h1 class="topictitle1">UPDATE and DELETE Operations</h1>
|
|
<div id="body8662426"><div class="section" id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_section63531734104519"><h4 class="sectiontitle">Suggestion 3.5: Preventing Simultaneous Updates or Deletions of the Same Row in a Row-store Table</h4><div class="note" id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_note4102113972211"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><p id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_p3291449204611"><strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b171941378610741">Impact of rule violation:</strong></p>
|
|
<ul id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_ul18760115410018"><li id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_li1876013545015">Concurrent <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b1296282515176">UPDATE</strong> and <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b2256182841716">DELETE</strong> operations on row-store tables may cause row lock blockage and distributed deadlocks, which can lead to service errors and performance degradation.</li></ul>
|
|
<p id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_p1929111496465"><strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b1022151596112629">Solution:</strong></p>
|
|
<ul id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_ul449122984813"><li id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_li1549329184816">Group <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b1542433819571">UPDATE</strong> and <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b10425103815714">DELETE</strong> operations by primary key or distribution column. Perform parallel operations between groups while keeping operations within a group serial.</li></ul>
|
|
</div></div>
|
|
</div>
|
|
<div class="section" id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_section6619155554517"><h4 class="sectiontitle">Suggestion 3.6: Avoiding Frequent or Simultaneous UPDATE and DELETE Operations on Column-store Tables</h4><div class="note" id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_note171881112125813"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><p id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_p1418881295812"><strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b132561529310741">Impact of rule violation:</strong></p>
|
|
<ul id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_ul1011962534414"><li id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_li14119425184419">Frequent <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b240418371227">UPDATE</strong> and <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b410234018227">DELETE</strong> operations on column-store tables can result in CU bloat, leading to large space occupation and decreased access performance.</li><li id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_li138141751205018">Concurrent <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b1061115682515">UPDATE</strong> and <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b1261118620254">DELETE</strong> operations on row-store tables may cause row lock blockage and distributed deadlocks, which can lead to service errors and performance degradation.</li></ul>
|
|
<p id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_p10188141245817"><strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b629107949112629">Solution:</strong></p>
|
|
<ul id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_ul1173125635215"><li id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_li77841258105210">Design tables with frequent <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b10448173982410">UPDATE</strong> and <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b19330104116241">DELETE</strong> operations as row-store tables.</li><li id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_li473105619526">Group <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b78280533575">UPDATE</strong> and <strong id="EN-US_TOPIC_0000002100586270__en-us_topic_0000002100207566_b182885325719">DELETE</strong> operations by primary key or distribution column. Perform parallel operations between groups while keeping operations within a group serial.</li></ul>
|
|
</div></div>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="dws_04_0105.html">GaussDB(DWS) SQL Statement Development Specifications</a></div>
|
|
</div>
|
|
</div>
|
|
|