📌 데이터베이스 트랜잭션과 내부 구조 (실무 관점 정리)

1. ACID와 Isolation Level

• ACID는 트랜잭션의 기본 원칙.
• 그중 **I (Isolation, 격리성)**을 세부적으로 구현한 것이 Isolation Level.

✅ 트랜잭션과 관련된 다른 개념 (계층, 구조 등)

1. ACID 속성 트랜잭션의 기본 원칙:
Atomicity (원자성): 모두 성공하거나 모두 실패해야 함
Consistency (일관성): 트랜잭션 전후 데이터가 무결성 유지
Isolation (격리성): 다른 트랜잭션과 독립적으로 수행
Durability (지속성): 커밋된 데이터는 영구 반영

➡️ Isolation Level은 ACID 중 **I (격리성)**을 어떻게 구현할지에 대한 세부 옵션.

2. MVCC (Multi Version Concurrency Control) – 다중 버전 동시성 제어

주로 READ COMMITTED 또는 REPEATABLE READ에서 사용됨.

데이터를 수정할 때 기존 버전을 유지해서 트랜잭션마다 스냅샷을 보는 것처럼 처리.

Oracle, PostgreSQL, InnoDB 등에서 MVCC 사용.

트랜잭션마다 Undo Log 혹은 Rollback Segment를 참조해서 과거 데이터를 조회함.

2. MVCC (Multi-Version Concurrency Control)

트랜잭션이 동시에 같은 데이터를 읽고 쓸 때 충돌을 줄이는 방식.
👉 핵심 아이디어: “읽는 트랜잭션은 과거 버전을 보고, 쓰는 트랜잭션은 새로운 버전을 만든다.”

(1) 동작 원리

• INSERT → 새로운 Row 생성.
• UPDATE → 새로운 버전(Row)을 만들고, 기존 Row는 Undo Log에 보관.
• DELETE → 실제 삭제하지 않고 "삭제 플래그"를 기록.
• SELECT → 트랜잭션 시작 시점의 Snapshot(Undo Log 참조)으로 읽음.

(2) DBMS별 MVCC 구현

• Oracle : Undo Segment 기반. SELECT는 Undo에 있는 과거 버전을 읽음.
• PostgreSQL : Tuple 버전 관리 → Vacuum(청소 작업) 필요.
• MariaDB/InnoDB : Undo Log + Redo Log 조합. Snapshot 읽기 지원.

3. Locking 계층 (동시성 제어)

트랜잭션 충돌을 제어하기 위해 락을 건다.

(1) 주요 락 단위

• Row-Level Lock : InnoDB. 동시성 높음.
• Table-Level Lock : MyISAM, 일부 DDL. 단순하지만 동시성 낮음.
• Gap Lock / Next-Key Lock : Phantom Read 방지용 (InnoDB).

(2) Intent Lock

• 계층적 락 구조에서 충돌 체크용.
• 예: 테이블 전체 락 vs 특정 Row 락이 충돌하는지 빠르게 판단.

(3) 락 경합(Tuning 관점)

• innodb_row_lock_waits : 락 충돌 횟수
• innodb_row_lock_time_avg : 평균 대기 시간
• 해결책:
  • 트랜잭션 단위를 짧게 유지
  • 인덱스 튜닝 → 불필요한 Range Scan 줄이기
  • Batch Update 대신 Chunk Update

4. Undo / Redo 로그 계층

(1) Undo Log

• MVCC 지원 : 과거 버전 데이터를 저장.
• Rollback 지원 : 트랜잭션 실패 시 원상 복구.
• InnoDB는 Undo Tablespace에 저장.

(2) Redo Log

• 장애 복구용 : Commit된 변경 사항을 기록.
• DB Crash 후 Redo Log를 리플레이하여 데이터 복원.
• InnoDB는 Write-Ahead Logging (WAL) 사용 → 먼저 Redo Log에 기록 후 실제 데이터 반영.

✅ 요약 (한눈에 보기)

 root 계정으로
할당받은 디스크를  oracleasm  에 추가

# oracleasm create disk [이름] /dev/mapper(실제공간위치)

#oracleasm scandisks

asmca 

추가 할 diskgroup 이름을 마우스 우클릭

add disk 로 추가하면 된다.

1. TABLE Tablespace MOVE / INDEX Tablespace MOVE (Rebuild)

-- TABLE Tablespace MOVE
ALTER TABLE ISP.VCT_GOT_I MOVE TABLESPACE TSKG01DT;

-- INDEX Tablespace MOVE(rebuild)
-- TABLE Tablespace MOVE 하면 INDEX 가 invalid 로 바뀜 
ALTER INDEX ISP.PK_VCT_GOT_I REBUILD TABLESPACE TSKG01IX;

2. Partition TABLE Tablespace MOVE / Partition INDEX Tablespace MOVE (Rebuild)

ALTER TABLE ISP.VCT_GOT_I MOVE PARTITION PT_L202210 TABLESPACE TSKG01DT;
ALTER INDEX ISP.PK_VCT_GOT_I REBUILD PARTITION PT_L202210 TABLESPACE TSKG01IX;

서브파티션 일 경우
ALTER TABLE ISP.ACT_GOT_I MOVE SUBPARTITION P_200801_SP2_000 TABLESPACE TSKG01DT;

▶ 파티션 테이블, 인덱스에서 DESCRIPTION 을 보면 테이블스페이스가 AS-IS 로 보일 때 DEFAULT ATTRIBUTES TABLESPACE 를 수동으로 변경한다.

ALTER TABLE ISP.VCT_GOT_I MODIFY DEFAULT ATTRIBUTES TABLESPACE TSKG01DT;
ALTER INDEX ISP.PK_VCT_GOT_I MODIFY DEFAULT ATTRIBUTES TABLESPACE TSKG01IX;

조회 시 TO_SINGLE_BYTE 함수 사용하기

가끔 클라이언트에서 조회를 했는데

"ORA-29275: 부분 다중 바이트 문자 에러가 난다" 는 경우가 있습니다.

해당 오류의 원인은

서버의 문자 집합(CHARACTER SET)과 클라이언트의 문자 집합(CHARACTER SET) 값이 다르거나

데이터가 이상하게 들어갔을 경우 발생합니다.

가장 쉽게 해결할 수 있는 방법,
"TO_SINGLE_BYTE 함수를 사용하여 SELECT문 작성하기" 입니다.

[예시] SELECT TO_SINGLE_BYTE(필드명) [변수명]  FROM 테이블명;

참 간단하죵..! 😁

이 방법 외,

1) 서버 CHARACTERSET 값을 클라이언트의 값과 맞추기 (비추천)

2) 클라이언트에서 요청한 값으로 데이터 수정을 하기

저 같은 경우에는

실무에서 가장 많이 사용되는 방법은 TO_SINGLE_BYTE 함수 이용하거나 2) 방법을 사용합니다.

1) 방법은 클라이언트에 맞추게 되면 모든 클라이언트의 CHARACTERSET 값도 바꿔야합니다.

사용자가 한 클라이언트거나 소수라면 상관없지만 대다수라면 정말 비추합니다.

2) 방법 같은 경우는 해당 클라이언트와 연락을 닿아 요청했던 값으로 데이터 수정을 합니다.

가장 평범한 방법이여서 경우에 따라 종종 사용합니다.

앞서 Cache Fusion의 개념에 대해 알아보았는데, 이번에는 Cache Fusion의 엑세스에 대해 알아보겠다.

Cache Fusion Access

Cache Fusion은 서로 다른 Node의 Instance에서 Block을 동기화하여 액세스하는 기술이다. 따라서 필요한 Block이 접속한 Instance에 존재하는 경우와 아닌 경우로 구분할 수 있다.

Local Access

요청한 Block의 최신 Block이 Local Instance에 존재할 경우 Instance 간 동기화없이 액세스한다. 

즉, Block을 액세스하는 세션의 위치 = 필요한 Block의 위치 = Master Node의 위치 인 경우를 Local 액세스라고 한다.  

Remote Access

요청한 Block이 다른 Instance에 의해 수정되어 Remote Instance에 존재할 경우 Interconnect를 통해 Block을 복제하여 가져온 후 Data에 액세스한다.

2-way Access

1. Instance 2에 접속한 세션에서 1번 Block을 액세스하는데, Instance 2에는 1번 Block이 없다.

2. Instance 2는 1번 Block을 Master Node인 Instance 3에게 요청한다.

3. Master Node는 GRD를 확인하여 1번 Block의 소유자(Holder Node)가 Instance 3인 것을 확인하고 Instance 3에게 Block 요청을 전달한다.

4. Instance 3은 Master Node의 요청을 받고 1번 Block의 이미지를 Instance 2에 전송한다.

5. Block을 전송받은 Instance 2는 세션의 요청에 대한 응답을 수행한다. 

즉, Block을 요청하는 세션의 위치 ≠ Block의 위치 = Master Node의 위치 인 경우 2개의 Instance에 액세스하기 때문에 2-way Access라고 한다.

3-way Access

1. Instance 2에 접속한 세션에서 1번 Block을 액세스하는데, Instance 2에는 1번 Block이 없다.

2. Instance 2는 1번 Block을 Master Node인 Instance 3에게 요청한다.

3. Master Node는 GRD를 확인하여 1번 Block의 소유자(Holder Node)가 Instance 1인 것을 확인하고 Instance 1에게 Block 요청을 전달한다.

4. Instance 1은 Master Node의 요청을 받고 1번 Block의 이미지를 Instance 2에 전송한다.

5. Block을 전송받은 Instance 2는 세션의 요청에 대한 응답을 수행한다. 

즉, Block을 요청하는 세션의 위치 ≠ Block의 위치 ≠ Master Node의 위치 인 경우 3개의 Instance에 액세스하기 때문에 3-way Access라고 한다.

Disk Access

1. Instance 2에 접속한 세션에서 1번 Block을 액세스하는데, Instance 2에는 1번 Block이 없다.

2. Instance 2는 1번 Block을 Master Node인 Instance 2에게 요청한다.

3. Master Node는 GRD를 확인하여 1번 Block이 어느 Instance에도 존재하지 않음을 확인한다.

4. Master Node에게 그 사실을 전달받은 Instance 2는 Disk에 존재하는 해당 Block을 Instance 2의 DB Buffer Cache에 캐싱한다.

5. Instance 2에 해당 Block이 캐싱되었으므로 해당 Block을 엑세스한다. 

즉, 요청한 Block이 어느 Instance에도 존재하지 않을 경우 Disk Access를 한다.

Oracle RAC의 DB Buffer Cache

오늘은 오라클 RAC 특징에 있어서 가장 중요한 cache fusion에 대해서 알아보겠다. 그러면 그 전에 DB Buffer cache에 대해서 알아야 한다.

Node

하나의 DB에 연결된 서버를 의미한다. 2 Node RAC는 2대의 물리적인 서버가 하나의 DB에 연결된 경우이다.

Instance

각각의 Node는 각각의 Instance를 가진다. 하나의 Instance는 여러 Node에 중복 존재할 수 없지만 하나의 Node는 여러개의 Instance로 구성할 수 있다.

SGA

하나의 Instance는 반드시 하나의 SGA를 가지며 SGA는 Shared Pool, DB Buffer Cache, Redo Log Buffer 등으로 구성된다.

Cache Fusion과 DB Buffer Cache

DB Buffer Cache는 유저가 요청한 SQL문을 수행하기 위해서 필요한 Data Block을 Disk로부터 메모리로 올리는(Caching) 하는 영역이다. Cache Fusion에서 DB Buffer Cache가 중요한 이유는 Cache Fusion이 서로 다른 Instance의 DB Buffer Cache에서 Block을 이동시키는 부분에서 발생하기 때문이다.

Cache Fusion

Cache Fusion은 OPS(Oracle Parallel Server)의 Block Transformation의 문제를 극복하기 위해 나타난 아키텍쳐이며 이를 통해 Oracle RAC는 성능적으로 안정성을 갖추게 되었다.

*Block Transformation

특정 Instance에 존재하는 Block을 다른 Instance에서 액세스하기 위해서 해당 블럭을 공유 Storage에 write한 후, 그 Block을 호출한 Instance의 DB Buffer Cache로 캐싱하여 Data Block을 다른 Node로 이동시키는 방식이다.

하지만 각각의 Instance에서 많은 작업이 수행되고, 각각의 Instance 사이에서 이동하는 Block이 많으면 성능 저하가 발생한다. 이 이유로 OPS는 사라지게 되었다.

Cache Fusion은 아래와 같은 방식으로 수행된다.

① Instance 2에 접속한 A 프로세스는 Instance 1에 존재하는 1번 Block을 액세스하기 위해 호출한다.

② Instance 1에 존재하는 1번 Block은 Instance 2의 호출에 응답하기 위해 Instance 2의 SGA의 DB Buffer Cache로 이동해야 한다. 이동을 위해 공유 Storage에 존재하는 DB를 이용하지 않고 Instance 사이의 Interconnect를 이용한다.

③ Interconnect를 이용하여 Instance 2로 캐싱된 1번 Block을 A 프로세스가 액세스할 수 있게 된다.

Cache Fusion의 목적

Block이 특정 Node의 DB Buffer Cache에 로드되어 있다면 Disk I/O가 발생하지 않기 때문에 Block 이동을 빠르게 처리할 수 있어서 Oracle RAC의 성능 향상에 가장 중요한 아키텍쳐이다.

Cache Fusion의 구성 요소

• GRD(Global Resource Directory)

   RAC에서 Global Resource를 관리한다. Cluster의 동기화를 위해서 Cluster 내의 Resource 정보들(Resource Master,       Resource Holder)을 저장한다.

*Resource Master : 특정 Resource의 Master를 지칭한다. 특정 Global Resource는 반드시 하나의 GRD에서 최신 정보를 관리한다.

*Resource Holder : 특정 Resource의 최종 버전을 가지고 있는 Node를 의미한다.

• GCS(Global Cache Service)

   Data의 일관성 및 무결성을 유지하는 서비스로, DB Buffer Cache 동기화에 대한 Global 정보를 저장한다. 즉, Block에     대한 Lock 정보를 관리한다.

• GES(Global Enqueue Service)

   DB Lock에 대한 정보를 관리하는 서비스로 Block 이외의 Lock 정보를 저장한다. 즉, Enqueue Lock, Library Cache         Lock, Row Cache Lock 등의 정보를 관리한다.

Oracle RAC 개념

Oracle RAC는 여러 개의 Instance가 하나의 Database를 엑세스 할 수 있다. 이는 application에서 접속할 수 있는 통로는 여러 개이며 Database는 하나인 형태이다.

Oracle RAC = N개의 Instance + 1개의 Database

그리고 RAC로 연결된 N개의 Instance에서 동일한 Datafile을 공유하여 엑세스한다. 하지만 Database 작업에 사용할 수 있는 CPU나 메모리 등의 Resource는 서로 공유하지 않으며 해당 Node의 Resource만을 사용한다.

Cluster

두 개 이상의 독립된 서버들과 Disk를 하나로 연결하는 기법이다. 사용자가 Cluster로 구성된 서버들 중 어느 서버에 접속해도 동일한 Disk를 엑세스하게 되므로 하나의 서버 또는 하나의 Disk에 연결하는 것처럼 인식한다.

Oracle RAC는 Oracle Clusterware를 사용하여 어느 Instance에 접속하여도 사용자에게 동일한 data를 실시간으로 조회, 변경할 수 있는 기능을 제공한다. 또한 Oracle Clusterware를 사용하면 높은 처리량과 고가용성을 보장할 수 있다.

Clusterware의 자세한 특징은 다음에 설명하도록 하겠다.

단일 Instance vs Oracle RAC

단일 Instance 환경에서와 마찬가지로 Oracle RAC 환경의 각 Instance에는 각자의 SGA와 백그라운드 프로세스가 존재한다. 그러나 모든 Datafile과 Control File은 모든 인스턴스에서 동일하게 엑세스할 수 있어야 하므로 공유 Storage에 위치해야 한다. 

그리고 각 Instance에는 고유한 Online Redo Log File이 존재한다. Online Redo Log File은 자신이 속한 Instance에 의해서만 기록될 수 있다. 그러나 online redo log file도 인스턴스 복구 시에는 다른 인스턴스에서 엑세스할 수 있어야 한다. 따라서 Online Redo log file도 공유 storage에 저장되어야 한다(redo log group을 공유하고, redo log file에 쓰는 것은 개별적으로 쓴다).

*Data 정합성

Oracle RAC 환경의 Instance는 여러 Instance에서 동시에 동일한 data를 엑세스하는 구조로 data를 공유한다. 동일한 data를 여러 인스턴스가 읽어도 문제는 없지만, 동일한 data에 대해 여러 인스턴스가 동시에 수정, 삽입 등을 하는 경우 data 무결성 문제가 발생할 수 있다. 그래서 Oracle RAC은 Cache lock과 Cache Fusion 등을 사용하여 이를 보장한다. 자세한 것은 다음에 설명하겠다.

Oracle RAC 구성요소

Oracle Grid Infrastructure

Oracle RAC는 Grid Infrastructure에서 제공하는 Oracle Clusterware의 기반하에 여러 Database 서버를 묶어서 하나의 시스템처럼 동작하도록 지원한다.

*GI는 Oracle Custerware 및 ASM으로 구성되며 운영체제와 긴밀하게 통합된 소프트웨어 계층이다.

- Clusterware 설치 : RAC 구성을 위해 필요한 소프웨어이며 오라클 설치 계정 이외 별도의 계정을 생성하여 설치하는 것을 권장한다.

- Clusterware의 OS 계정 : 일반적으로 GI와 ASM을 소유하는 전용 OS 계정인 grid 계정을 사용한다.

공유 Storage

각 Instance는 공유 Storage를 통해 물리적인 data를 공유하며 database에서 사용하는 ASM 및 CFS(Cluster File System)를 구성한다.

공유 Storage에서 Database File은 모든 Node에 동등하게 동시에 엑세스할 수 있어야 한다. 물론 Storage에서 생성된 Disk는 공유 모드가 활성화 되어야 한다.

Oracle RAC Network 구성 요소

• Public IP

각 Node에 대한 고유한 IP로 서버 주소와 동일하다. 일반적으로 Node 관리 목적으로 사용된다.

• Service IP

클라이언트에서 Database 서버의 Public IP를 사용하여 접속할 경우 장애가 발생한 Node에서 세션을 다른 Node로 옮기는데 많은 시간이 걸릴 수 있다. 이때 VIP(Virtual IP)를 사용하여 클라이언트가 node에 장애가 발생했다는 것을 신속하게 인식할 수 있도록 함으로써 다른 Node로 재연결 시간을 향상시킬 수 있다.

• SCAN(Single Client Access Name)

GNS 및 DNS를 사용하여 정의할 수 있다. SCAN을 이용할 경우 Cluster 내 서버 수에 관계없이 Load balancing 및 고가용성을 고려하여 3개의 IP 주소를 권장한다.

• Private IP(Cluster Interconnect)

Cluster Node 별 통신을 위한 IP로 다음과 같은 목적으로 사용한다.

-Resource 동기화를 위해 Cluster에서 Heartbeat 프로세스를 위해서 사용하는 통신 경로

-Instance에서 다른 Instance로 data를 전송(cache fusion)하는 용도로 사용 

Oracle Kernal 구성 요소

오라클 커널의 구성요소는 RAC database의 각 인스턴스에 대한 추가 백그라운드 프로세스의 집합(GRD, GCS, GES)이다. 자세한 설명은 cache fusion에서 설명하겠다.

UPDATE [ table ] a

SET a.[column] = ( SELECT DECODE(SUBSTR(B.[COLUMN],1,3), '[DATA]' , '[DATA]' ) || SUBSRT(B.[COLUMN],4)

                                  FROM [TABLE] B

                                WHERE A.[COLUMN] = B.[COLUMN]

                                       AND B.[COLUMN] = AAA )

WHERE A.[COLUMN]  IN  ( SELECT [COLUMN] 

                                                FROM [TABLE]

                                              WHERE [COLUMN] = ) ; 

Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)

SELECT a.table_name
     , TO_NUMBER ( dbms_xmlgen.getxmltype( 'SELECT COUNT(*) c FROM ' || a.table_name ).Extract('//text()') )
     * COUNT (*) cnt
  FROM user_tables      a
     , user_tab_columns b
 WHERE a.table_name = b.table_name
 GROUP BY a.table_name;

SELECT  TABLE_NAME

        ,  TO_NUMBER(

           EXTRACTVALUE(

               XMLTYPE(

                   DBMS_XMLGEN.GETXML('select count(*) c from '||TABLE_NAME))

               ,'/ROWSET/ROW/C')) COUNT

  FROM  USER_TABLES;