数据管理

数据库数据管理¶

本节介绍SeaboxMPP数据库中的数据管理语言（DML）以及如何插入或更新、删除数据。

并发控制说明¶

SeaboxMPP数据库和SeaboxSQL不为并发控制使用锁。它们使用一种多版本模型来维护数据一致性，即多版本并发控制（MVCC）。MVCC 为每一个数据库会话实现了事务隔离，并且每一个查询事务会看到一个数据的快照。这保证该事务会看到一致的不受其他并发事务影响的数据。

因为MVCC不会为并发控制使用显式锁，锁竞争被最小化并且SeaboxMPP数据库在多用户环境中维持了合理的性能。为查询（读取）数据获得的锁不与为写数据获得的锁冲突。

SeaboxMPP数据库提供了多种锁模式来控制对表中数据的并发访问。大部分SeaboxMPP数据库的SQL命令自动获取适当的锁来确保在命令执行期间被引用的表不会被删除或者被以不兼容的方式被修改。对于不能轻易适应于MVCC行为的应用，可以使用 LOCK LOCK命令来获取显式锁。不过，MVCC的正确使用通常能提供更好的性能。

表 1. SeaboxMPP数据库中的锁模式

锁模式	相关的SQL命令	冲突模式
ACCESS SHARE	SELECT	ACCESS EXCLUSIVE
ROW SHARE	SELECT FOR SHARE, SELECT…FOR UPDATE	EXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVE	INSERT, COPY	SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE UPDATE EXCLUSIVE	VACUUM (without FULL), ANALYZE	SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE	CREATE INDEX	ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE ROW EXCLUSIVE		ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
EXCLUSIVE	DELETE, UPDATE, SELECT…FOR UPDATE	ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
ACCESS EXCLUSIVE	ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL	ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE

注意： SeaboxMPP数据库会为 UPDATE,DELETE, 和 SELECT...FOR UPDATE获取更加严格的EXCLUSIVE锁。(而不是SeaboxSQL中的 ROW EXCLUSIVE) .

当分布式死锁检测开启时，运行在堆表上的 DELETE, UPDATE, 和 SELECT...FOR UPDATE 的锁模式是ROW EXCLUSIVE的。详情请见全局死锁检测.

插入(insert)¶

使用 INSERT 命令在一个表中创建行。这个命令要求该表的名称和表中每一个列的值; 可以选择性地以任意顺序指定列名。

如果没有指定列名，以那些列在表中的顺序列出数据值，用逗号分隔它们。

例如，指定要插入的列名和值：

INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

只指定要插入的值:

INSERT INTO products VALUES (1, 'Cheese', 9.99);

通常，数据值都是常量，但也可以使用标量表达式。例如：

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2016-05-07';

可以在单个命令中插入多行。例如：

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

要插入数据到一个分区表，应指定根分区表，即用 CREATE TABLE 命令创建的表。也可以在一个INSERT命令中指定该分区表的一个叶子子表。如果数据对于指定的叶子子表无效，会返回一个错误。不支持在INSERT 命令中指定一个不是叶子子表的子表。

要插入大量数据，使用外部表或者COPY 命令。对于插入大量行，这些导入机制比INSERT 更加有效。更多有关批量数据导入的信息请见导入和导出数据。导入和导出数据

列存表的存储模型是为批量数据导入而优化。SeaboxMPP不推荐对列存表的单行 INSERT 语句。对于列存表，SeaboxMPP数据库支持最多127个并发INSERT 事务插入到一个列存表。

更新(update)¶

UPDATE 命令在一个表中更新行。可以更新一个表中所有的行、所有行的一个子集或者单个行。可以单独更新每一列而不影响其他列。

要执行一次更新，需要：

要更新的表和列的名称
这些列的新值
指定要更新的行的一个或者更多条件。

例如，下面的命令把所有价格为5 的产品更新为价格为 10:

UPDATE products SET price = 10 WHERE price = 5;

在SeaboxMPP数据库中使用 UPDATE 由下列限制:

如果使用了镜像，不能在UPDATE语句中使用STABLE或VOLATILE函数。

删除(delete)¶

DELETE命令从一个表中删除行。指定一个WHERE子句可以删除满足特定条件的行。如果不指定WHERE子句，该表中所有的行都会被删除。其结果是一个合法的但为空的表。例如，从产品表中删除所有价格为10的行：

DELETE FROM products WHERE price = 10;

要从一个表中删除所有行:

DELETE FROM products;

在SeaboxMPP数据库中使用 DELETE 具有和使用UPDATE类似的限制: 如果使用了镜像，不能在 UPDATE 语句中使用STABLE或VOLATILE 函数。

截断(truncate)¶

使用TRUNCATE命令可以快速地移除一个表中的所有行。例如：

TRUNCATE mytable;

这个命令在一次操作中清空一个表的所有行。注意TRUNCATE 不扫描该表，因此它不会处理继承的子表或者ON DELETE的重写规则。该命令只截断所提到的表中的行。

事务(transaction)¶

事务允许用户把多个SQL语句捆绑在一个要么全做要么全不做的操作中。

下面是SeaboxMPP数据库的SQL事务命令：

BEGIN 或者 START TRANSACTION开始一个事务块。
END 或者 COMMIT 提交一个事务的结果。
ROLLBACK 放弃一个事务而不做任何更改。
SAVEPOINT在一个事务中标记一个位置并且允许做部分回滚。用户可以回滚在一个保存点之后执行的命令但保留该保存点之前执行的命令。
ROLLBACK TO SAVEPOINT回滚一个事务到一个保存点。
RELEASE SAVEPOINT 销毁一个事务内的保存点。

事务隔离级别¶

SeaboxMPP数据库接受下列标准SQL事务级别：

读未提交 和 读已提交 的行为像标准的 读已提交。
可重复读 和 可序列化 的行为像标准的 可重复读。

下列信息描述了SeaboxMPP事务级别的行为：

读已提交/读未提交

SeaboxMPP 数据库不允许任何命令来看其他并发事务的未提交的更新，因此 读未提交 会和 读已提交的行为一样。读已提交提供快速、简单、部分的事务隔离。使用读已提交和读未提交事务隔离， SELECT, UPDATE, 和DELETE事务在一个查询开始时取得的数据库快照上操作。

SELECT 查询:

- 看得见该查询开始前被提交的数据。

- 看得见在该事务内执行的更新。

- 看不见事务外未提交的数据。

- 如果并发事务在该查询所在事务最初的读操作之前就被提交，该查询可能会看到这个并发事务所作的更改。

如果其他并发事务在同一个事务中后续的SELECT查询开始前提交更改，这些查询能够看到不同的数据。 UPDATE 和 DELETE命令只找在该命令开始前提交的行。

读已提交事务隔离允许并发事务在UPDATE 或 DELETE找到行之前修改或者锁定该行。读已提交或读未提交事务隔离可能不适合执行复杂查询和更新并且要求该数据库的一致性视图的应用。

读已提交 事务隔离不适合执行复杂查询和更新并且要求该数据库的一致性视图的应用。

可重复读和可序列化

根据SQL标准规定，可序列化 事务隔离确保在其中事务的并行化执行结果就像一个接一个执行一样。如果指定 可序化时，SeaboxMPP数据库会退而采用可重复读. 可重复读事务能防止脏读、不可重复读和幻读，而且不需要昂贵的锁定，但是在SeaboxMPP数据库的一些SERIALIZABLE事务之间可能发生其他的相互影响而阻止它们变成真正地可序列化。并发运行的事务应该被检查来识别出不会因为不允许对同一数据的并发更新而被阻止的相互影响。

通过使用显式表锁或者要求冲突事务更新一个被引入来表示该冲突的虚拟行可以阻止所发现的问题。

对于 可重复读 事务, SELECT查询:

- 看得到一个事务开始时（不是该事务中当前查询开始时）的数据快照。

- 只看得到在查询开始前被提交的数据。

- 看得到该事务内执行的更新。

- 看不到该事务外部的未提交数据。

- 看不到并发事务所作的更改。

- 一个单一事务中的后续 `SELECT` 命令总是看到相同的数据。

- `UPDATE`, `DELETE`, `SELECT FOR UPDATE`, 和 `SELECT FOR SHARE`命令只会发现在该命令开始前被提交的行。 如果一个目标行被找到时一个并发事务已经更新、删除或者锁定该行，` 可重复读 ` 事 务会等待该并发事务执行或者回滚。 如果并发事务执行变化，那么可重复读事务会回滚。如果`  可重复读 ` 事务回滚，那么可序列化或者可重复读事务执行变化。

SeaboxMPP数据库中的默认事务隔离级别是已提交读。要为一个事务更改隔离级别，在BEGIN该事务时声明隔离级别或者在事务开始后使用SET TRANSACTION命令设置隔离级别。

全局死锁检测¶

SeaboxMPP数据库全局死锁检测器后端进程会收集所有executor上的锁信息，并使用有向算法来检测本地死锁和全局死锁是否存在。

该算法使SeaboxMPP数据库放宽对堆表的并发更新和删除限制。（SeaboxMPP数据库仍然在列存表上使用表级锁定，对UPDATE, DELETE, 和 SELECT...FOR UPDATE的并发操作进行限制。）

默认情况下，全局死锁检测器是被禁用的，SeaboxMPP数据库以串行方式对堆表执行并发更新和删除操作。可以通过设置配置参数sc_enable_global_deadlock_detector，开启并发更新并让全局死锁检测器检测死锁是否存在。

启用全局死锁检测器后，当启动SeaboxMPP数据库时，coordinator 主机上会自动启动一个后端进程。可以通过sc_global_deadlock_detector_period配置参数，来设置采集和分析锁等待数据的时间间隔。

如果全局死锁检测器发现了死锁，它会通过取消最新的事务所关联的一个或多个后端进程来避免死锁。

当全局死锁检测器发现了以下事务类型的死锁时，只有一个事务将成功。其他事务将失败，并打印错误指出不允许对同一行进行并发更新。

在同一行堆表的并发事务中，第一个事务是更新操作，下一个事务执行更新或删除，并且查询计划包含一个动作操作符。
堆表的同一分发键上的并发更新事务由SeaboxMPP Database Seabox查询优化器执行
哈希表的同一行上的并发事务更新由优化器执行。

注意: SeaboxMPP数据库通过配置参数deadlock_timeout指定本地死锁检测的间隔。由于本地死锁检测和全局死锁检测算法的不同，被死锁检测器终止的进程也不同，这取决于本地死锁检测和全局死锁检测哪个先被触发。

注意: 若打开配置参数 lock_timeout，且将数值设定为小于deadlock_timeout 和sc_global_deadlock_detector_period,查询会在死锁检测被触发之前就被终止。

用户可以通过自定义函数，sc_dist_wait_status()查看所有executor上的锁等待信息：哪些事务在等待锁，哪些事务在持有锁，锁的类型和模式，等待和持有锁的会话标识，以及事务运行在哪个executor上。

sc_dist_wait_status() 的输出如下例所示:

SELECT * FROM pg_catalog.sc_dist_wait_status();
-[ RECORD 1 ]----+--------------
segid            | 0
waiter_dxid      | 11
holder_dxid      | 12
holdTillEndXact  | t
waiter_lpid      | 31249
holder_lpid      | 31458
waiter_lockmode  | ShareLock
waiter_locktype  | transactionid
waiter_sessionid | 8
holder_sessionid | 9
-[ RECORD 2 ]----+--------------
segid            | 1
waiter_dxid      | 12
holder_dxid      | 11
holdTillEndXact  | t
waiter_lpid      | 31467
holder_lpid      | 31250
waiter_lockmode  | ShareLock
waiter_locktype  | transactionid
waiter_sessionid | 9
holder_sessionid | 8

全局死锁检测器会终止某个事务以打破死锁，并且打印如下的错误信息：

ERROR:  canceling statement due to user request: "cancelled by global deadlock detector"

全局死锁检测器UPDATE和DELETE兼容性

全局死锁检测器可以管理堆表上这些类型的UPDATE和DELETE命令的并发更新：

- 单个表的简单更新。使用Seabox优化器更新非分布键。该命令不包含`FROM`子句或`WHERE`子句中的子查询。

  ``` sql
  UPDATE t SET c2 = c2 + 1 WHERE c1 > 10;
  ```

- 简单删除单个表。该命令在`FROM`或`WHERE`子句中不包含子查询。

  ``` sql
  DELETE FROM t WHERE c1 > 10;
  ```

- 分割`UPDATE`。对于Seabox优化器，`UPDATE`命令更新分布键。

  ``` sql
  UPDATE t SET c = c + 1; -- c is a distribution key
  ```

- 复杂的`UPDATE`。`UPDATE`命令包括多个表联接。

  ``` sql
  UPDATE t1 SET c = t1.c+1 FROM t2 WHERE t1.c = t2.c;
  ```

  或者该命令在`WHERE`子句中包含一个子查询。

  ``` sql
  UPDATE t SET c = c + 1 WHERE c > ALL(SELECT * FROM t1);
  ```

- 复杂的`DELETE`。复杂的`DELETE`命令类似于复杂的`UPDATE`，并且涉及多个表联接或子查询。

  ``` sql
  DELETE FROM t USING t1 WHERE t.c > t1.c;
  ```

下表显示了由全局死锁检测器处理的并发UPDATE或DELETE命令。

例如，同一表行上的并发简单UPDATE命令由全局死锁检测器管理。

对于并发的复杂UPDATE和简单UPDATE，仅执行一个UPDATE，并为另一个UPDATE返回错误。

表 2. 由全局死锁检测器管理的并发更新和删除

	简单`UPDATE`	简单`DELETE`	分裂`UPDATE`	复杂`UPDATE`	复杂`DELETE`
简单`UPDATE`	YES	YES	NO	NO	NO
简单`DELETE`	YES	YES	NO	YES	YES
分裂`UPDATE`	NO	NO	NO	NO	NO
复杂`UPDATE`	NO	YES	NO	NO	NO
复杂`DELETE`	NO	YES	NO	NO	YES

清理数据库¶

虽然新事务看不到被删除或者被更新的数据行，但是它们仍然在磁盘上占用物理空间。周期性地运行VACUUM命令可以移除这些过期的行。例如：

VACUUM mytable;

VACUUM 命令会收集表级别的统计信息，例如行数和页数。在导入数据后清理所有的表，包括列存表。有关推荐的例行清理操作的信息，请见例行清理和分析.

重要: 如果在数据库数据上频繁地执行更新和删除，VACUUM,VACUUM FULL, 和 VACUUM ANALYZE命令应该被用来维护SeaboxMPP数据库中的数据。有关使用 VACUUM命令的信息请见SeaboxMPP数据库参考指南。

耗尽锁¶

当数据库操作在单个事务中访问多个表时，SeaboxMPP数据库可能会耗尽锁。备份和还原就是此类操作的示例。

当SeaboxMPP数据库的锁用完时，可能会看到的错误消息引用了共享内存错误：

... "WARNING","53200","out of shared memory",,,,,,"LOCK TABLE ...
... "ERROR","53200","out of shared memory",,"You might need to increase max_locks_per_transaction.",,,,"LOCK TABLE ...

注意在本文中，shared memory是指内部对象的共享内存：锁插槽。 Out of shared memory不表示系统或SeaboxMPP级别的内存资源已耗尽。

如提示所述，请在遇到此错误时考虑增加max_locks_per_transaction服务器配置参数。