SQL, 병행제어와 회복

정보처리기사 필기 준비하며 정리한 내용입니다.

📌트랜잭션

한꺼번에 모두 수행되어야 할 연산

트랜잭션의 성질

• 원자성 : 모두 반영되든지 아예 안되든지. COMMIT과 ROLLBACK에 의해 보장됨
• 일관성 : 트랜잭션 전-후에 시스템 고정요소의 상태는 같아야
• 독립성, 격리성 : 다른 트랜잭션이 끼어들 수 없다
• 영속성 : 완료된 결과는 영구적으로 반영되어야 한다.

트랜잭션의 상태

1. 실행시작

2. 활동 : 실행중

3-A1. 부분완료 : COMMIT 전 상태

3-A2. 완료 : COMMIT 후

3-B1. 실패 : 오류 발생하여 중단된 상태

3-B2. 철회 : ROLLBACK 후

 

📌SQL 문법의 종류

SQL은 기본적으로 대소문자를 구분하지 않는다.

DDL Data Definition Language 데이터 정의어

• CREATE
  • DB 생성 : CREATE DATABSE db명
    • 테이블보다 큰 개념
  • 테이블 생성 : CREATE TABLE 테이블명
  • 인덱스 생성 : CREATE INDEX 인덱스명 ON 테이블명 ( name );
    • name, age 등 복합 생성도 가능
  • 뷰 생성
    • 회원 테이블에서 이름과 나이만 가져오는 뷰 생성
      • CREATE VIEW 뷰이름 AS SELECT 이름, 나이 FROM 회원
• ALTER : 변경
  • 속성 추가 : ALTER TABLE 테이블명 ADD 속성이름 속성형식 등
  • 속성 변경 : ALTER TABLE 테이블명 MODIFY 속성이름 속성형식
  • 속성 삭제 : ALTER TABLE 테이블명 DROP COLUMN 속성이름
  • INDEX 변경
    • 인덱스명 변경 : ALTER INDEX 인덱스명 RENAME TO 변경할인덱스명
    • 인덱스 속도 저하나 깨졌을 경우 재구성 : ALTER INDEX 인덱스명 REBUILD
    • 인덱스 비활성화 : ALTER INDEX 인덱스명 UNUSABLE
• DROP : 구조 자체를 삭제
  • 테이블 삭제 : DROP TABLE 테이블명
• RENAME
• TRUNCATE : 데이터 삭제
  • 내용 삭제 : TRUNCATE 테이블명
    • 테이블 안의 데이터만 삭제하는 것
• 제약 조건 적용
  • PRIMARY KEY : 테이블 기본키 정의. NOT NULL과 UNIQUE 제약이 기본 포함
  • UNIQUE : 유일한 값을 가져야 함. 동일한 값을 가져서는 안 되는 항목 (기본키와는 다르다)
  • FOREIGN KEY : 외래키.
    • FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
      • 본 테이블에 있는 게 삭제되면 삭제하겠따는 뜻
  • NOT NULL : NULL 일 수 없다
  • CHECK

DML Data Definition Language 데이터 조작어

• SELECT
  • WHERE 조건
  • GROUP BY 그룹
  • HAVING 그룹의 조건
  • ORDER BY 정렬 : ASC 오름차순 / DESE 내림차순
  • 논리연산자 : NOT, AND, OR
  • 와일드카드
    • 여러 문자 : %
    • 한 문자 : _
  • NULL 조건 검색 방법 : 입사일 IS NULL / IS NOT NULL
  • BETWEEN ~ AND ~
  • DISTINCT : 중복없이 출력
    • SELECT DISTINT 컬럼 FROM 테이블명
• INSERT
  • 데이터 추가. 순서 같아야 한다
  • INSERT INTO 테이블명 (이름,나이) VALUES (”이름값”, 나이값)
  • INSERT INTO 테이블1 SELECT 이름, 나이 FROM 테이블2
    • 여러 행 검색하여 추가. 셀렉트 결과를 INSERT 한다는 뜻
• UPDATE
  • 갱신. 셀 단위로 갱신 가능. WHERE 생략하면 모든 행 갱신
  • EX. 모든 직원의 나이를 1살 증가
    • UPDATE 테이블명 SET 나이 = 나이+1
  • EX.개발팀의 부서명을 개발지원팀으로 변경
    • UPDATE 테이블명 SET 부서 = “개발지원팀” WHERE 부서 = “개발팀”
• DELETE
  • 일부 데이터 직접 삭제.
  • 데이터 초기화는 아니다. 1~10행을 삭제하면 다음 데이터는 11번쨰로 등록된 판정.
  • DELETE FROM 테이블명 WHERE 조건

DCL Data Control Language 데이터 제어어 : 권한

• GRANT, REVOKE
• GRANT 권한을 준다
  • GRANT 권한 ON 테이블명 TO 사용자계정
    • 뒤에 아래 옵션을 추가할수도
    • WITH GRANT OPTION 지정둰 권한을 다른 유저에게도 부여할 수 있다
• REVOKE 권한을 회수한다
  • REVOKE 권한 ON 테이블명 FROM 사용자계정
    • 뒤에 아래 옵션을 추가할수도
    • CASCADE 사용자가 부여한 모든 사용자 권한을 같이 회수한다
      • CASCADE는 종속이라는 뜻
• TCL Transaction Contol Language 트랜잭션 제어어
  • COMMIT 트랜잭션 종료 후 수행 내용 DB에 반영
  • ROLLBACK : 오류로 인해 취소됨. 이전 상태로 복구
  • SAVEPOINT : 저장점 정의해 롤백할 수 있는 기능

 

📌집합 연산자

SELECT 절의 컬럼 수가 동일하고 동일 위치에 존재하고 데이터 타입이 상호호환 가능해야.

종류

• UNION : 중복배제
• UNION ALL : 중복되어도 그대로
• INTERSECT : 교집합
• EXCEPT = MINUS : 차집합

문법

SELECT 과목명 FROM 수강정보 WHERE 이름 = "AAA"
UNION
SELECT 과목명 FROM 수강정보 WHERE 이름 = "bbb"

 

📌JOIN

INNER JOIN

공통된 데이터만 추출

SELECT * FROM 테이블명A INNER JOIN 테이블B
ON 테이블명A.id = 테이블명B.id

NATURAL JOIN

자연 조인

동일한 이름,타입을 가진 컬럼이 필요

자동으로 그 컬럼을 조인에 사용한다

FULL OUTER JOIN

좌-우측 테이블 데이터 모두 읽어 중복 데이터 삭제한 결과

LEFT OUTER JOIN

좌측 테이블 기준으로 일치하는 행만 결합

일치 안하면 NULL로 채움

RIGHT OUTER JOIN

잘 안 쓴다

우측 테이블 기준

CROSS JOIN

곱집합

조인 조건이 없다

모든 조합을 반환한다

 

📌서브쿼리

SELECT 안에 SELECT가 있는 것

• SELECT 안

SELECT
	이름,
	(SELECT 주문번호 FROM 주문 WHERE 조건) AS 주문번호
FROM 회원정보
WHERE 회원번호 = "001"

안 쓰는 이유 : 반드시 한가지 값만 리턴되어야 해서 (아닐 시 오류 발생)

• FROM 안

SELECT
	회원번호, 이름
FROM (
	SELECT * FROM 회원정보
	WHERE 이름 LIKE "이%"
}

• WHERE 안
  • 다중행 서브쿼리 연산자
    • IN : 서브쿼리 결과값 포함하면 출력
      • SELECT 회원번호, 이름 FROM 회원정보 WHERE 회원번호 IN ( SELECT 회원번호 FROM 주문 )
    • EXISTS : 행 존재 여부 확인하여 출력 (성능 좋음)
      • SELECT 과목이름 FROM 성적 WHERE EXISTS ( SELECT 학번 FROM 학생 WHERE 학생.학번 = 성적.학번 AND 학생.학과 IN ("전산", "전기") AND 학생.주소 = "경기" }
    • ANY = SOME : 하나라도 만족하면 출력
      • SELECT * FROM 상품 WHERE 단가 > ANY ( SELECT 단가 FROM 상품 WHERE 제조사 = "B" )
    • ALL : 모두 만족하는 값을 출력
      • SELECT * FROM 상품 WHERE 단가 > ALL ( SELECT 단가 FROM 상품 WHERE 제조사 = "B" )

 

절차형 SQL 작성

1) 저장 프로시저

• 쿼리의 집합. 모듈. 함수라고 생각하자!
• 리턴값이 없거나 하나거나 여러개거나
• DB안에 있어서 낮은 처리 성능 → 프로그램단에서 처리하는 게 훨씬 빠르다
• 개발과 DB를 분리시킨거라 유지 보수가 어렵다
• 요즘엔 잘 안쓴다!
• CREATE OR REPLACE PROCEDURE 프로시저명

2) 트리거

개념

이벤트에 반응해 자동으로 실행되는 작업

INSERT DELETE UPDATE 같은 DML문이 수행되었을 때 자동으로 동작하도록 작성된 프로그램

재고 관리 등에서 많이 쓰인다

유형

• 행 트리가 : 행 각각에 대해 실행. FOR EACH ROW 옵션 사용
• 문장 트리거 : 각 문에 대해 단 한번만 실행

예시

CREATE TRIGGER 트리거명
AFTER INSERT ON 재고테이블명 FOR EACH ROW /* 행 트리거 */
BEGIN
	UPDATE 상품테이블
	SET
		nStock = nStock  + NEW.nStock
	WHERE idx = NEW.p_idx;
END

3) 사용자 정의 함수

• 이걸 호출하면 미리 정의한 기능을 수행하는 모듈 (저장 프로시저와 비슷)
• 리턴값이 하나인 게 저장 프로시저와의 차이점
• CREATE OR REPLACE FUNCTION 함수명

 

병행제어와 회복

📌병행제어

여러 트랜잭션들이 동시에 실행되면서도 데이터베이스의 일관성을 유지하게 할 수 있는 기법

미 수행시 문제점

• 갱신 분실
  • 갱신 결과 분실
• 비완료 의존성
  • 실패 후 회복 전에 실패한 갱신결과가 참조됨
• 모순성
• 연쇄 복귀
  • 다른 것도 함께 롤백됨

병행제어 기법

• 로킹 Locking
  • 단위가 크면 Lock 수는 적어지고 병행성은 낮아지고 오버헤드는 감소
• 2단계 로킹 규약
  • 확장단계 : Lock만 허용
  • 축소단계 : Unlock만 허용
• 타임스탬프
  • 정해진 시간 순서
• 낙관적 병행제어
  • 트랜잭션 종료 시 일괄 검사
• 다중 버전 병행 제어
  • 여러 버전의 타임스탬프 비교

📌회복

복구 작업

장애의 유형

• 트랜잭선 장애 : 논리적 오류
• 시스템 장애 : H/W 시스템 자체
• 미디어 장애 : 디스크 자체

Undo & Redo

• Undo 취소
• Redo 재실행

로그 파일

변경사항을 DB에 기록하기 전에 미리 기록해두는 별도의 파일

안전한 하드디스크에!

회복 기법

• 지연 갱신 회복 기법
  • 변경 내용을 로그 파일에만 저장
  • 커밋 발생 전까진 DB에 기록하지 않는다
    • 따라서 Undo가 필요없다 (미실행된건 그냥 폐기하면 됨)
• 즉시 갱신 회복 기법
  • 트랜잭선 수행 도중에도 즉시 DB에 기록하는 것
  • 장애 발생 시 UNDO 취소처리 필요
  • 물론 REDO도 사용
• 검사점 회복 기법
  • checkpoint 이후만 회복
• 그림자 페이징 회복 기법
  • shadow page table을 이용한느 것
  • 트랜잭션 성공하면 그림자테이블 삭제. 실패할 시 그림자를 current로 만듦
• 미디어 회복 기법
  • 물리적 저장장치에 덤프
• ARIES 회복 기법
  • 알고리즘 이용
  • 3단계 : 분석 → REDO → UNDO