插入, 更新, 和删除数据
插入, 更新, 和删除数据
这一节提供了Greenplum数据库中有关操纵数据和并发访问的信息。
关于Greenplum数据库中的并发控制
Greenplum数据库和PostgreSQL不为并发控制使用锁。它们使用一种多版本模型来维护数据一致性，即多版本并发控制（MVCC）。 MVCC 为每一个数据库会话实现了事务隔离，并且每一个查询事务会看到一个数据的快照。 这保证该事务会看到一致的不受其他并发事务影响的数据。
因为MVCC不会为并发控制使用显式锁，锁竞争被最小化并且Greenplum数据库在多用户环境中维持了合理的性能。为查询（读取）数据获得的 锁不与为写数据获得的锁冲突。
Greenplum数据库提供了多种锁模式来控制对表中数据的并发访问。大部分Greenplum数据库的SQL命令自动获取适当的锁来确保在命令执行期间 被引用的表不会被删除或者被以不兼容的方式被修改。对于不能轻易适应于MVCC行为的应用，可以使用 LOCK LOCK命令来获取显式锁。 不过，MVCC的正确使用通常能提供更好的性能。
| 锁模式 | 相关的SQL命令 | 冲突模式 |
|---|---|---|
| ACCESS SHARE | SELECT | ACCESS EXCLUSIVE |
| ROW SHARE | SELECT FOR SHARE, SELECT...FOR UPDATE | EXCLUSIVE, ACCESS EXCLUSIVE |
| ROW EXCLUSIVE |
INSERT, COPY See Note. |
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 See Note. |
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 |
当分布式死锁检测开启时，运行在堆表上的 DELETE, UPDATE, 和 SELECT...FOR UPDATE 的锁模式是 ROW EXCLUSIVE的。 详情请见 全局死锁检测.
插入行
使用 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 更加有效。 更多有关批量数据装载的信息请见装载和卸载数据。装载和卸载数据
追加优化表的存储模型是为批量数据装载而优化。Greenplum不推荐对追加优化表的单行 INSERT 语句。对于追加优化表， Greenplum数据库支持最多127个并发INSERT 事务插入到一个追加优化表。
更新现有行
UPDATE 命令在一个表中更新行。可以更新一个表中所有的行、所有行的一个子集或者单个行。可以单独更新每一列而不影响其他列。
要执行一次更新，需要：
- 要更新的表和列的名称
- 这些列的新值
- 指定要更新的行的一个或者更多条件。
例如，下面的命令把所有价格为5 的产品更新为价格为 10:
UPDATE products SET price = 10 WHERE price = 5;
在Greenplum数据库中使用 UPDATE 由下列限制:
- GPORCA可以为Greenplum分布键列提供更新支持，Postgres planner则不会 。
- 如果使用了镜像，不能在UPDATE语句中使用STABLE或VOLATILE 函数。
- Greenplum数据库的分区列不能被更新.
删除行
DELETE命令从一个表中删除行。指定一个WHERE子句可以删除满足特定条件的行。 如果不指定WHERE 子句，该表中所有的行都会被删除。 其结果是一个合法的但为空的表。例如，从产品表中删除所有价格为10的行：
DELETE FROM products WHERE price = 10;
要从一个表中删除所有行:
DELETE FROM products;
在Greenplum数据库中使用 DELETE 具有和使用UPDATE类似的限制:
- 如果使用了镜像，不能在 UPDATE 语句中使用STABLE 或VOLATILE 函数。
截断一个表
使用TRUNCATE命令可以快速地移除一个表中的所有行。例如：
TRUNCATE mytable;
这个命令在一次操作中清空一个表的所有行。注意TRUNCATE 不扫描该表，因此它不会处理继承的子表或者ON DELETE 的重写规则。该命令只截断所提到的表中的行。
使用事务
事务允许用户把多个SQL语句捆绑在一个要么全做要么全不做的操作中。
下面是Greenplum数据库的SQL事务命令：
- BEGIN 或者 START TRANSACTION 开始一个事务块。
- END 或者 COMMIT 提交一个事务的结果。
- ROLLBACK 放弃一个事务而不做任何更改。
- SAVEPOINT 在一个事务中标记一个位置并且允许做部分回滚。用户可以回滚在一个保存点之后执行的命令但保留该保存点之前执行的命令。
- ROLLBACK TO SAVEPOINT 回滚一个事务到一个保存点。
- RELEASE SAVEPOINT 销毁一个事务内的保存点。
事务隔离级别
Greenplum数据库接受下列标准SQL事务级别：
- 读未提交 和 读已提交 的行为像标准的 读已提交.
- 可重复读 和 可序列化 的行为像behave like 可重复读.
下列信息描述了Greenplum事务级别的行为：
读已提交/读未提交
Greenplum 数据库不允许任何命令来看其他并发事务的未提交的更新，因此 读未提交 会和 读已提交的行为一样。读已提交 提供快速、简单、部分的事务隔离。使用读已提交和读未提交事务隔离， SELECT, UPDATE, 和DELETE 事务在一个查询开始时取得的数据库快照上操作。
一个 SELECT 查询:
- 看得见该查询开始前被提交的数据。
- 看得见在该事务内执行的更新。
- 看不见事务外未提交的数据。
- 如果并发事务在该查询所在事务最初的读操作之前就被提交，该查询可能会看到这个并发事务所作的更改。
如果其他并发事务在同一个事务中后续的SELECT查询开始前提交更改，这些查询能够看到不同的数据。 UPDATE 和 DELETE 命令只找在该命令开始前提交的行。
读已提交事务隔离允许并发事务在UPDATE 或 DELETE找到行之前修改或者锁定该行。 读已提交或读未提交事务隔离可能不适合执行复杂查询和更新并且要求该数据库的一致性视图的应用。 读已提交 事务隔离可能不适合执行 复杂查询和更新并且要求该数据库的一致性视图的应用。
可重复读和可序列化
根据SQL标准规定，可序列化 事务隔离确保在其中事务的并行化执行结果就像一个接一个执行一样。 如果指定 可序化时， Greenplum数据库会退而采用可重复读. 可重复读事务能防止脏读、不可重复读和幻读，而且不需要昂贵的锁定， 但是在Greenplum数据库的一些SERIALIZABLE事务之间可能发生其他的相互影响而阻止它们变成真正地可序列化。 并发运行的事务应该被检查来识别出不会因为不允许对同一数据的并发更新而被阻止的相互影响。 通过使用显式表锁或者要求冲突事务更新一个被引入来表示该冲突的虚拟行可以阻止所发现的问题。
对于 可重复读 事务, 一个 SELECT查询:
- 看得到一个事务开始时（不是该事务中当前查询开始时）的数据快照。
- 只看得到在查询开始前被提交的数据。
- 看得到该事务内执行的更新。
- 看不到该事务外部的未提交数据。
- 看不到并发事务所作的更改。
- 一个单一事务中的后续 SELECT 命令总是看到相同的数据。
- UPDATE, DELETE, SELECT FOR UPDATE, 和 SELECT FOR SHARE 命令只会发现在该命令开始前被提交的行。 如果一个目标行被找到时一个并发事务已经更新、删除或者锁定该行， 可重复读 事 务会等待该并发事务执行或者回滚。 如果并发事务执行变化，那么可重复读事务会回滚。如果 可重复读 事务回滚， 那么可序列化或者可重复读事务执行变化。
Greenplum数据库中的默认事务隔离级别是读已提交。 要为一个事务更改隔离级别，在BEGIN 该事务时声明隔离级别或者在事务开始后使用SET TRANSACTION命令设置隔离级别。
全局死锁检测
Greenplum数据库全局死锁检测器后端进程会收集所有segment上的锁信息，并使用有向算法来检测本地死锁和全局死锁是否存在。 该算法使Greenplum数据库放宽对堆表的并发更新和删除限制。 （Greenplum数据库仍然在AO / CO表上使用表级锁定，对UPDATE, DELETE, 和 SELECT...FOR UPDATE的并发操作进行限制。）
默认情况下，全局死锁检测器是被禁用的，Greenplum数据库以串行方式对堆表执行并发更新和删除操作。 可以通过设置配置参数gp_enable_global_deadlock_detector，开启并发更新并让全局死锁检测器检测死锁是否存在。
启用全局死锁检测器后，当启动Greenplum数据库时，master 主机上会自动启动一个后端进程。可以通过 gp_global_deadlock_detector_period配置参数，来设置采集和分析锁等待数据的时间间隔。
如果全局死锁检测器发现了死锁，它会通过取消最新的事务所关联的一个或多个后端进程来避免死锁。
- 在同一行堆表的并发事务中，第一个事务是更新操作，下一个事务执行更新或删除，并且查询计划包含一个动作操作符。
- 堆表的同一分发键上的并发更新事务由Greenplum Database Postgres查询优化器执行
- 哈希表的同一行上的并发事务更新由GPORCA优化器执行。
用户可以通过自定义函数，pg_dist_wait_status() 查看所有segment上的锁等待信息：哪些事务在等待锁，哪些事务在持有锁，以及事务运行在哪个segment上。 pg_dist_wait_status() 的输出如下例所示:
SELECT * FROM pg_dist_wait_status();
segid | waiter_dxid | holder_dxid | holdTillEndXact
-------+-------------+-------------+-----------------
-1 | 29 | 28 | t
0 | 28 | 26 | t
0 | 27 | 29 | t
1 | 26 | 27 | t
(4 rows)
全局死锁检测器会终止某个事务以打破死锁，并且打印如下的错误信息：
ERROR: canceling statement due to user request: "cancelled by global deadlock detector"
清理数据库
虽然新事务看不到被删除或者被更新的数据行，但是它们仍然在磁盘上占用物理空间。周期性地运行VACUUM命令可以移除这些过期的行。例如：
VACUUM mytable;
VACUUM 命令会收集表级别的统计信息，例如行数和页数。在装载数据后清理所有的表，包括追加优化表。有关推荐的例行清理操作的信息， 请见 例行清理和分析.