DBMS, SQL, 트랜잭션

DBMS (Database Management System)

• 데이터를 저장, 관리, 검색 및 조작하는 소프트웨어 시스템으로, 데이터베이스의 생성과 유지 관리를 책임짐
• DBMS는 데이터의 일관성과 무결성을 유지하며, 다수의 사용자와 애플리케이션이 동시에 데이터에 접근할 수 있도록 조정

 

DBMS의 주요 개념

• 데이터베이스: 관련 데이터를 구조적으로 저장한 집합
• 테이블: 데이터베이스의 기본 저장 단위로, 행(개별 데이터 항목)과 열(데이터의 속성)로 구성
• 스키마(Schema): 데이터베이스의 구조를 정의하는 메타데이터. 테이블, 열, 제약 조건, 관계 등을 나타냄
• 쿼리(Query): 데이터베이스에 대해 수행되는 명령어 또는 요청으로, 데이터를 검색하거나 조작

 

DBMS의 기능

• 데이터 정의: 테이블과 관계를 정의하고 데이터베이스 구조를 설계. DDL(Data Definition Language)을 사용하여 테이블, 인덱스, 뷰 등을 정의
• 데이터 조작: 데이터를 삽입, 수정, 삭제 및 조회. DML(Data Manipulation Language)을 사용하여 데이터를 처리
• 트랜잭션 관리: 트랜잭션의 원자성, 일관성, 격리성, 지속성을 보장하여 데이터베이스의 무결성을 유지
• 데이터 보안: 사용자 인증 및 권한 부여를 통해 데이터에 대한 접근을 제어. 데이터의 기밀성과 무결성을 유지
• 백업 및 복구: 데이터의 정기적인 백업을 수행하고, 시스템 장애나 데이터 손실 시 데이터를 복구
• 성능 최적화: 쿼리 최적화, 인덱스 생성, 캐싱 등 다양한 방법을 사용하여 데이터베이스 성능을 개선
• 데이터 무결성: 데이터의 정확성과 일관성을 보장하기 위해 제약 조건(예: 기본 키, 외래 키, 유일성 제약 등)을 설정

 

DBMS의 유형

• 관계형 DBMS (RDBMS, Relational DBMS)
  • 데이터를 테이블 형태로 저장하며, SQL(Structured Query Language)을 사용하여 데이터의 정의, 조작, 쿼리 등을 수행.
  • 관계형 모델에 기반
  • MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
• 객체지향 DBMS (OODBMS, Object-Oriented DBMS)
  • 객체 지향 프로그래밍 개념을 데이터베이스에 적용하여 데이터를 객체로 저장
  • 객체 간의 관계를 정의
  • ObjectDB, db4o.
• 문서 지향 DBMS
  • JSON, BSON 등의 문서 형식으로 데이터를 저장
  • 데이터 구조가 유연하며, 비정형 데이터를 처리하는 데 적합
  • MongoDB, CouchDB.
• 키-값 저장소 (Key-Value Store)
  • 데이터를 키와 값의 쌍으로 저장
  • 매우 높은 성능을 제공하며, 간단한 조회 및 저장 작업에 적합
  • Redis, Riak.
• 열 지향 DBMS (Columnar DBMS)
  • 데이터를 열 단위로 저장하여 대규모 데이터 분석 작업에 적합
  • 데이터 압축과 빠른 읽기 성능을 제공
  • Apache Cassandra, HBase.
• 그래프 DBMS (Graph DBMS)
  • 데이터와 그 관계를 그래프 구조로 저장
  • 복잡한 관계를 효율적으로 표현하고 분석
  • Neo4j, Amazon Neptune.

---

SQL (Structured Query Language)

데이터베이스 관리 시스템(DBMS)에서 데이터를 정의, 조작, 제어 및 조회하는 데 사용되는 표준 언어

 

 

데이터 정의 언어 (DDL: Data Definition Language)

DDL은 데이터베이스의 구조를 정의하는 데 사용

• CREATE: 새로운 데이터베이스 객체(테이블, 인덱스 등)를 생성

CREATE TABLE 테이블명 (
  컬럼명1 데이터타입 제약조건,
  컬럼명2 데이터타입 제약조건,
  ...
);

• ALTER: 기존 데이터베이스 객체를 수정

ALTER TABLE 테이블명
ADD 컬럼명 데이터타입 제약조건;

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

• DROP: 데이터베이스 객체를 삭제
• TRUNCATE: 테이블의 모든 데이터를 삭제하지만 테이블 구조는 유지

DROP TABLE 테이블명;

TRUNCATE TABLE 테이블명;

 

 

데이터 조작 언어 (DML: Data Manipulation Language)

DML은 데이터베이스 내의 데이터를 조작하는 데 사용

• SELECT: 데이터 조회

SELECT 컬럼명1, 컬럼명2
FROM 테이블명
WHERE 조건;

• INSERT: 새로운 데이터 추가

INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...)
VALUES (값1, 값2, ...);

• UPDATE: 기존 데이터 수정

UPDATE 테이블명
SET 컬럼명1 = 값1, 컬럼명2 = 값2
WHERE 조건;

• DELETE: 데이터 삭제

DELETE FROM 테이블명
WHERE 조건;

 

 

데이터 제어 언어 (DCL: Data Control Language)

DCL은 데이터베이스에 대한 접근을 제어하는 데 사용

• GRANT: 사용자에게 특정 권한을 부여

GRANT 권한 ON 객체 TO 사용자;

• REVOKE: 사용자로부터 특정 권한을 회수

REVOKE 권한 ON 객체 FROM 사용자;

 

 

트랜잭션 제어 언어 (TCL: Transaction Control Language)

TCL은 트랜잭션을 제어하는 데 사용

• COMMIT: 트랜잭션의 변경 사항을 확정
• ROLLBACK: 트랜잭션의 변경 사항을 취소
• SAVEPOINT: 트랜잭션 내에서 되돌릴 수 있는 지점을 설정
• SET TRANSACTION: 트랜잭션의 특성을 설정

COMMIT;

ROLLBACK;

SAVEPOINT 저장점명;

SET TRANSACTION READ ONLY;

 

 

집계 함수 

COUNT, SUM, AVG, MIN, MAX

SELECT COUNT(컬럼명)
FROM 테이블명
WHERE 조건;

 

JOINs

테이블 간의 관계를 기반으로 데이터를 결합하는 데 사용

• INNER JOIN: 두 테이블에서 조건에 일치하는 행만 반환
• LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 조건에 일치하는 행을 반환
• RIGHT JOIN: 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 조건에 일치하는 행을 반환
• FULL JOIN: 두 테이블에서 조건에 일치하는 모든 행을 반환
• CROSS JOIN: 두 테이블의 카티시안 곱을 반환

SELECT A.컬럼명, B.컬럼명
FROM 테이블A A
INNER JOIN 테이블B B ON A.조건 = B.조건;

SELECT A.컬럼명, B.컬럼명
FROM 테이블A A
LEFT JOIN 테이블B B ON A.조건 = B.조건;

SELECT A.컬럼명, B.컬럼명
FROM 테이블A A
RIGHT JOIN 테이블B B ON A.조건 = B.조건;

SELECT A.컬럼명, B.컬럼명
FROM 테이블A A
FULL JOIN 테이블B B ON A.조건 = B.조건;

SELECT A.컬럼명, B.컬럼명
FROM 테이블A A
CROSS JOIN 테이블B B;

 

View

저장된 쿼리로서, 복잡한 쿼리를 단순하게 사용할 수 있게 하는 가상의 테이블

• CREATE VIEW: 뷰 생성
• DROP VIEW: 뷰 삭제

CREATE VIEW 뷰명 AS
SELECT 컬럼명
FROM 테이블명
WHERE 조건;

DROP VIEW 뷰명;

 

 

---

 

트랜잭션 (Transactional Operation)

• 데이터베이스 시스템에서 데이터를 처리하는 핵심 단위로, 데이터베이스의 일관성과 무결성을 유지
• 여러 사용자 또는 애플리케이션의 데이터 접근과 조작을 조정하여 충돌이나 오류를 방지

 

트랜잭션의 개념

• 원자성(Atomicity)
  • 트랜잭션 내의 모든 작업이 전부 성공적으로 완료되거나, 실패하면 전부 롤백되어 데이터베이스의 상태가 트랜잭션 시작 전으로 되돌아감
  • ‘모두 또는 아무것도’라는 원칙을 따름
• 일관성(Consistency)
  • 트랜잭션이 성공적으로 완료되면 데이터베이스는 일관된 상태를 유지
  • 트랜잭션이 데이터베이스의 무결성을 보장하며, 비즈니스 규칙과 제약 조건이 유지됨
• 격리성(Isolation)
  • 동시에 실행되는 트랜잭션 간에는 상호 간섭이 없어야 함. 즉,  트랜잭션이 실행 중일 때 다른 트랜잭션의 결과가 영향을 미치지 않아야 함
  • 각 트랜잭션은 독립적으로 실행되어야 함
• 지속성(Durability)
  • 트랜잭션이 성공적으로 커밋되면, 해당 트랜잭션의 결과는 시스템의 장애가 발생하더라도 지속적으로 유지되어야 함
  • 커밋된 데이터는 영구적으로 저장됨

 

트랜잭션의 상태

• 활동 중(Active): 트랜잭션이 시작되었지만 아직 완료되지 않은 상태
• 커밋(Committed): 트랜잭션의 모든 작업이 성공적으로 완료되어 데이터베이스에 반영된 상태
• 롤백(Rolled Back): 트랜잭션 중 오류가 발생하거나 의도적으로 취소된 상태로, 모든 작업이 원래 상태로 되돌려짐
• 대기(Deferred): 트랜잭션이 실행되었지만 결과를 즉시 반영할 수 없는 상태로, 주로 트랜잭션이 여러 단계로 이루어진 경우

 

트랜잭션의 격리 수준

트랜잭션의 격리 수준은 트랜잭션 간의 상호 작용을 제어함

• READ UNCOMMITTED
  • 트랜잭션이 커밋되지 않은 데이터를 읽을 수 있음
  • 가장 낮은 격리 수준으로, '더티 리드'가 발생 가능
• READ COMMITTED
  • 트랜잭션은 커밋된 데이터만 읽을 수 있음
  • '더티 리드'는 방지되지만 '논리적 비관계'가 발생할 수 있음
• REPEATABLE READ
  • 트랜잭션이 시작된 시점의 데이터만 읽을 수 있으며, 다른 트랜잭션이 데이터를 수정해도 이 트랜잭션은 일관된 읽기 결과를 보장받음
  • '논리적 비관계'는 방지되지만 '팬텀 리드'는 발생할 수 있음
• SERIALIZABLE
  • 가장 높은 격리 수준
  • 트랜잭션이 순차적으로 실행되는 것과 같은 효과를 제공
  • '팬텀 리드'를 방지하지만 성능이 저하될 수 있음

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

 

트랜잭션의 병행성 문제와 해결

트랜잭션이 병행적으로 실행될 때 발생할 수 있는 문제

• 더티 리드(Dirty Read): 한 트랜잭션이 아직 커밋되지 않은 다른 트랜잭션의 데이터를 읽는 경우. 이는 READ UNCOMMITTED 격리 수준에서 발생]
• 논리적 비관계(Non-repeatable Read): 한 트랜잭션이 데이터를 읽은 후, 다른 트랜잭션이 해당 데이터를 수정하여 이후에 읽을 때 다른 결과가 나오는 경우. 이는 READ COMMITTED 격리 수준에서 발생 가능
• 팬텀 리드(Phantom Read): 한 트랜잭션이 특정 범위의 데이터를 읽은 후, 다른 트랜잭션이 데이터를 삽입하거나 삭제하여 다음 읽기 시 결과가 달라지는 경우. 이는 REPEATABLE READ 격리 수준에서 발생 가능