← 정보처리기사 실기 개념정리 목차

SQL 완전 정리 ⑧ DML·트랜잭션·권한

앞의 편들이 "데이터를 어떻게 읽는가(SELECT)"였다면, 이 편은 데이터를 바꾸는 쪽이다. DML(INSERT·UPDATE·DELETE·MERGE)로 행을 바꾸고, TCL(COMMIT·ROLLBACK·SAVEPOINT)로 그 변경을 한 덩어리(트랜잭션) 로 묶어 확정하거나 되돌리며, 여러 사용자가 동시에 바꿀 때 생기는 이상 현상을 격리 수준과 락으로 막는다. 마지막으로 DCL(GRANT·REVOKE)로 "누가 무엇을 바꿀 수 있는가"를 정한다. 본문의 쿼리·결과 표는 PostgreSQL 14 에서 실제로 실행해 맞췄다(두 세션이 엇갈리는 동시성 예제도 세션 두 개를 띄워 단계별로 재현했다). MERGE·Oracle 시스템 권한처럼 PG14 에서 못 돌리는 문법은 소개만 한다.


0. 이 편에서 쓰는 테이블

부서

dnodnameloc
10개발서울
20영업부산
30인사서울
40기획NULL

사원

enoenamednojobsalmgrhiredatebonus
1김하늘10팀장500NULL2019-03-01NULL
2이도윤10대리35012021-07-1530
3박서준10사원28022023-01-02NULL
4최지우20팀장450NULL2018-11-2050
5정민재20사원30042022-05-0920
6한유나20사원30042024-02-01NULL
7오세린NULL사원250NULL2025-01-06NULL

참여

enopnohours
110020
210040
310030
220010
520025

이 편에서는 데이터를 실제로 바꾸므로, 각 예제는 위 원본 상태에서 출발한다고 가정한다(예제끼리 효과가 누적되지 않는다). 필요할 때마다 작은 테이블(신입, 계좌 등)을 추가한다.

DML 문장마다 DBMS 가 돌려주는 영향받은 행 수(PostgreSQL 의 INSERT 0 3, UPDATE 2, DELETE 1)도 함께 적는다 — "몇 행이 바뀌는가"는 결과 표만큼 자주 묻는다.


1. INSERT — 행 넣기

1.1 문법 골격

INSERT INTO 테이블 [(열1, 열2, …)] VALUES (값1, 값2, …) [, (값1, 값2, …) …];   -- 값을 직접
INSERT INTO 테이블 [(열1, 열2, …)] SELECT …;                                  -- 조회 결과를 통째로

예제용으로 기본값이 있는 테이블을 하나 만든다.

CREATE TABLE 신입 (
  eno      INT PRIMARY KEY,
  ename    VARCHAR(10) NOT NULL,
  dno      INT,
  sal      INT  DEFAULT 200,
  hiredate DATE DEFAULT DATE '2026-03-02'
);

1.2 열 목록을 쓰느냐 생략하느냐

형태규칙
열 목록 생략테이블에 정의된 모든 열을, 정의된 순서대로 값을 줘야 한다
열 목록 명시적은 열만, 적은 순서대로 값을 준다. 테이블 정의 순서와 달라도 된다
목록에서 빠진 열DEFAULT 가 있으면 기본값, 없으면 NULL. 그런데 그 열이 NOT NULL 이고 기본값이 없으면 오류
INSERT INTO 신입 VALUES (11, '강하준', 10, 220, DATE '2026-03-02');  -- 전 열, 정의 순서
INSERT INTO 신입 (eno, ename) VALUES (12, '윤서아');                  -- dno·sal·hiredate 빠짐
INSERT INTO 신입 (ename, eno, dno) VALUES ('임도현', 13, 20);         -- 순서를 바꿔 적어도 OK
INSERT INTO 신입 VALUES (14, '송지안', NULL, DEFAULT, DEFAULT);       -- NULL·DEFAULT 키워드
INSERT INTO 신입 (eno, ename, sal) VALUES (15, '배준서', NULL);       -- 명시적 NULL
SELECT * FROM 신입 ORDER BY eno;
enoenamednosalhiredate
11강하준102202026-03-02
12윤서아NULL2002026-03-02
13임도현202002026-03-02
14송지안NULL2002026-03-02
15배준서NULLNULL2026-03-02

읽을 점:

  • 12번: 목록에 없는 dno 는 기본값이 없어 NULL, sal·hiredate 는 DEFAULT 값이 들어갔다.
  • 14번: DEFAULT 는 "이 자리에 기본값을 넣어라"라는 키워드다. 열 목록을 생략한 채로도 기본값을 쓸 수 있다.
  • 15번: 명시적으로 NULL 을 주면 DEFAULT 가 적용되지 않는다. 기본값은 "값을 안 줬을 때"만 쓰이고, "NULL 을 줬을 때"는 NULL 이 들어간다. 자주 틀리는 지점이다.

1.3 제약 위반은 문장 전체가 실패

INSERT INTO 신입 (eno, dno) VALUES (17, 10);
-- ERROR: null value in column "ename" ... violates not-null constraint
INSERT INTO 신입 (eno, ename) VALUES (12, '중복');
-- ERROR: duplicate key value violates unique constraint "신입_pkey"

ename 은 NOT NULL 인데 기본값이 없으니 목록에서 빼면 NULL 이 들어가려다 거부된다. 기본키 중복도 마찬가지.

엔진 차이 — 값 개수가 열보다 적을 때: 열 목록을 생략하고 VALUES (16, '홍길동') 처럼 값을 덜 주면 표준·Oracle(ORA-00947 값의 수가 충분하지 않습니다)·MySQL(Column count doesn't match)은 오류다. PostgreSQL 만 예외적으로 뒤쪽 열을 기본값으로 채워 받아 준다(실행 결과: 16 | 홍길동 | NULL | 200 | 2026-03-02). 시험 답은 표준대로 "오류"다. 열 목록을 생략하면 값은 열 개수만큼 줘야 한다고 외운다.

1.4 다중행 INSERT

INSERT INTO 신입 (eno, ename, dno) VALUES (21, '가', 30), (22, '나', 30), (23, '다', 40);
-- INSERT 0 3

한 문장으로 세 행. 표준(SQL:1999 이후)·PostgreSQL·MySQL·SQLite 가 지원한다. Oracle 은 오랫동안 이 형식이 없어서 INSERT ALL INTO … INTO … SELECT * FROM DUAL 을 썼다(23ai 부터 다중행 VALUES 지원).

한 문장은 원자적이다. 셋 중 하나라도 실패하면 셋 다 들어가지 않는다.

INSERT INTO 신입 (eno, ename) VALUES (24, '라'), (12, '중복'), (25, '마');
-- ERROR: duplicate key ... Key (eno)=(12) already exists.
-- 24·25 도 들어가지 않는다 (실행 후 행 수 변화 없음 확인)

1.5 INSERT … SELECT

INSERT INTO 신입 (eno, ename, dno, sal)
SELECT eno + 100, ename, dno, sal
FROM   사원
WHERE  job = '팀장';
-- INSERT 0 2
SELECT * FROM 신입 WHERE eno > 100;
enoenamednosalhiredate
101김하늘105002026-03-02
104최지우204502026-03-02
  • VALUES 를 쓰지 않는다. INSERT INTO … VALUES (SELECT …) 는 틀린 문법이다.
  • SELECT 의 열 개수·순서·타입이 INSERT 열 목록과 맞아야 한다. 이름은 상관없다(위치로 대응).
  • 조회 결과가 0행이면 0행 삽입(오류 아님), 10행이면 10행 삽입.
  • 목록에 없는 hiredate 는 역시 DEFAULT.

비슷한 문장 — 테이블을 새로 만들며 복사: CREATE TABLE 사본 AS SELECT …(표준·Oracle·PG·MySQL), SELECT … INTO 사본 FROM …(SQL Server·PG). INSERT … SELECT 는 이미 있는 테이블에 넣는 것이고, 이 둘은 테이블을 새로 만든다. CTAS 로 만든 테이블에는 열 이름·타입과 데이터만 오고 기본키·외래키·DEFAULT 는 복사되지 않는다(Oracle 은 NOT NULL 만 따라온다).


2. UPDATE — 값 바꾸기

2.1 문법 골격

UPDATE 테이블 [별칭]
SET    열1 = 식1 [, 열2 = 식2 …]
[WHERE 조건];

동작 순서: ① WHERE 로 대상 행을 고른다 → ② 고른 행마다 SET 의 식을 그 행의 갱신 전 값으로 계산한다 → ③ 계산 결과를 한꺼번에 쓴다. WHERE 를 통과한 행 수가 영향받은 행 수(UPDATE n)다.

2.2 산술 갱신 — 자기 자신을 참조

UPDATE 사원 SET sal = sal * 1.1 WHERE dno = 10;      -- UPDATE 3
SELECT eno, ename, sal FROM 사원 WHERE dno = 10 ORDER BY eno;
enoenamesal
1김하늘550
2이도윤385
3박서준308

sal = sal * 1.1 은 "오른쪽(현재 값)을 계산해 왼쪽 열에 넣는다"는 대입이지 방정식이 아니다. sal 이 INT 이므로 결과는 정수로 반올림되어 저장된다.

NULL 이 섞이면 산술 결과도 NULL 이다.

UPDATE 사원 SET bonus = bonus + 10 WHERE dno = 20;   -- UPDATE 3
enoenamebonus 전bonus 후
4최지우5060
5정민재2030
6한유나NULLNULL

한유나도 WHERE 를 통과했으므로 "갱신된 행" 3개에 들어간다. 하지만 NULL + 10 = NULL 이라 값은 그대로다. 0 으로 시작하게 하려면 bonus = COALESCE(bonus, 0) + 10.

2.3 여러 열 동시 갱신 — 모두 "이전 값" 기준

CREATE TABLE t (a INT, b INT);
INSERT INTO t VALUES (1, 2);
UPDATE t SET a = b, b = a;
SELECT * FROM t;
ab
21

표준 SQL 에서 SET 의 모든 식은 갱신 전 행을 보고 계산된다. 그래서 a = b 는 이전 b(2), b = a 는 이전 a(1) — 임시 변수 없이 스왑이 된다. 프로그래밍 언어의 a = b; b = a;(둘 다 2가 됨)와 다르다. PostgreSQL·Oracle·SQLite 가 이렇게 동작한다(PG·SQLite 실행 확인).

MySQL 은 예외 — MySQL 의 단일 테이블 UPDATE 는 SET 을 왼쪽부터 차례로 적용하고, 뒤쪽 식은 앞에서 이미 바뀐 값을 본다(공식 문서에 명시된 비표준 동작). 같은 문장이 MySQL 에서는 a = b → a = 2, b = a → b = 2(새 a) 가 되어 결과가 (2, 2) 다.

엔진UPDATE t SET a = b, b = a (a=1, b=2)
표준·Oracle·PostgreSQL·SQLitea=2, b=1 (스왑)
MySQLa=2, b=2

2.4 서브쿼리로 SET — 매칭이 없으면 NULL 이 된다

급여 조정표가 따로 있다고 하자.

조정

enonew_sal
3300
6330

박서준·한유나 두 사람만 바꾸려고 이렇게 쓰면:

UPDATE 사원
SET    sal = (SELECT new_sal FROM 조정 WHERE 조정.eno = 사원.eno);   -- UPDATE 7 (!)
enoenamesal
1김하늘NULL
2이도윤NULL
3박서준300
4최지우NULL
5정민재NULL
6한유나330
7오세린NULL

WHERE 가 없으니 7행 모두 대상이고, 조정표에 없는 사원은 스칼라 서브쿼리가 0행 → NULL 을 돌려주므로 급여가 NULL 로 지워졌다. 서브쿼리 SET 의 대표 사고다. 막는 방법은 세 가지:

-- ① WHERE EXISTS 로 "조정표에 있는 사원만" 대상으로 (표준, 모든 엔진)
UPDATE 사원
SET    sal = (SELECT new_sal FROM 조정 WHERE 조정.eno = 사원.eno)
WHERE  EXISTS (SELECT 1 FROM 조정 WHERE 조정.eno = 사원.eno);       -- UPDATE 2

-- ② 매칭이 없으면 원래 값 유지 (7행을 "갱신"하지만 5행은 같은 값)
UPDATE 사원
SET    sal = COALESCE((SELECT new_sal FROM 조정 WHERE 조정.eno = 사원.eno), sal);  -- UPDATE 7

-- ③ 조인 UPDATE (엔진별 문법)
UPDATE 사원 e SET sal = j.new_sal FROM 조정 j WHERE j.eno = e.eno;               -- PostgreSQL: UPDATE 2
-- MySQL:  UPDATE 사원 e JOIN 조정 j ON j.eno = e.eno SET e.sal = j.new_sal;
-- Oracle: MERGE 로 쓰는 것이 일반적 (§4)

①·②·③ 모두 결과 급여는 500, 350, 300, 450, 300, 330, 250 으로 같다(실행 확인). 영향받은 행 수만 다르다. WHERE 절 IN (SELECT eno FROM 조정) 으로 써도 ①과 같다.

스칼라 서브쿼리는 1행 이하여야 한다.

UPDATE 사원 SET sal = (SELECT sal FROM 사원 WHERE dno = 10) WHERE eno = 7;
-- ERROR: more than one row returned by a subquery used as an expression
UPDATE 사원 SET sal = (SELECT MAX(sal) FROM 사원 WHERE dno = 20) WHERE eno = 7;   -- OK, 450

여러 열을 한 서브쿼리로 (표준 행 값 생성자, Oracle·PG 지원):

UPDATE 사원 SET (sal, bonus) = (SELECT sal, bonus FROM 사원 WHERE eno = 2) WHERE eno = 3;
-- 박서준: sal 350, bonus 30

2.5 WHERE 를 빠뜨리면

UPDATE 사원 SET job = '사원';      -- UPDATE 7 — 팀장·대리까지 전원 '사원'

문법 오류가 아니라 정상 실행된다는 것이 위험하다. WHERE 없는 UPDATE·DELETE 는 테이블 전체가 대상이다. 실무 습관: ① 같은 WHERE 로 SELECT COUNT(*) 를 먼저 돌려 대상 수 확인, ② 트랜잭션을 열고(BEGIN) 실행한 뒤 행 수를 보고 COMMIT/ROLLBACK, ③ MySQL 은 sql_safe_updates 옵션으로 키 조건 없는 UPDATE·DELETE 를 막을 수 있다.


3. DELETE — 행 지우기

3.1 문법 골격

DELETE FROM 테이블 [별칭] [WHERE 조건];
  • DELETE 는 행 단위로 지운다. 특정 열 값만 비우려면 UPDATE … SET 열 = NULL 이다. DELETE 열 FROM … 같은 문법은 없다.
  • FROM 은 표준에서 필수. Oracle·SQL Server 는 DELETE 사원 WHERE … 처럼 생략을 허용한다.
  • WHERE 생략 = 전체 삭제(§2.5 와 같은 위험). 테이블 구조는 남는다.

3.2 서브쿼리 조건으로 삭제

-- 개발부(10) 사원의 참여 기록 삭제
DELETE FROM 참여 WHERE eno IN (SELECT eno FROM 사원 WHERE dno = 10);   -- DELETE 4
enopnohours
520025
-- 어떤 프로젝트에도 참여하지 않은 사원 삭제 (상관 서브쿼리)
DELETE FROM 사원 e
WHERE NOT EXISTS (SELECT 1 FROM 참여 c WHERE c.eno = e.eno);         -- DELETE 3

남는 사원: 김하늘·이도윤·박서준·정민재 (최지우·한유나·오세린 삭제).

-- 평균(347.14…) 미만 급여 삭제 — 같은 테이블을 서브쿼리에서 참조
DELETE FROM 사원 WHERE sal < (SELECT AVG(sal) FROM 사원);              -- DELETE 4

남는 사원: 김하늘(500)·이도윤(350)·최지우(450). 서브쿼리의 평균은 삭제 전 테이블 기준으로 한 번 계산된다 (지우면서 평균이 바뀌어 연쇄적으로 더 지워지지 않는다). 단 MySQL 은 삭제 대상 테이블을 같은 문장의 서브쿼리에서 직접 읽는 것을 막는다(Error 1093) — (SELECT AVG(sal) FROM (SELECT sal FROM 사원) x) 처럼 파생 테이블로 한 번 감싸야 한다.

NOT IN 함정은 DELETE 에서도 그대로 — DELETE FROM 사원 WHERE dno NOT IN (SELECT dno FROM 프로젝트); 는 0행 삭제다. 프로젝트.dno 에 NULL(신규 프로젝트)이 있어 dno NOT IN (10, 20, NULL) 이 어떤 행에서도 TRUE 가 되지 않는다(⑤편). WHERE dno = NULL 도 0행 — IS NULL 로 써야 한다.

3.3 조인 기반 삭제 — 엔진마다 문법이 다르다

"직급이 '사원'인 사람의 참여 기록 삭제"를 다른 테이블 조건으로 지우는 경우.

엔진문법
표준(모든 엔진)DELETE FROM 참여 WHERE eno IN (SELECT eno FROM 사원 WHERE job = '사원');
PostgreSQLDELETE FROM 참여 c USING 사원 e WHERE e.eno = c.eno AND e.job = '사원';
MySQLDELETE c FROM 참여 c JOIN 사원 e ON e.eno = c.eno WHERE e.job = '사원';
SQL ServerDELETE c FROM 참여 c JOIN 사원 e ON … (MySQL 과 비슷)
Oracle조인 DELETE 문법 없음 → IN/EXISTS 서브쿼리

PostgreSQL 실행 결과: DELETE 2 — 박서준(3)·정민재(5)의 기록이 지워지고 (1,100) (2,100) (2,200) 이 남는다. MySQL 의 DELETE c FROM … 에서 DELETE 바로 뒤의 별칭이 "어느 테이블에서 지울지"를 정한다 (DELETE c, e FROM … 이면 두 테이블에서 동시에 지운다).

3.4 DELETE vs TRUNCATE vs DROP

DELETETRUNCATEDROP
분류DMLDDLDDL
지우는 것조건에 맞는 행(WHERE 가능)모든 행(WHERE 불가)테이블 자체(구조·데이터·인덱스)
롤백가능Oracle·MySQL 불가(묵시적 커밋) / PG 는 트랜잭션 안이면 가능위와 같음
속도행마다 로그 → 느림저장 공간을 통째로 비움 → 빠름빠름
트리거행 삭제 트리거 동작동작 안 함—

PG 에서 BEGIN; TRUNCATE 참여; ROLLBACK; 후 SELECT count(*) FROM 참여 는 5 — 되살아난다(실행 확인, §6.5 참고).


4. MERGE 와 UPSERT — "있으면 UPDATE, 없으면 INSERT"

외부에서 받은 데이터를 기존 테이블에 반영할 때, 키가 이미 있으면 갱신하고 없으면 새로 넣어야 한다. INSERT 와 UPDATE 를 따로 쓰면 "확인 → 넣기" 사이에 다른 트랜잭션이 끼어들 수 있으므로 한 문장으로 묶는 문법이 생겼다.

예제: 사원별 급여만 담은 급여(eno PK, sal) 테이블(사원 7명의 현재 급여)에, 조정표를 반영한다.

조정 (이번엔 신규 8번 포함)

enonew_sal
3300
6330
8230

4.1 MERGE — 표준 SQL:2003 (Oracle 9i+, SQL Server, PostgreSQL 15+)

MERGE INTO 급여 g                         -- 대상(target)
USING 조정 j                              -- 원천(source)
   ON (g.eno = j.eno)                     -- 매칭 조건
WHEN MATCHED THEN
     UPDATE SET g.sal = j.new_sal         -- PG 는 SET sal = … (대상 열에 별칭 안 붙임)
WHEN NOT MATCHED THEN
     INSERT (eno, sal) VALUES (j.eno, j.new_sal);

동작: 원천의 각 행을 ON 조건으로 대상과 맞춰 보고, 짝이 있으면 MATCHED 절, 없으면 NOT MATCHED 절을 실행한다.

원천 행대상에 있나실행결과
(3, 300)있음 (280)UPDATE3 → 300
(6, 330)있음 (300)UPDATE6 → 330
(8, 230)없음INSERT8, 230 추가
  • Oracle 은 WHEN MATCHED THEN UPDATE … DELETE WHERE … 로 갱신 뒤 조건부 삭제도 붙일 수 있고, 각 절에 WHERE 를 달 수 있다.
  • Oracle 에서는 ON 절에 쓴 열을 UPDATE SET 으로 바꿀 수 없다(ORA-38104).
  • 이 환경의 PostgreSQL 은 14 라 MERGE 를 실행하지 않았다. 같은 결과를 아래 ON CONFLICT 로 확인했다.

4.2 UPSERT — PostgreSQL ON CONFLICT (9.5+)

INSERT INTO 급여 (eno, sal)
SELECT eno, new_sal FROM 조정
ON CONFLICT (eno) DO UPDATE SET sal = EXCLUDED.sal;      -- INSERT 0 3
SELECT * FROM 급여 ORDER BY eno;
enosal
1500
2350
3300
4450
5300
6330
7250
8230
  • ON CONFLICT (eno) — 이 열의 고유 제약(PK·UNIQUE) 이 충돌하면 INSERT 대신 뒤의 동작을 한다. 고유 제약이 없는 열은 지정할 수 없다.
  • EXCLUDED — "넣으려다 거부된 행"을 가리키는 가상 테이블. EXCLUDED.sal 이 새 값, 급여.sal 이 기존 값이다.
  • 보고되는 INSERT 0 3 은 삽입 1 + 갱신 2 를 합친 수다.
INSERT INTO 급여 VALUES (1, 999), (9, 210) ON CONFLICT (eno) DO NOTHING;   -- INSERT 0 1
-- 1번은 충돌이라 무시(500 유지), 9번만 들어간다

INSERT INTO 급여 VALUES (2, 100)
ON CONFLICT (eno) DO UPDATE SET sal = EXCLUDED.sal WHERE 급여.sal < EXCLUDED.sal;   -- INSERT 0 0
-- 조건부 갱신: 기존 350 < 100 이 거짓이라 아무것도 안 바뀐다

4.3 MySQL ON DUPLICATE KEY UPDATE

INSERT INTO 급여 (eno, sal) VALUES (3, 300), (8, 230)
ON DUPLICATE KEY UPDATE sal = VALUES(sal);          -- 8.0.20+ 는 별칭 방식 권장:
-- INSERT INTO 급여 VALUES (3,300),(8,230) AS new ON DUPLICATE KEY UPDATE sal = new.sal;

충돌 대상을 지정하지 않는다 — 어느 PK/UNIQUE 든 걸리면 UPDATE 로 바뀐다. 영향받은 행 수는 삽입 1, 갱신 2, 값이 같아 변화 없음 0 으로 센다. REPLACE INTO 는 충돌 시 기존 행을 DELETE 후 INSERT 하는 다른 동작이다(빠진 열은 기본값으로 리셋, 삭제 트리거 발동).

기능표준·Oracle·SQL Server·PG15+PostgreSQLMySQL
있으면 갱신·없으면 삽입MERGE … WHEN MATCHED / NOT MATCHEDON CONFLICT (키) DO UPDATEON DUPLICATE KEY UPDATE
있으면 무시MERGE 에 NOT MATCHED 만ON CONFLICT DO NOTHINGINSERT IGNORE
원천테이블·서브쿼리VALUES·SELECTVALUES·SELECT

5. 트랜잭션 — 논리적 작업 단위

트랜잭션은 데이터베이스의 상태를 바꾸는 하나의 논리적 작업 단위다. 계좌이체 "A 에서 300 빼고 B 에 300 더하기"는 UPDATE 두 문장이지만 업무상 하나다 — 하나만 반영되면 돈이 사라지거나 생긴다. 그래서 둘을 묶어 전부 반영(COMMIT) 하거나 전부 취소(ROLLBACK) 한다.

5.1 ACID — 트랜잭션이 지켜야 할 네 성질

성질뜻깨지면 생기는 일담당하는 DBMS 기능
Atomicity 원자성전부 반영되거나 전혀 반영되지 않는다 (All or Nothing)이체 중 장애 → A 만 빠지고 B 엔 안 들어감회복(Recovery) 기법 — 로그를 이용한 UNDO, 트랜잭션 관리자
Consistency 일관성실행 전후 모두 무결성 제약을 만족하는 일관된 상태잔액이 음수, 총액이 달라짐무결성 제약(PK·FK·CHECK) 검사 + 올바르게 짠 트랜잭션
Isolation 독립성(격리성)동시에 실행되는 트랜잭션이 서로의 중간 결과를 보지 못한다갱신 손실·Dirty Read 등 (§7)동시성 제어(Concurrency Control) — 락·MVCC, 격리 수준
Durability 영속성(지속성)COMMIT 된 결과는 장애가 나도 사라지지 않는다커밋 직후 정전 → 반영 분실회복 기법 — 로그 선기록(WAL), REDO, 백업

외우는 연결: 원자성·영속성 = 회복, 독립성 = 동시성 제어, 일관성 = 무결성 제약. 원자성은 "취소할 수 있어야(UNDO)", 영속성은 "다시 적용할 수 있어야(REDO)" 하므로 둘 다 로그에 기댄다.

5.2 트랜잭션 상태

다이어그램 로딩 중…
상태설명
활동 (Active)트랜잭션이 실행 중인 상태
부분 완료 (Partially Committed)마지막 연산까지 실행했지만 아직 COMMIT 이 확정되기 전. 결과가 아직 버퍼(메모리)에 있다
완료 (Committed)COMMIT 이 성공해 결과가 영구 반영된 상태
실패 (Failed)오류 등으로 더 진행할 수 없음을 발견한 상태
철회 (Aborted)ROLLBACK 되어 시작 전 상태로 되돌아간 상태. 이후 재시작하거나 폐기한다

함정 — "부분 완료"는 "일부만 반영됨"이 아니다. 연산은 전부 끝났고 커밋만 남은 상태다. 부분 완료에서도 디스크 반영 중 장애가 나면 실패 → 철회로 갈 수 있다.


6. TCL — COMMIT · ROLLBACK · SAVEPOINT

6.1 문법

BEGIN;  /  START TRANSACTION;        -- 명시적 시작 (Oracle 은 첫 DML 이 자동으로 시작)
COMMIT;                               -- 변경 확정, 트랜잭션 종료
ROLLBACK;                             -- 시작 시점으로 전부 취소, 트랜잭션 종료
SAVEPOINT 이름;                        -- 중간 저장점 표시
ROLLBACK TO [SAVEPOINT] 이름;          -- 그 저장점 이후만 취소 (트랜잭션은 계속)
RELEASE SAVEPOINT 이름;                -- 저장점 제거 (PG·MySQL)

6.2 이체 — COMMIT 과 실패 시 ROLLBACK

CREATE TABLE 계좌 (id CHAR(1) PRIMARY KEY, bal INT CHECK (bal >= 0));
INSERT INTO 계좌 VALUES ('A', 1000), ('B', 500);

BEGIN;
UPDATE 계좌 SET bal = bal - 300 WHERE id = 'A';
UPDATE 계좌 SET bal = bal + 300 WHERE id = 'B';
COMMIT;                                   -- A 700, B 800

잔액보다 큰 2000 을 이체하면:

BEGIN;
UPDATE 계좌 SET bal = bal - 2000 WHERE id = 'A';
-- ERROR: new row … violates check constraint "계좌_bal_check"   (A 가 -1300 이 되려 함)
UPDATE 계좌 SET bal = bal + 2000 WHERE id = 'B';
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT;                                   -- PG 는 COMMIT 대신 ROLLBACK 을 수행하고 "ROLLBACK" 을 출력
SELECT * FROM 계좌;                        -- A 700, B 800 (변화 없음)

CHECK 제약(일관성)이 잘못된 상태를 거부했고, 트랜잭션 전체가 취소(원자성)됐다.

엔진 차이 — 오류 난 뒤의 트랜잭션

엔진문장 하나가 실패하면
PostgreSQL트랜잭션 전체가 aborted 상태. 이후 문장은 모두 거부, COMMIT 해도 ROLLBACK 된다
Oracle·MySQL그 문장만 취소(문장 수준 롤백). 트랜잭션은 살아 있어 다음 문장이 실행된다

그래서 Oracle·MySQL 에서 위 스크립트를 오류 무시하고 끝까지 돌리면 두 번째 UPDATE 가 성공해 A 는 700 그대로, B 만 2800 이 되어 COMMIT 될 수 있다. 원자성은 "오류 시 응용이 ROLLBACK 을 호출한다"는 전제 위에서 지켜진다.

6.3 SAVEPOINT — 단계별 추적

CREATE TABLE 로그 (n INT);

BEGIN;
INSERT INTO 로그 VALUES (5);      -- ①
SAVEPOINT s1;                     -- ②
INSERT INTO 로그 VALUES (6);      -- ③
ROLLBACK TO s1;                   -- ④
INSERT INTO 로그 VALUES (7);      -- ⑤
ROLLBACK TO s1;                   -- ⑥
INSERT INTO 로그 VALUES (8);      -- ⑦
COMMIT;                           -- ⑧
SELECT * FROM 로그;
단계문장트랜잭션 안에서 보이는 로그살아 있는 저장점
①INSERT 55—
②SAVEPOINT s15s1
③INSERT 65, 6s1
④ROLLBACK TO s15s1 (저장점은 남는다)
⑤INSERT 75, 7s1
⑥ROLLBACK TO s15s1
⑦INSERT 85, 8s1
⑧COMMIT5, 8 확정— (트랜잭션 종료로 모두 사라짐)

실행 결과 5, 8. 핵심 규칙 세 가지:

  1. ROLLBACK TO s1 은 s1 이후만 취소한다. s1 이전 작업(5)은 남고, 트랜잭션도 끝나지 않는다.
  2. 같은 저장점으로 여러 번 되돌아갈 수 있다(④·⑥).
  3. 그냥 ROLLBACK 은 저장점과 관계없이 트랜잭션 시작 시점까지 전부 취소한다.

저장점을 지나쳐 되돌리면 그 뒤 저장점은 사라진다.

BEGIN;
INSERT INTO 로그 VALUES (1);
SAVEPOINT s1;
INSERT INTO 로그 VALUES (2);
SAVEPOINT s2;
INSERT INTO 로그 VALUES (3);
ROLLBACK TO s1;                   -- 2, 3 취소 + s2 도 소멸 → 남은 것: 1
INSERT INTO 로그 VALUES (4);      -- 1, 4
ROLLBACK TO s2;                   -- ERROR: savepoint "s2" does not exist
COMMIT;
엔진마지막 COMMIT 후 로그
PostgreSQL아무것도 없음 — s2 오류로 트랜잭션이 aborted, COMMIT 이 ROLLBACK 이 됨 (실행 확인)
Oracle·MySQL1, 4 — 오류 난 ROLLBACK TO s2 만 무시되고 나머지 커밋

시험 문제는 보통 오류 없는 흐름을 준다. 저장점 이름이 같은 것을 다시 만들면(같은 이름 SAVEPOINT) Oracle 은 앞의 것을 지우고 새 위치로 옮긴다.

6.4 자동 커밋 (Auto Commit)

명시적으로 BEGIN 하지 않으면 문장 하나하나가 각자의 트랜잭션이 되어 실행 직후 자동으로 커밋되는 방식.

환경기본값
PostgreSQL(psql)·MySQL·SQL Server·JDBC자동 커밋 ON — BEGIN / START TRANSACTION 으로 끄고 묶는다
Oracle (SQL*Plus·SQL Developer)자동 커밋 OFF — 첫 DML 부터 트랜잭션이 열리고 COMMIT 을 직접 해야 한다 (SET AUTOCOMMIT ON 으로 변경)

자동 커밋 ON 에서 INSERT INTO 로그 VALUES (11); 은 바로 영구 반영되므로 뒤이은 ROLLBACK 이 되돌릴 것이 없다. 반대로 psql 에서 \set AUTOCOMMIT off 후 INSERT 하고 COMMIT 없이 접속을 끊으면 그 INSERT 는 사라진다(실행 확인: 12 는 남지 않았다). Oracle 도 COMMIT 없이 비정상 종료하면 롤백된다(SQL*Plus 의 정상 EXIT 은 기본적으로 커밋).

6.5 DDL 과 트랜잭션 — 묵시적 커밋 vs 트랜잭션 DDL

Oracle·MySQL: CREATE·ALTER·DROP·TRUNCATE 같은 DDL 은 실행 직전과 직후에 묵시적(자동) COMMIT 을 한다.

-- Oracle / MySQL
INSERT INTO 로그 VALUES (10);
CREATE TABLE 임시 (x INT);     -- ← 여기서 앞의 INSERT 까지 커밋된다
ROLLBACK;                      -- 되돌릴 것이 없다: 10 도 남고 임시 테이블도 남는다

PostgreSQL: DDL 도 트랜잭션 안에서 실행되고 롤백된다(SQL Server·SQLite 도 대부분 같다).

BEGIN;
INSERT INTO 로그 VALUES (10);
CREATE TABLE 임시 (x INT);
ALTER TABLE 로그 ADD COLUMN memo TEXT;
DROP TABLE 계좌;
ROLLBACK;

SELECT * FROM 로그;                                       -- 5, 8  (10 없음, memo 열도 없음)
SELECT to_regclass('임시') AS 임시, to_regclass('계좌') AS 계좌;
임시계좌
NULL계좌

INSERT·CREATE·ALTER·DROP 네 개가 전부 취소되어 임시 테이블은 없고, 지웠던 계좌 테이블은 살아 있다. 마이그레이션 스크립트를 BEGIN … COMMIT 으로 감싸 실패 시 통째로 되돌릴 수 있는 것이 PG 의 장점이다.

OracleMySQLPostgreSQL
DDL 실행 시앞뒤 묵시적 COMMIT앞뒤 묵시적 COMMIT트랜잭션의 일부
BEGIN; INSERT; CREATE TABLE; ROLLBACK;INSERT·테이블 모두 남음모두 남음모두 취소
TRUNCATE 롤백불가불가가능

7. 동시성 제어 — 여러 트랜잭션이 같은 데이터를 만질 때

트랜잭션을 하나씩 순서대로(직렬로) 실행하면 문제가 없지만 느리다. 그래서 DBMS 는 여러 트랜잭션을 겹쳐서(병행) 실행한다. 겹치는 순간 서로의 중간 결과를 보거나 덮어쓰면서 아래 네 가지 이상 현상이 생긴다. "병행 실행해도 어떤 직렬 실행과 결과가 같다"를 직렬 가능성(Serializability) 이라 하고, 동시성 제어의 목표가 이것이다.

이 절의 타임라인은 PostgreSQL 14 에서 연결 두 개(T1, T2)를 열고 단계를 번갈아 실행해 재현한 결과다. 예제 테이블: 계좌(id, bal) = (1, 100).

7.1 갱신 손실 (Lost Update)

두 트랜잭션이 같은 값을 읽고 → 계산해서 → 덮어쓰면, 먼저 쓴 쪽의 갱신이 사라진다.

순서T1 (30 입금)T2 (50 입금)계좌.bal
①SELECT bal → 100100
②SELECT bal → 100100
③UPDATE … SET bal = 130; COMMIT130
④UPDATE … SET bal = 150; COMMIT150

정답은 180 인데 150 — T1 의 +30 이 잃어버려졌다. 실행 결과도 READ COMMITTED 에서 150 이었다.

막는 방법:

방법동작실행 결과
상대 갱신 SET bal = bal + 30읽기·쓰기를 한 문장에서 해 DBMS 가 행 락으로 직렬화180
SELECT … FOR UPDATE읽는 순간 행에 배타 락 → T2 는 T1 이 끝날 때까지 대기180
REPEATABLE READ 이상PG 는 T2 의 UPDATE 를 거부T2: could not serialize access due to concurrent update → 130 (T2 는 재시도해야 함)

7.2 오손 읽기 (Dirty Read)

아직 커밋되지 않은 다른 트랜잭션의 변경을 읽는 것. 그 트랜잭션이 ROLLBACK 하면 존재한 적 없는 값을 읽은 셈이 된다.

순서T1T2
①UPDATE 계좌 SET bal = 999 (커밋 안 함)
②SELECT bal → 999 ?
③ROLLBACK

READ UNCOMMITTED 에서만 생길 수 있다. PostgreSQL 은 READ UNCOMMITTED 를 지정해도 READ COMMITTED 로 동작한다 — 실행해 보면 SHOW transaction_isolation 은 read uncommitted 인데 ② 의 결과는 100(커밋된 값)이었다. MVCC 구조상 커밋 안 된 버전을 남에게 보여 주지 않기 때문이다. MySQL(InnoDB)·SQL Server 의 READ UNCOMMITTED 에서는 999 가 보인다.

7.3 반복 불가능 읽기 (Non-repeatable Read)

한 트랜잭션 안에서 같은 행을 두 번 읽었는데 값이 다르다 — 그 사이 다른 트랜잭션이 그 행을 수정(UPDATE)하고 커밋했기 때문.

순서T1T2 (자동 커밋)
①BEGIN; SELECT bal → 100
②UPDATE 계좌 SET bal = bal + 50 (커밋)
③SELECT bal → ?
T1 의 격리 수준①③ (실행 결과)
READ COMMITTED100150 — 문장마다 최신 커밋을 본다
REPEATABLE READ100100 — 트랜잭션 시작 시점의 스냅샷을 계속 본다

7.4 유령 읽기 (Phantom Read)

같은 조건으로 두 번 조회했는데 행의 개수가 다르다 — 그 사이 다른 트랜잭션이 조건에 맞는 행을 삽입(INSERT)·삭제하고 커밋했기 때문. 반복 불가능 읽기가 "있던 행의 값"이 바뀌는 것이라면, 유령은 "없던 행이 나타나는" 것이다.

주문(id, amt) = (1, 100), (2, 200) 에서 T1 이 SELECT COUNT(*) FROM 주문 WHERE amt >= 100 을 두 번, 그 사이 T2 가 (3, 300) 삽입·커밋:

T1 의 격리 수준1회차2회차 (실행 결과)
READ COMMITTED23 — 유령 발생
REPEATABLE READ (PG)22

표준은 REPEATABLE READ 에서 유령을 허용한다. PostgreSQL 의 REPEATABLE READ 는 스냅샷 격리라서 유령까지 막는다 (MySQL InnoDB 도 일반 SELECT 에서는 막는다). 시험은 표준 표로 답한다.

7.5 격리 수준 4단계 (ANSI/ISO 표준)

격리 수준Dirty ReadNon-repeatable ReadPhantom Read
READ UNCOMMITTED (레벨 0)발생발생발생
READ COMMITTED (레벨 1)방지발생발생
REPEATABLE READ (레벨 2)방지방지발생
SERIALIZABLE (레벨 3)방지방지방지
  • 아래로 갈수록 일관성↑, 병행성(동시 처리량)↓.
  • 기본값: Oracle·PostgreSQL·SQL Server = READ COMMITTED, MySQL InnoDB = REPEATABLE READ.
  • 지정: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; (PG 는 BEGIN ISOLATION LEVEL … 도 가능)
  • 갱신 손실은 표준 표에 없지만 모든 수준에서 막아야 할 기본 문제로 본다(§7.1 방법들).

외우기 — 표의 "방지"는 오른쪽 아래로 계단. 이름이 곧 답이다: READ COMMITTED = "커밋된 것만 읽는다"(Dirty 방지), REPEATABLE READ = "반복해 읽어도 같다"(Non-repeatable 방지), SERIALIZABLE = "직렬 실행과 같다"(전부 방지).


8. 락(Lock)과 2단계 로킹

8.1 공유 락과 배타 락

이미 공유 락(S)이미 배타 락(X)
공유 락(S) 요청허용 (여럿이 동시에 읽기)대기
배타 락(X) 요청대기대기
  • 공유 락(Shared, 읽기 락): 읽기용. 다른 트랜잭션도 공유 락은 걸 수 있지만 배타 락은 못 건다.
  • 배타 락(Exclusive, 쓰기 락): 쓰기용. 걸려 있는 동안 다른 트랜잭션은 어떤 락도 못 건다.
  • UPDATE·DELETE 는 행에 배타 락을 건다. SELECT … FOR UPDATE 는 읽으면서 배타 락을 건다(§7.1).
  • PostgreSQL·Oracle 은 MVCC(다중 버전)라서 일반 SELECT 는 락을 걸지 않고 스냅샷을 읽는다 — 그래서 읽기와 쓰기가 서로를 막지 않는다.

8.2 로킹 단위 (Granularity)

로킹 단위락 개수관리 오버헤드병행성
큼 (데이터베이스·테이블)적음작음낮음
작음 (페이지·행·필드)많음큼높음

시험 문장: "로킹 단위가 작을수록 병행성 수준은 높아지고 관리는 복잡해진다." 반대로도 묻는다.

8.3 2단계 로킹 규약 (2PL, Two-Phase Locking)

트랜잭션의 락 연산을 두 단계로 나눈다.

단계할 수 있는 것할 수 없는 것
확장 단계 (Growing)락 획득(lock)해제(unlock)
축소 단계 (Shrinking)락 해제(unlock)새 락 획득
  • 한 번이라도 unlock 하면 그 뒤로는 lock 을 걸 수 없다.
  • 2PL 을 지키면 직렬 가능성이 보장된다. 그러나 교착상태는 막지 못한다.
  • 엄격한 2PL(Strict 2PL): 배타 락을 커밋·철회 때까지 쥐고 있는다 → 연쇄 복귀(cascading rollback) 방지. 실제 DBMS 가 쓰는 방식.

8.4 교착상태 (Deadlock)

T1 이 A 를 잠그고 B 를 기다리는데 T2 는 B 를 잠그고 A 를 기다리면, 둘 다 영원히 기다린다.

순서T1T2
①UPDATE 계좌 … id = 1 (1 에 X 락)
②UPDATE 계좌 … id = 2 (2 에 X 락)
③UPDATE 계좌 … id = 2 → T2 대기
④UPDATE 계좌 … id = 1 → T1 대기 → 교착

DBMS 는 대기 그래프에서 순환을 탐지해 한쪽을 강제 철회한다(PG: ERROR: deadlock detected). 예방책은 모든 트랜잭션이 같은 순서로 자원을 잠그기(항상 id 오름차순), 트랜잭션을 짧게 유지하기, 락 타임아웃. 교착상태 4조건(상호 배제·점유와 대기·비선점·환형 대기)은 운영체제와 같은 개념이다.


9. DCL — GRANT · REVOKE · ROLE

9.1 문법 골격

GRANT 권한 [, 권한 …] ON 객체 TO 사용자 | 역할 | PUBLIC [WITH GRANT OPTION];

REVOKE [GRANT OPTION FOR] 권한 [, 권한 …] ON 객체 FROM 사용자 | 역할 | PUBLIC [CASCADE | RESTRICT];
키워드뜻
TO / FROMGRANT 는 TO, REVOKE 는 FROM — 빈칸 문제 단골
WITH GRANT OPTION받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한까지 준다
GRANT OPTION FOR권한 자체는 두고 재부여 권한만 회수한다
CASCADE이 사용자가 남에게 다시 준 권한까지 연쇄 회수
RESTRICT연쇄로 회수될 권한이 있으면 회수를 거부(오류). 표준·PG 의 기본값
PUBLIC모든 사용자

9.2 권한 종류 (객체 권한)

권한대상비고
SELECT테이블·뷰열 단위 가능 SELECT (ename, sal)
INSERT테이블·뷰열 단위 가능
UPDATE테이블·뷰열 단위 UPDATE (sal) 이 자주 나온다
DELETE테이블·뷰행 단위만 (열 단위 없음)
REFERENCES테이블그 테이블을 참조하는 FK 를 만들 권한
ALL PRIVILEGES테이블위 전부 (Oracle 은 ALL)

열 단위 권한 실행 확인 — GRANT SELECT, UPDATE (sal) ON 사원 TO 박; 후 박으로 접속(SET ROLE 박):

UPDATE 사원 SET sal = sal + 10 WHERE eno = 3;      -- UPDATE 1 (성공)
UPDATE 사원 SET job = '대리' WHERE eno = 3;        -- ERROR: permission denied for table 사원
enojobsal
3사원290

sal 만 바뀌고 job 은 그대로다.

9.3 WITH GRANT OPTION 과 연쇄 회수 — 흐름 추적

PostgreSQL 에서 롤 김·이·박을 만들고 그대로 실행했다(테이블 소유자 = DBA).

-- ① DBA
GRANT SELECT ON 사원 TO 김 WITH GRANT OPTION;
-- ② 김
GRANT SELECT ON 사원 TO 이 WITH GRANT OPTION;
-- ③ 이
GRANT SELECT ON 사원 TO 박;

권한 그래프: DBA → 김(재부여 가능) → 이(재부여 가능) → 박

사용자SELECT재부여 가능
김OO
이OO
박OX

(a) RESTRICT 로 회수 시도

REVOKE SELECT ON 사원 FROM 김;            -- RESTRICT 가 기본
REVOKE SELECT ON 사원 FROM 김 RESTRICT;

둘 다 ERROR: dependent privileges exist (HINT: Use CASCADE to revoke them too). 김이 이에게, 이가 박에게 준 권한이 김의 권한에 기대고 있으므로 거부된다. 아무 권한도 바뀌지 않는다.

(b) 재부여 권한만 연쇄 회수

REVOKE GRANT OPTION FOR SELECT ON 사원 FROM 김 CASCADE;
사용자SELECT재부여 가능
김O (권한 자체는 남음)X
이XX
박XX

김이 "남에게 줄 자격"을 잃었으므로, 그 자격으로 내려간 이·박의 권한이 함께 사라진다.

(c) 권한 자체를 연쇄 회수 (①~③ 을 다시 실행한 뒤)

REVOKE SELECT ON 사원 FROM 김 CASCADE;
사용자SELECT
김X
이X
박X

독립 경로는 살아남는다 — 만약 DBA 가 따로 GRANT SELECT ON 사원 TO 박; 도 했다면, (c) 뒤에도 박은 SELECT 할 수 있다. 박의 권한 하나가 김 경로가 아닌 DBA 직접 경로에서 왔기 때문이다.

재부여 권한 없이 GRANT 하면? 박(재부여 X)이 GRANT SELECT ON 사원 TO 이; 를 실행하면 PG 는 WARNING: no privileges were granted 를 내고 아무것도 주지 않는다(Oracle 은 ORA-01031 오류).

9.4 역할(ROLE)과 PUBLIC

역할 = 권한의 묶음. 사람마다 권한을 일일이 주는 대신 역할에 권한을 주고, 사람에게 역할을 준다.

CREATE ROLE 인사팀;
GRANT SELECT ON 부서 TO 인사팀;   -- 역할에 권한
GRANT 인사팀 TO 이;              -- 사람에게 역할 (ON 이 없다)
-- 이: SELECT COUNT(*) FROM 부서;  → 4
REVOKE 인사팀 FROM 이;
-- 이: SELECT COUNT(*) FROM 부서;  → ERROR: permission denied for table 부서
  • 역할에 권한을 추가·회수하면 그 역할을 가진 모든 사용자에게 즉시 반영된다.
  • GRANT SELECT ON 부서 TO PUBLIC; → 모든 사용자가 조회 가능(실행 확인: 역할 없는 이도 4).
  • Oracle 의 기본 역할: CONNECT(접속), RESOURCE(객체 생성), DBA(전부).

9.5 시스템 권한 (Oracle, 소개)

객체가 아니라 행위에 대한 권한: CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE USER 등.

GRANT CREATE TABLE TO 김 WITH ADMIN OPTION;   -- 시스템 권한의 재부여는 WITH ADMIN OPTION
객체 권한시스템 권한
예SELECT·UPDATE ON 사원CREATE TABLE, CREATE SESSION
재부여 옵션WITH GRANT OPTIONWITH ADMIN OPTION
회수 시 연쇄연쇄 회수됨(CASCADE)연쇄 회수 안 됨 — 받은 사람이 준 권한은 남는다

확인 문제

1. 다음 SQL 을 실행한 뒤 q1 에 남는 값을 쓰시오.

BEGIN;
INSERT INTO q1 VALUES (1);
SAVEPOINT a;
INSERT INTO q1 VALUES (2);
SAVEPOINT b;
INSERT INTO q1 VALUES (3);
ROLLBACK TO b;
INSERT INTO q1 VALUES (4);
ROLLBACK TO a;
INSERT INTO q1 VALUES (5);
COMMIT;

2. q3(id, v) = (1, 10), (2, 20), (3, 30) 일 때 실행 후 id 순 v 값은?

BEGIN;
UPDATE q3 SET v = v * 2;
SAVEPOINT s;
DELETE FROM q3 WHERE v > 30;
ROLLBACK TO s;
UPDATE q3 SET v = v + 1 WHERE id = 3;
COMMIT;

3. 표준 격리 수준 중 Non-repeatable Read 는 막지만 Phantom Read 는 허용하는 수준은?

4. T1·T2 가 잔액 100 을 각각 읽은 뒤 T1 은 SET bal = 130, T2 는 SET bal = 150 으로 덮어쓰고 차례로 커밋했다. 이 현상의 이름과 최종 잔액은?

5. (O/X) ① PostgreSQL 은 READ UNCOMMITTED 에서 Dirty Read 가 발생한다. ② 2단계 로킹 규약은 교착상태를 예방한다. ③ 로킹 단위가 작을수록 병행성이 높다. ④ Oracle 에서 CREATE TABLE 직후 ROLLBACK 하면 앞서 실행한 INSERT 가 취소된다.

6. 빈칸을 채우시오. 사원 테이블의 sal 열만 수정할 수 있는 권한을 박에게 주고, 박이 그 권한을 남에게 다시 줄 수 있게 한다.

GRANT ( ㉠ ) ON 사원 TO 박 ( ㉡ );

7. DBA → 김(WITH GRANT OPTION) → 이 → 박 순으로 SELECT 를 부여했다(이도 WITH GRANT OPTION). DBA 가 REVOKE SELECT ON 사원 FROM 김 RESTRICT; 를 실행하면 결과는? 이어서 CASCADE 로 실행하면 SELECT 가능한 사람은?

8. UPDATE t SET a = b, b = a; (실행 전 a=1, b=2) 의 결과를 PostgreSQL 과 MySQL 로 각각 쓰시오.

정답과 풀이

번호정답풀이
11, 5ROLLBACK TO b 로 3 취소 → 4 삽입 → ROLLBACK TO a 로 2·4 취소(b 도 소멸) → 5 삽입. PG 실행 확인
220, 40, 612배 → 20, 40, 60. DELETE(60 삭제)는 ROLLBACK TO s 로 취소, 2배는 s 이전이라 유지. id 3 에 +1 → 61. PG 실행 확인
3REPEATABLE READ표준 표의 레벨 2. 유령까지 막으려면 SERIALIZABLE
4갱신 손실(Lost Update), 150T1 의 +30 이 덮어써져 사라졌다. 상대 갱신(bal = bal + 30)이나 FOR UPDATE 로 막으면 180
5① X ② X ③ O ④ X① PG 는 READ COMMITTED 처럼 동작. ② 2PL 은 직렬성은 보장하지만 교착상태는 못 막는다. ④ DDL 직전 묵시적 COMMIT 으로 INSERT 가 이미 확정
6㉠ UPDATE (sal) ㉡ WITH GRANT OPTION열 단위 권한은 권한 뒤 괄호. 재부여는 WITH GRANT OPTION (시스템 권한이면 WITH ADMIN OPTION)
7RESTRICT: 오류로 거부(변화 없음) / CASCADE: 아무도 없음이·박의 권한이 김에게 의존하므로 RESTRICT 는 dependent privileges exist. CASCADE 는 김·이·박 연쇄 회수
8PG a=2, b=1 / MySQL a=2, b=2표준은 SET 의 오른쪽을 모두 갱신 전 값으로 계산(스왑). MySQL 은 왼쪽부터 차례로 반영해 b 가 새 a(2)를 받는다

한 줄 요약

주제한 줄
INSERT열 목록 생략 = 모든 열을 정의 순서대로. 생략한 열은 DEFAULT, 없으면 NULL
UPDATESET 오른쪽은 전부 갱신 전 값 기준(스왑 가능, MySQL 예외). WHERE 생략 = 전 행
서브쿼리 SET매칭 없는 행은 NULL 이 된다 → WHERE EXISTS 로 대상 제한
DELETE vs TRUNCATEDELETE = DML·롤백 가능·WHERE 가능 / TRUNCATE = DDL·전체·(Oracle·MySQL) 롤백 불가
MERGE / UPSERT있으면 UPDATE 없으면 INSERT. PG ON CONFLICT, MySQL ON DUPLICATE KEY UPDATE
ACID원자성·영속성 = 회복, 독립성 = 동시성 제어, 일관성 = 무결성
부분 완료연산은 끝났고 COMMIT 만 남은 상태
SAVEPOINTROLLBACK TO 는 그 지점 이후만 취소, 트랜잭션은 계속. 뒤 저장점은 소멸
DDL 과 커밋Oracle·MySQL = DDL 앞뒤 묵시적 COMMIT / PG = DDL 도 롤백
이상 현상갱신 손실(덮어쓰기)·Dirty(미커밋 읽기)·Non-repeatable(값 변경)·Phantom(행 증감)
격리 수준RU < RC(Dirty 방지) < RR(+Non-repeatable) < SERIALIZABLE(+Phantom). 위로 갈수록 병행성↑
락S-S 만 호환. 로킹 단위 작을수록 병행성↑·오버헤드↑
2PL확장(획득만) → 축소(해제만). 직렬성 보장, 교착상태는 못 막음
GRANT / REVOKEGRANT … TO, REVOKE … FROM. 재부여 = WITH GRANT OPTION
연쇄 회수CASCADE = 다시 준 권한까지, RESTRICT = 의존 권한 있으면 거부. 독립 경로 권한은 남는다
ROLE권한 묶음. GRANT 역할 TO 사용자 (ON 없음)
← SQL ⑦ DDL·제약·뷰·인덱스SQL ⑨ 관계대수·정규화 →