트랜잭션이란?

데이터베이스 트랜잭션(Database Transaction)은 데이터베이스 관리 시스템 또는 유사한 시스템에서 상호작용의 단위입니다. 여기서 유사한 시스템이란 트랜잭션이 성공과 실패가 분명하고 상호 독립적이며, 일관되고 믿을 수 있는 시스템을 의미합니다.

이론적으로 데이터베이스 시스템은 각각의 트랜잭션에 대해 원자성(Atomicity), 일관성(Consistency), 고립성(Isolation), 영구성(Durability)을 보장합니다. 이 성질을 첫글자를 따 ACID라 부릅니다. 그러나, 실제로는 성능향상을 위해 이런 특성들이 종종 완화되곤 합니다.


다시말해서 트랜잭션은 데이터베이스 내에서 한꺼번에 수행되어야 할 일련의 연산들입니다. 간단하게 말해서 전부 성공하거나 전부 실패되거나 둘 중 하나의 작업을 수행합니다. 트랙잭션의 모든 연산은 반드시 한꺼번에 완료가 되야 하며 그렇지 않은 경우에는 한꺼번에 취소되어야 하는 원자성을 가지고 있습니다.

한꺼번에 완료가 된 경우에는 COMMIT을 호출해 작업결과를 데이터베이스에 반영 됩니다취소가 되거나 문제가 발생한 경우에는 ROLLBACK을 호출하고 작업결과를 모두 취소하여 데이터베이스에 영향을 미치지 않게 됩니다. 

트랜잭션 성질

트랜잭션이 진행되기 전과 완료된 후에 상태를 볼 수 있지만 트랜잭션이 진행되는 중간 데이터는 볼수 없습니다. 

1. 원자성(Atomicity)

분리 할수 없는 하나의 단위로 작업은 모두 완료되거나 모두 취소 되어야 합니다.

예로 이체 과정 중에 트랜잭션이 실패하게 되어 예금이 사라지는 경우가 발생해서는 안 되기 때문에 DBMS는 완료되지 않은 트랜잭션의 중간 상태를 데이터베이스에 반영해서는 안 됩니다. 즉, 트랜잭션의 모든 연산들이 정상적으로 수행 완료되거나 아니면 전혀 어떠한 연산도 수행되지 않은 상태를 보장해야 합니다. atomicity는 쉽게 'all or nothing' 특성으로 설명된다

2. 일관성(Consistency)

사용되는 모든 데이터는 일관되어야 합니다.

고립된 트랜잭션의 수행이 데이터베이스의 일관성을 보존해야 합니다. 즉, 성공적으로 수행된 트랜잭션은 정당한 데이터들만을 데이터베이스에 반영해야 합니다. 트랜잭션의 수행을 데이터베이스 상태 간의 전이(transition)로 봤을 때, 트랜잭션 수행 전후의 데이터베이스 상태는 각각 일관성이 보장되는 서로 다른 상태가 됩니다. 트랜잭션 수행이 보존해야 할 일관성은 기본 키, 외래 키 제약과 같은 명시적인 무결성 제약 조건들 뿐만 아니라, 자금 이체 예에서 두 계좌 잔고의 합은 이체 전후가 같아야 한다는 사항과 같은 비명시적인 일관성 조건들도 있습니다.

3. 격리성(Isolation)

접근하고 있는 데이터는 다른 트랜잭션으로 부터 격리 되어야 합니다.

여러 트랜잭션이 동시에 수행되더라도 각각의 트랜잭션은 다른 트랜잭션의 수행에 영향을 받지 않고 독립적으로 수행되어야 합니다. 즉, 한 트랜잭션의 중간 결과가 다른 트랜잭션에게는 숨겨져야 한다는 의미인데, 이러한 isolation 성질이 보장되지 않으면 트랜잭션이 원래 상태로 되돌아갈 수 없게 됩니다. Isolation 성질을 보장할 수 있는 가장 쉬운 방법은 모든 트랜잭션을 순차적으로 수행하는 것입니다. 하지만 병렬적 수행의 장점을 얻기 위해서 DBMS는 병렬적으로 수행하면서도 일렬(serial) 수행과 같은 결과를 보장할 수 있는 방식을 제공하고 있습니다.

4. 영속성(Durability)

트랙잭션이 정상 종료되면 그 결과는 시스템에 영구적으로 적용되어야 합니다.

트랜잭션이 성공적으로 완료되어 COMMIT되고 나면, 해당 트랜잭션에 의한 모든 변경은 향후에 어떤 소프트웨어나 하드웨어 장애가 발생되더라도 보존되어야 합니다.

5. 순차성(Sequentiality)

데이터를 다시 로드하고 트랜잭션을 재생하여 원래 트랜잭션이 수행된 후의 상태로 데이터를 되돌리는 것을 말합니다.

UNDO, REDO

REDO는 "다시 하다."라는 뜻을 가지고 UNDO는  "원상태로 돌리다" 라는 뜻을 가지고 있습니다. 즉 REDO는 무언가를 다시 하는 것이고 UNDO는 무언가를 되돌리는 것입니다.

REDO는 기본적으로 복구의 역할을 합니다. 오라클 서버에 무슨 작업을 하든지 모두 REDO에 기록이 됩니다. (UNDO 포함)

UNDO는 작업 롤백과, 읽기 일관성, 복구를 합니다.

REDOUNDO의 공통점은 복구를 한다는 것입니다.


하지만 둘의 복구 방법은 차이가 있습니다.

REDO는 복구를 할때 사용자가 했던 작업을 그대로 다시 하지만 UNDO는 사용자가 했던 작업을 반대로 진행합니다. 즉 사용자의 작업을 원상태로 돌립니다.


예를 들어 아래와 같은 작업을 했을때 세션이 비정상 종료 되었다고 가정합니다.

update t1
set no = no + 1
where id = 1;


세션이 복구 되는 과정에서 아래의 작업을 진행합니다.

update t1
set no = no + 1
where id = 1;


 하지만 만일 세션이 비정상 종료가 되기전 COMMIT을 하지 않았다면 UNDO를 이용하여 아래와 같은 작업을 이어서 하게됩니다.

update t1
set no = no - 1
where id = 1;


복구는 UNDO 를 통해서 복구를 하게 됩니다. 즉, ROLLBACK을 한다는 말입니다. 시스템 장애가 발생하게 되면 UNDO 데이터도 모두 날아갑니다. 결국 시스템 장애시 REDO 데이터를 이용해서 마지막 CHECK POINT부터 장애까지의 DB BUFFER CACHE 를 복구하게 됩니다. 이게 완료가 되면 UNDO를 이용하여 COMMIT되지 않은 데이터를 모두 ROLLBACK 함으로써 복구를 완료하게 됩니다. 결국 REDO가 UNDO를 복구하고 최종적으로 UNDO가 복구를 하게 됩니다. 

참고로 UNDO 데이터는 아래와 같이 기록 됩니다. 

INSERT 시, insert 된 로우의 rowid 기록
UPDATE 시, 바뀐 컬럼의 바뀌기 전 값 기록
DELETE 시, 지워진 모든 데이터 기록

'DB' 카테고리의 다른 글

[DB] 클러스터  (0) 2016.11.18
[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27


명령어 종류 

명령어 

설명 

데이터 조작어

(DML :  Data Manipulation Language

SELECT 

데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 함

INSERT

UPDATE

DELETE 

데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류(데이터 삽입, 수정, 삭제)의 명령어들을 말함.

 데이터 정의어

(DDL : Data Definition Language)

CREATE

ALTER

DROP

RENAME

TRUNCATE 

테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 (생성, 변경, 삭제, 이름변경) 데이터 구조와 관련된 명령어들을 말함.

데이터 제어어

(DCL : Data Control Language) 

GRANT

REVOKE 

데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어들을 말함.

트랜잭션 제어어

(TCL : Transaction Control Language)

COMMIT

ROLLBACK

SAVEPOINT 

논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말함.



'DB' 카테고리의 다른 글

[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27
[SQL] Union과 Union all 차이  (0) 2016.10.12
  1. 코딩하는흑구 2019.02.27 11:13 신고

    항상 잘보고있습니다.^^

select 결과를 update하는 방법은 여러가지가 존재합니다.

Set Subquery

set 절에 subquery로 쿼리 결과를 update할 수 있습니다. 이 방법의 단점은 쿼리 결과가 0~1개 일때만 유효합니다. 만약 쿼리 결과가 1개 이상 나올 경우 에러가 납니다.

UPDATE [테이블명]
SET (컬럼, 컬럼1, ...) = (값, 값1, ... ) 
[WHERE conditions];

-- 예시
update test set (pk, id, name) = (select pk, id, name from test1 where pk = 1) -- 만약 해당 서브쿼리의 조회결과 갯수가 2개 이상일 시 에러

From Subquery

이 방법은 from절에 subquery를 넣어 사용하는 방법입니다. set절에 subquery를 사용하는 것과는 다르게 쿼리결과가 2개 이상나와도 업데이트를 할 수 있습니다. 단 업데이트를 해야하는 컬럼은 set절에 전부 명시해야합니다.(귀찮)

update [테이블명]
set [업데이트할 컬럼] = [업데이트 값]
from [테이블명]
where [조건]
 
 
--예시
update test
    set count = test.count + a.count
    from (select * from test1) as a
    where test.pk = a.pk
 
-- test테이블 내 pk와 test1 테이블 내 pk 가 같은 데이터에만 count를 추가해줌
-- 조인도 가능


'DB' 카테고리의 다른 글

[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27
[SQL] Union과 Union all 차이  (0) 2016.10.12
[SQL] Joins  (0) 2016.08.24

만약 text 타입의 a라는 컬럼을 smallint로 타입을 변경한다고 할 때 아래와 같은 에러가 납니다.

alter table test alter column a type smallint;
 
-- 에러 발생
You might need to specify "USING a::smallint


텍스트 또는 varchar에서 정수로의 자동 변환을 지원하지 않습니다. (즉, 정수로 예상하는 함수에 varchar를 전달하거나 varchar 필드를 정수로 할당 할 수 없습니다.) 따라서 ALTER TABLE ...을 사용하여 형변환에 대해 명시적으로 지정해야합니다.

alter table test alter column a type smallint using a::smallint;


기타 sql에는 first()와 last() aggregate function을 제공하지만 postgresql에서는 직접 제공해주지 않습니다. 따라서 다른 sql에서 사용하는 first()와 last()를 사용하기 위해선 아래의 프로시저 함수를 추가해야 동일한 기능을 사용가능합니다.

-- Drop a function if exists
DROP FUNCTION IF EXISTS first_agg(anyelement, anyelement) CASCADE;
 
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $1;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE FIRST (
        sfunc    = first_agg,
        basetype = anyelement,
        stype    = anyelement
);
 
-- Drop a function if exists
DROP FUNCTION IF EXISTS last_agg(anyelement, anyelement) CASCADE;
-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $2;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE LAST (
        sfunc    = last_agg,
        basetype = anyelement,
        stype    = anyelement
);


컬럼추가

ALTER TABLE books ADD publication date;

컬럼삭제

ALTER TABLE books DROP publication;

컬럼 default값 추가

ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval('books_idx');

컬럼 default값 제거

ALTER TABLE books ALTER id DROP DEFAULT;

컬럼 NOT NULL 세팅

ALTER TABLE books ALTER COLUMN id SET NOT NULL;

컬럼 NOT NULL 제거

ALTER TABLE books ALTER COLUMN id DROP NOT NULL;

테이블 이름 변경

ALTER TABLE books RENAME TO literature;

컬럼명 변경

ALTER TABLE books RENAME COLUMN in_stock TO is_in_stock;

컬럼 데이터 타입 변경

ALTER TABLE books ALTER COLUMN publication TYPE text;

constraint (제약조건) 추가

ALTER TABLE editions ADD CONSTRAINT foreign_book FOREIGN KEY (book_id) REFERENCES books (id);
ALTER TABLE editions ADD CONSTRAINT hard_or_paper_back CHECK (type='p' OR type='h');

constraint (제약조건) 변경 (변경은 없고 drop -> add)

ALTER TABLE editions DROP CONSTRAINT editions_type_check;
ALTER TABLE editions ADD CONSTRAINT editions_type_check CHECK (type=ANY(ARRAY[0::smallint, 1::smallint, 2::smallint])); 

테이블 소유자 변경

ALTER TABLE employees OWNER TO corwin;


  1. 김상준 2019.08.26 14:03

    파티션이 되어 있는 테이블의 컬럼을 생성을 하고자 합니다.

    그럼 파티션 메인 테이블에 수정을 하면 나머지도 다 변경이 되나요?

    아님 하나하나 다 변경을 해줘야 하나요?

+ Random Posts