GaussDB(DWS)中UPSERT特性用法总结

GaussDB(DWS)中UPSERT特性用法总结

 1 简介

  在关系型数据库中，upsert是一个组合词，即当往表中插入记录时，如果记录已经存在则更新或忽略，否则插入新纪录，更新的策略需要按照更新指定的表达式计算结果，而为了使用该特性需要使用相应语法。本文主要介绍了GaussDB(DWS)中UPSERT的用法、语法约束和行列存的区别等。 

2 语法介绍

  GaussDB(DWS)的Upsert特性支持PG和MySQL两种语法，具体语法差异如下图所示。

  注：GaussDB(DWS)只支持唯一或者主键约束冲突忽略。

2.1 MySql语法

  冲突忽略语法：INSERT IGNORE INTO。

  冲突更新语法：INSERT INTO ON DUPLICATE KEY UPDATE。 

• UPDATE字句中的values，表示因冲突而本该排除的数据行中某一列的值，里面只能是列名、复合类型的某一列。
• UPDATE的表达式目前只支持+-*/%双目运算符、+-单目运算符、TYPECAST、比较操作符。values(c) 外层不能添加函数。如果需要，使用c形式。（见2.2 PG语法）。

2.2 PG语法

  冲突忽略语法：INSERT INTO ON CONFLICT DO NOTHING。

  冲突更新语法：INSERT INTO ON CONFLICT DO UPDATE SET。

• UPDATE 子句中表达式支持：

1. DEFAULT，表是该列的DEFAULT值，如果没有指定则为NULL。
2. 常量或带有表达式的常量，如100，upper(‘a’)。
3. 带有表达式的表目标列，如upper(colName), upper(table_name. colName)。
4. 以上两种构成的表达式语句。

• EXCLUDED与MySQL语法中values功能相同，不同的是EXCLUDED支持表达式。支持EXCLUDED引用列或带有表达式的EXCLUDED引用列，如 colName, upper(EXCLUDED. colName)。EXCLUDED. colName表示该行对应的column_name列的值。
• CONFLICT[colName] 可以指定含有唯一约束的列名,因此包含colName的唯一索引（不管顺序）都会被推断为arbiter index，并用于冲突更新。如果推断不成功，则报错。
• 也通过CONFLICT ON CONSTRAINT unique_index_name 指定含有唯一约束的索引名,如索引不含有唯一约束或不是插入table的索引则报错。

• 执行策略冲突或更行。当做DO NOTHING冲突忽略时，CONFLICT后可以不指定唯一索引，如果有多个约束，当其中一个约束冲突，插入就会忽略。当做DO UPDATE时，CONFLICT后必须指定唯一索引。

3 行列存upsert区别

  在GaussDB中行列存的UPSERT的实现方式是不一样，行存是先检测冲突再插入，列存是先插入再检测冲突。因此理论上来说，在相同场景下，若插入的数据冲突率较高，行存的UPSERT性能优于列存，若冲突率较低，则列存的UPSERT性能更好（注意：由于列存结构的特殊性，当在列存表上执行UPSERT时，建议开启DELTA表，若DELTA表关闭会导致并发性能较差和空间膨胀的问题）。

4 已知死锁场景和解决办法

  若业务中有大并发和高冲突率的upsert业务场景时，有可能出现分布式死锁，具体原理如下图：

  当T1向DN1中插入了数据1，T2向DN2中插入了数据2时，若T1向DN2中插入数据2，需要等T1事务结束，此时当T2向DN1插入数据1时，要等T1事务结束，因此发生分布式死锁。

  该类死锁场景，在PG上也同样存在，属于已知场景，若发生在单节点上会被检测到并解锁，但若发生在多节点上便会产生分布式死锁，由于目前GaussDB没有分布式死锁自动检测并解锁的工具，因此会影响业务性能。若存在该业务场景且发现插入性能明显变差时，可通过pgxc_deadlock视图检测是否出现分布式死锁，如下图：

  若发现产生分布式死锁，可采取以下方法：

(1)业务端避免大量重复数据的插入，比如去重

(2)减小锁超时的时间。