[GPDB DBA Guide v5.1 한글판] #데이터 삽입, 업데이트 및 삭제

[GPDB DBA Guide v5.1 한글판] #데이터 삽입, 업데이트 및 삭제

##서론 참고##

2018/08/20 - [GPDB Database Administrator Guide 한글판 작업] - Greenplum database administrator 한글판 작업 시작 및 안내사항

---

데이터 삽입, 업데이트 및 삭제

이 섹션에서는 Greenplum 데이터베이스의 데이터 조작 및 동시 액세스에 대한 정보를 제공합니다.

이 섹션의 목록

* Greenplum 데이터베이스의 동시성 제어

* 데이터 삽입

* 데이터 업데이트

* 데이터 삭제

* 트랜젝션 작업

* 데이터베이스 Vacuuming

Greenplum 데이터베이스의 동시성 제어

Greenplum 데이터베이스 및 PostgreSQL은 동시성 제어를 위해 locks을 사용하지 않습니다. Multiversion Concurrency Control (MVCC)라는 다중 버전 모델을 사용하여 데이터 일관성을 유지 합니다. MVCC는 각 데이터베이스 세션별 트랜잭션 분리를 시키고, 각 쿼리 트랜잭션에는 데이터의 스냅샷이 표시됩니다. 이렇게 하면 트랜잭션이 다른 트랜잭션의 영향을 받지 않고 일관된 데이터를 볼 수 있습니다.

MVCC는 동시성 제어를 위해 locks를 사용하지 않기 때문에 locks 경합을 최소화하고, Greenplum 데이터베이스는 다중 서버 환경에서 합리적인 성능을 유지할 수 있게됩니다. 쿼리(읽기) 데이터를 위해 발생한 locks이 데이터를 쓰기 위해 발생한 locks과 충돌하지 않습니다.

Greenplum 데이터베이스는 어떤 테이블의 데이터에 대한 동시 액세스를 제어하기 위해 다양한 lock 모드를 제공합니다. 대부분의 Greenplum 데이터베이스 SQL 명령은 이것이 실행되는 동안 참조된 테이블의 데이터가 삭제 또는 수정되지 않도록 적절한 잠금을 자동으로 발생시킵니다. MVCC의 동작에 쉽게 적응할 수 없는 응용 프로그램의 경우 lock명령을 사용하여 명시적 lock을 발생 시킬수 있습니다. 그러나 MVCC의 적절한 사용은 일반적으로 더 좋은 성능을 제공 합니다.

Table 1. Lock Modes in Greenplum 데이터베이스

Lock Mode	Associated SQL Commands	Conflicts With
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, 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

@ Note : Greenplum 데이터베이스는 UPDATE, DELETE, and SELECT FOR UPDATE에 대해 보다 제한적인 EXCLUSIVE lock을 발생시킵니다. (PostgreSQL의 ROW EXCLUSIVE lock 보다 더 제한적인)

데이터 삽입

INSERT 명령어를 사용하여 테이블에 행을 생성합니다. 이 명령에는 테이블 이름과 테이블의 각 열에 대한 값이 필요합니다. 원하는 경우 열 이름을 원하는 순서대로 지정 할 수 있습니다. 열 이름을 지정하지 않으면 데이터 값을 쉼표로 구분하여 테이블의 열 순서대로 나열합니다.

예를 들어, 삽입할 열 이름과 값을 모두 지정 하려면

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

값만 지정 하려면

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

일반적으로 데이터 값은 지정되어야 하지만 SELECT 쿼리를 사용 할 수도 있다.

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);

파티션 테이블에 데이터를 INSERT 하려면 CREATE TABLE 명령으로 작성된 루트 테이블을 지정 합니다. INSERT문으로 파티션 테이블의 하위 테이블을 지정할 수도 있습니다. 지정한 하위 테이블에 대한 데이터가 유효하지 않으면 오류가 반환됩니다. INSERT문에서 하위테이블이 아닌 이름은 사용할 수 없습니다.

대량의 데이터를 INSERT 하려면 외부 테이블 또는 COPY문을 사용하십시요. 대향의 행을 INSERT 할때 INSERT문보다 부하가 적습니다. 대량 데이터 로드에 대한 자세한 내용은 데이터 로드 및 언로드 를 참조하십시요.

append-optimized 테이블의 스토리지 모델은 대량 데이터 로드에 최적화되어 있습니다. Greenplum에서는 append-optimized 테이블에 대해 단일 행 INSERT문을 사용하지 않는 것이 좋습니다. append-optimized 테이블의 경우 Greenplum 데이터베이스는 최대 127개의 동시 INSERT 트랜잭션을 append-optimized 테이블에 지원합니다.

데이터 업데이트

UPDATE문은 테이블의 행을 업데이트합니다. 모든 행, 모든 행의 하위 집합 또는 테이블의 개별 행을 업데이트 할 수 있습니다. 다른 열에 영향을 주지 않고 각 열을 개별적으로 업데이트 할 수 있습니다.

업데이트를 하려면 다음의 정보가 필요합니다.

* 업데이트 할 테이블 및 열의 이름

* 열의 새 값

* 업데이트할 행 또는 행을 지정하는 하나 이상의 조건

예를 들어, 다음 명령문은 가격이 5인 모든 제품의 가격을 10으로 업데이트 합니다.

UPDATE products SET price = 10 WHERE price = 5;

Greenplum 데이터베이스의 UPDATE문 사용시엔 다음과 같은 제한이 있습니다.

* 분산키는 업데이트 되지 않을 수 있습니다.

* 미러가 활성화된 경우 업데이트 문에서는 STABLE 또는 VOLATILE 기능을 사용 할 수 없습니다.

* RETURNING 절을 지원하지 않습니다.

* 파티션 컬럼의 업데이트는 할 수 없습니다.

데이터 삭제

DELETE문은 테이블에서 행을 삭제합니다. 특정 조건과 일치하는 행을 삭제하려면 WHERE절에 조건을 지정합니다. WHERE절을 지정하지 않으면 테이블의 모든 행이 삭제됩니다. 빈 테이블이 됩니다. 예를 들어 가격이 10인 모든 제품의 행을 테이블에서 제거하려면 다음과 같이 하십시요.

DELETE FROM products WHERE price = 10;

테이블에서 모든 데이터를 삭제 하려면 다음과 같이 하십시요.

DELETE FROM products; 

Greenplum 데이터베이스에서 DELETE를 사용하면 UPDATE와 유사한 제한이 있습니다.

* 미러가 활성화된 경우 업데이트 문에서는 STABLE 또는 VOLATILE 기능을 사용 할 수 없습니다.

* RETURNING 절을 지원하지 않습니다.

Truncating a Table

TRUNCATE문을 사용하여 테이블의 모든 행을 신속하게 제거 할수도 있습니다. 예를 들면 다음과 같습니다.

TRUNCATE mytable;

위 명령은 테이블의 모든행을 비웁니다. TRUNCATE는 테이블을 스캔하지 않으므로 상속된 하위 테이블 또는 ON DELETE 재작성 규칙을 따르지 않습니다. 테이블의 행만 제거 하게 됩니다.

트랜잭션 작업

트랜잭션은 사용자가 다수 SQL문의 집합을  전체 다 처리 되던지, 않던지 하게 합니다.

다음은 Greenplum 데이터베이스 SQL 트랜잭션 명령문 입니다.

* BEGIN or START TRANSACTION starts a transaction block.

* END or COMMIT commits the results of a transaction.

* ROLLBACK abandons a transaction without making any changes.

* SAVEPOINT marks a place in a transaction and enables partial rollback. You can roll back  commands executed after a savepoint while maintaining commands executed before the savepoint.

* ROLLBACK TO SAVEPOINT rolls back a transaction to a savepoint.

* RELEASE SAVEPOINT destroys a savepoint within a transaction.

트랜잭션 분리 수준

Greenplum 데이터베이스는 다음과 같은 표준 SQL 트랜잭션 수준을 허용합니다.

* read uncommitted and read committed behave like the standard read committed

* repeatable read is disallowed. If the behavior of repeatable read is required, use serializable.

* serializable behaves in a manner similar to SQL standard serializable

다음 정보는 Greenplum 트랜잭션 수준의 동작을 설명합니다.

* read committed/read uncommitted — 빠르고 간단한 부분적인 트랜잭션 분리를 제공합니다. 읽기 커밋 및 읽기 커밋되지 않은 트랜잭션 분리를 통해 SELECT, UPDATE 및 DELETE 트랜잭션은 조회가 시작될 때 생성된 데이터베이스의 스냅샷에서 작동합니다.

A SELECT 쿼리 :

* 조회가 시작되기 전에 커밋된 데이터를 검색합니다.

* 트랜잭션 내에서 실행된 업데이트입니다.

* 트랜잭션 외부에 커밋되지 않은 데이터가 표시되지 않습니다.

* 자체 트랜잭션에서 초기 읽기 후 동시 트랜잭션이 커밋된 경우 동시 트랜잭션이 수행한 변경 내용을 볼 수 있습니다.

동일한 트랜잭션의 연속 SELECT 쿼리는 다른 동시 트랜잭션이 쿼리를 시작하기 전에 변경을 커밋하는 경우 서로 다른 데이터를 볼 수 있습니다. UPDATE 및 DELETE 명령은 명령이 시작되기 전에 커밋된 행만 찾습니다.

읽기 커밋되거나 커밋되지 않은 트랜잭션 분리를 통해 동시 트랜잭션은 UPDATE 또는 DELETE가 행을 찾기 전에 행을 수정하거나 잠글 수 있습니다. 읽기 커밋되거나 커밋되지 않은 트랜잭션 분리는 복잡한 쿼리 및 업데이트를 수행하고 데이터베이스를 일관성 있게 봐야 하는 애플리케이션에 적합하지 않을 수 있습니다.

* serializable — 트랜잭션이 동시에 실행되는 것이 아니라 하나씩 실행되는 엄격한 트랜잭션 분리를 제공합니다. 일련화 가능한 수준의 응용 프로그램은 일련화 실패 시 트랜잭션을 다시 시도하도록 설계되어야 합니다. Greenplum 데이터베이스에서 SERIALIZABLE은 다른 트랜잭션에서 커밋되지 않은 변경된 데이터를 읽거나, 일치하지않는 동일한 데이터 읽기 및 팬텀 읽기를 방지하지만 일부 SERIALIZABLE 트랜잭션에서는 이러한 상호 작용이 발생할 수 있습니다. 동시에 실행되는 트랜잭션을 검사하여 동일한 데이터의 동시 업데이트를 허용하지 않음으로써 차단되지 않는 상호 작용을 식별해야 합니다. 명시적 테이블 잠금을 사용하거나 충돌을 나타내기 위해 도입된 더미 열을 업데이트하도록 충돌하는 트랜잭션을 요구하여 식별된 문제를 방지할 수 있습니다.

A SELECT 쿼리 :

* 트랜잭션의 시작에 따른 데이터 스냅샷을 찾습니다(트랜잭션 내의 현재 쿼리가 아닌).

* 조회가 시작되기 전에 커밋된 데이터만 검색합니다.

* 트랜잭션 내에서 실행된 업데이트입니다.

* 트랜잭션 외부에 커밋되지 않은 데이터가 표시되지 않습니다.

* 동시 트랜잭션이 변경한 내용이 표시되지 않습니다.

단일 트랜잭션 내의 연속 SELECT 명령은 항상 동일한 데이터를 봅니다.

UPDATE, Delete, SELECT FOR UPDATE 및 SELECT FOR SHARE 명령은 명령이 시작되기 전에 커밋된 행만 찾습니다. 행을 발견했을 때 동시 트랜잭션이 이미 대상 행을 업데이트, 삭제 또는 잠근 경우 직렬화되거나 반복 가능한 읽기 트랜잭션이 동시 트랜잭션이 행을 업데이트, 삭제 또는 롤백할 때까지 기다립니다.

동시 트랜잭션이 행을 업데이트하거나 삭제하면 직렬화되거나 반복 가능한 읽기 트랜잭션이 롤백됩니다. 동시 트랜잭션이 롤백되면 직렬화되거나 반복 가능한 읽기 트랜잭션이 업데이트되거나 행을 삭제합니다.

Greenplum 데이터베이스의 기본 트랜잭션 분리 수준은 읽기 커밋됨입니다. 트랜잭션의 분리 수준을 변경하려면 트랜잭션을 BEGING할 때 분리 수준을 선언하거나 트랜잭션이 시작된 후 SET TRANSACTION 명령을 사용합니다.

데이터베이스 Vacuuming

삭제되거나 업데이트된 데이터 행은 새 트랜잭션에서 볼 수 없는 경우에도 Disk의 실제 공간을 차지합니다. VACUUM 명령을 정기적으로 실행하면 이러한 행들이 제거됩니다. 예를 들면 다음과 같습니다.

VACUUM mytable;

VACUUM 명령은 행 및 페이지 수와 같은 테이블 수준 통계를 수집합니다. 추가 최적화 테이블를 포함하여 데이터를 로드한 후 모든 테이블을 VACUUM합니다. 권장되는 VACUUM 작업에 대한 자세한 내용은 일상적인 VACUUM 및 분석을 참조하십시오.

중요: VACUUM, VACUUM FULL 및 VACUUM ANALYZE 명령을 사용하여 Greenplum 데이터베이스의 데이터를 유지관리해야 합니다. 명령 사용에 대한 자세한 내용은 Greenplum Database Reference Guide의 VACUUM 명령을 참조하십시오.

사용 가능한 공간 맵 구성

만료된 행은 사용 가능한 공간 맵에서 유지됩니다. 사용 가능한 공간 맵은 데이터베이스의 만료된 행을 모두 포함할 수 있을 만큼 커야 합니다. 그렇지 않으면 정규 VACUUM 명령을 사용하여 사용 가능한 공간 맵을 오버플로우 하는 만료된 행이 사용한 공간을 회수할 수 없습니다.

VACUUM FULL은 만료된 모든 행 공간을 회수하지만, 비용이 많이 드는 작업이며 대규모 배포된 Greenplum 데이터베이스 테이블에서 완료하는 데 지나치게 오랜 시간이 걸릴 수 있습니다. 사용 가능한 공간 맵이 오버플로되면 CREATE TABLE AS 문을 사용하여 테이블을 재생성하고 이전 테이블을 삭제할 수 있습니다. VACUUM FULL을 사용하지 않는 것이 좋습니다.

다음 서버 구성 매개 변수를 사용하여 사용 가능한 공간 맵의 크기를 지정합니다.

* max_fsm_pages

* max_fsm_relations

◎상위 주제 : #Greenplum Database Administrator 가이드

---

* 원본 보기 (https://gpdb.docs.pivotal.io/510/admin_guide/dml.html#topic2__lock_note)

---

* 해당 섹션 리스트 제목에 링크가 없는 것은 아직 하위 경로 번역 작업이 이루어 지지 않음을 미리 알려 드립니다. (하위 경로 번역이 이루어 지면 섹션 제목에 링크가 활성화 됩니다.)

By.sTricky