SQL 완전 정리 ⑧ DML·트랜잭션·권한
앞의 편들이 "데이터를 어떻게 읽는가(SELECT)"였다면, 이 편은 데이터를 바꾸는 쪽이다. DML(INSERT·UPDATE·DELETE·MERGE)로 행을 바꾸고, TCL(COMMIT·ROLLBACK·SAVEPOINT)로 그 변경을 한 덩어리(트랜잭션) 로 묶어 확정하거나 되돌리며, 여러 사용자가 동시에 바꿀 때 생기는 이상 현상을 격리 수준과 락으로 막는다. 마지막으로 DCL(GRANT·REVOKE)로 "누가 무엇을 바꿀 수 있는가"를 정한다. 본문의 쿼리·결과 표는 PostgreSQL 14 에서 실제로 실행해 맞췄다(두 세션이 엇갈리는 동시성 예제도 세션 두 개를 띄워 단계별로 재현했다). MERGE·Oracle 시스템 권한처럼 PG14 에서 못 돌리는 문법은 소개만 한다.
0. 이 편에서 쓰는 테이블
부서
| dno | dname | loc |
|---|---|---|
| 10 | 개발 | 서울 |
| 20 | 영업 | 부산 |
| 30 | 인사 | 서울 |
| 40 | 기획 | NULL |
사원
| eno | ename | dno | job | sal | mgr | hiredate | bonus |
|---|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | NULL | 2019-03-01 | NULL |
| 2 | 이도윤 | 10 | 대리 | 350 | 1 | 2021-07-15 | 30 |
| 3 | 박서준 | 10 | 사원 | 280 | 2 | 2023-01-02 | NULL |
| 4 | 최지우 | 20 | 팀장 | 450 | NULL | 2018-11-20 | 50 |
| 5 | 정민재 | 20 | 사원 | 300 | 4 | 2022-05-09 | 20 |
| 6 | 한유나 | 20 | 사원 | 300 | 4 | 2024-02-01 | NULL |
| 7 | 오세린 | NULL | 사원 | 250 | NULL | 2025-01-06 | NULL |
참여
| eno | pno | hours |
|---|---|---|
| 1 | 100 | 20 |
| 2 | 100 | 40 |
| 3 | 100 | 30 |
| 2 | 200 | 10 |
| 5 | 200 | 25 |
이 편에서는 데이터를 실제로 바꾸므로, 각 예제는 위 원본 상태에서 출발한다고 가정한다(예제끼리 효과가 누적되지 않는다). 필요할 때마다 작은 테이블(신입, 계좌 등)을 추가한다.
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;
| eno | ename | dno | sal | hiredate |
|---|---|---|---|---|
| 11 | 강하준 | 10 | 220 | 2026-03-02 |
| 12 | 윤서아 | NULL | 200 | 2026-03-02 |
| 13 | 임도현 | 20 | 200 | 2026-03-02 |
| 14 | 송지안 | NULL | 200 | 2026-03-02 |
| 15 | 배준서 | NULL | NULL | 2026-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;
| eno | ename | dno | sal | hiredate |
|---|---|---|---|---|
| 101 | 김하늘 | 10 | 500 | 2026-03-02 |
| 104 | 최지우 | 20 | 450 | 2026-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;
| eno | ename | sal |
|---|---|---|
| 1 | 김하늘 | 550 |
| 2 | 이도윤 | 385 |
| 3 | 박서준 | 308 |
sal = sal * 1.1 은 "오른쪽(현재 값)을 계산해 왼쪽 열에 넣는다"는 대입이지 방정식이 아니다.
sal 이 INT 이므로 결과는 정수로 반올림되어 저장된다.
NULL 이 섞이면 산술 결과도 NULL 이다.
UPDATE 사원 SET bonus = bonus + 10 WHERE dno = 20; -- UPDATE 3
| eno | ename | bonus 전 | bonus 후 |
|---|---|---|---|
| 4 | 최지우 | 50 | 60 |
| 5 | 정민재 | 20 | 30 |
| 6 | 한유나 | NULL | NULL |
한유나도 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;
| a | b |
|---|---|
| 2 | 1 |
표준 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·SQLite a=2, b=1 (스왑) MySQL a=2, b=2
2.4 서브쿼리로 SET — 매칭이 없으면 NULL 이 된다
급여 조정표가 따로 있다고 하자.
조정
| eno | new_sal |
|---|---|
| 3 | 300 |
| 6 | 330 |
박서준·한유나 두 사람만 바꾸려고 이렇게 쓰면:
UPDATE 사원
SET sal = (SELECT new_sal FROM 조정 WHERE 조정.eno = 사원.eno); -- UPDATE 7 (!)
| eno | ename | sal |
|---|---|---|
| 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
| eno | pno | hours |
|---|---|---|
| 5 | 200 | 25 |
-- 어떤 프로젝트에도 참여하지 않은 사원 삭제 (상관 서브쿼리)
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 = '사원'); |
| PostgreSQL | DELETE FROM 참여 c USING 사원 e WHERE e.eno = c.eno AND e.job = '사원'; |
| MySQL | DELETE c FROM 참여 c JOIN 사원 e ON e.eno = c.eno WHERE e.job = '사원'; |
| SQL Server | DELETE 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
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| 분류 | DML | DDL | DDL |
| 지우는 것 | 조건에 맞는 행(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번 포함)
| eno | new_sal |
|---|---|
| 3 | 300 |
| 6 | 330 |
| 8 | 230 |
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) | UPDATE | 3 → 300 |
| (6, 330) | 있음 (300) | UPDATE | 6 → 330 |
| (8, 230) | 없음 | INSERT | 8, 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;
| eno | sal |
|---|---|
| 1 | 500 |
| 2 | 350 |
| 3 | 300 |
| 4 | 450 |
| 5 | 300 |
| 6 | 330 |
| 7 | 250 |
| 8 | 230 |
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+ | PostgreSQL | MySQL |
|---|---|---|---|
| 있으면 갱신·없으면 삽입 | MERGE … WHEN MATCHED / NOT MATCHED | ON CONFLICT (키) DO UPDATE | ON DUPLICATE KEY UPDATE |
| 있으면 무시 | MERGE 에 NOT MATCHED 만 | ON CONFLICT DO NOTHING | INSERT IGNORE |
| 원천 | 테이블·서브쿼리 | VALUES·SELECT | VALUES·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 5 | 5 | — |
| ② | SAVEPOINT s1 | 5 | s1 |
| ③ | INSERT 6 | 5, 6 | s1 |
| ④ | ROLLBACK TO s1 | 5 | s1 (저장점은 남는다) |
| ⑤ | INSERT 7 | 5, 7 | s1 |
| ⑥ | ROLLBACK TO s1 | 5 | s1 |
| ⑦ | INSERT 8 | 5, 8 | s1 |
| ⑧ | COMMIT | 5, 8 확정 | — (트랜잭션 종료로 모두 사라짐) |
실행 결과 5, 8. 핵심 규칙 세 가지:
ROLLBACK TO s1은 s1 이후만 취소한다. s1 이전 작업(5)은 남고, 트랜잭션도 끝나지 않는다.- 같은 저장점으로 여러 번 되돌아갈 수 있다(④·⑥).
- 그냥
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·MySQL | 1, 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 의 장점이다.
| Oracle | MySQL | PostgreSQL | |
|---|---|---|---|
| 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 → 100 | 100 | |
| ② | SELECT bal → 100 | 100 | |
| ③ | UPDATE … SET bal = 130; COMMIT | 130 | |
| ④ | UPDATE … SET bal = 150; COMMIT | 150 |
정답은 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 하면 존재한 적 없는 값을 읽은 셈이 된다.
| 순서 | T1 | T2 |
|---|---|---|
| ① | 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)하고 커밋했기 때문.
| 순서 | T1 | T2 (자동 커밋) |
|---|---|---|
| ① | BEGIN; SELECT bal → 100 | |
| ② | UPDATE 계좌 SET bal = bal + 50 (커밋) | |
| ③ | SELECT bal → ? |
| T1 의 격리 수준 | ① | ③ (실행 결과) |
|---|---|---|
| READ COMMITTED | 100 | 150 — 문장마다 최신 커밋을 본다 |
| REPEATABLE READ | 100 | 100 — 트랜잭션 시작 시점의 스냅샷을 계속 본다 |
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 COMMITTED | 2 | 3 — 유령 발생 |
| REPEATABLE READ (PG) | 2 | 2 |
표준은 REPEATABLE READ 에서 유령을 허용한다. PostgreSQL 의 REPEATABLE READ 는 스냅샷 격리라서 유령까지 막는다 (MySQL InnoDB 도 일반 SELECT 에서는 막는다). 시험은 표준 표로 답한다.
7.5 격리 수준 4단계 (ANSI/ISO 표준)
| 격리 수준 | Dirty Read | Non-repeatable Read | Phantom 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 를 기다리면, 둘 다 영원히 기다린다.
| 순서 | T1 | T2 |
|---|---|---|
| ① | 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 / FROM | GRANT 는 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 사원
| eno | job | sal |
|---|---|---|
| 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 | 재부여 가능 |
|---|---|---|
| 김 | O | O |
| 이 | O | O |
| 박 | O | X |
(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 |
| 이 | X | X |
| 박 | X | X |
김이 "남에게 줄 자격"을 잃었으므로, 그 자격으로 내려간 이·박의 권한이 함께 사라진다.
(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 OPTION | WITH 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 로 각각 쓰시오.
정답과 풀이
| 번호 | 정답 | 풀이 |
|---|---|---|
| 1 | 1, 5 | ROLLBACK TO b 로 3 취소 → 4 삽입 → ROLLBACK TO a 로 2·4 취소(b 도 소멸) → 5 삽입. PG 실행 확인 |
| 2 | 20, 40, 61 | 2배 → 20, 40, 60. DELETE(60 삭제)는 ROLLBACK TO s 로 취소, 2배는 s 이전이라 유지. id 3 에 +1 → 61. PG 실행 확인 |
| 3 | REPEATABLE READ | 표준 표의 레벨 2. 유령까지 막으려면 SERIALIZABLE |
| 4 | 갱신 손실(Lost Update), 150 | T1 의 +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) |
| 7 | RESTRICT: 오류로 거부(변화 없음) / CASCADE: 아무도 없음 | 이·박의 권한이 김에게 의존하므로 RESTRICT 는 dependent privileges exist. CASCADE 는 김·이·박 연쇄 회수 |
| 8 | PG a=2, b=1 / MySQL a=2, b=2 | 표준은 SET 의 오른쪽을 모두 갱신 전 값으로 계산(스왑). MySQL 은 왼쪽부터 차례로 반영해 b 가 새 a(2)를 받는다 |
한 줄 요약
| 주제 | 한 줄 |
|---|---|
| INSERT | 열 목록 생략 = 모든 열을 정의 순서대로. 생략한 열은 DEFAULT, 없으면 NULL |
| UPDATE | SET 오른쪽은 전부 갱신 전 값 기준(스왑 가능, MySQL 예외). WHERE 생략 = 전 행 |
| 서브쿼리 SET | 매칭 없는 행은 NULL 이 된다 → WHERE EXISTS 로 대상 제한 |
| DELETE vs TRUNCATE | DELETE = DML·롤백 가능·WHERE 가능 / TRUNCATE = DDL·전체·(Oracle·MySQL) 롤백 불가 |
| MERGE / UPSERT | 있으면 UPDATE 없으면 INSERT. PG ON CONFLICT, MySQL ON DUPLICATE KEY UPDATE |
| ACID | 원자성·영속성 = 회복, 독립성 = 동시성 제어, 일관성 = 무결성 |
| 부분 완료 | 연산은 끝났고 COMMIT 만 남은 상태 |
| SAVEPOINT | ROLLBACK TO 는 그 지점 이후만 취소, 트랜잭션은 계속. 뒤 저장점은 소멸 |
| DDL 과 커밋 | Oracle·MySQL = DDL 앞뒤 묵시적 COMMIT / PG = DDL 도 롤백 |
| 이상 현상 | 갱신 손실(덮어쓰기)·Dirty(미커밋 읽기)·Non-repeatable(값 변경)·Phantom(행 증감) |
| 격리 수준 | RU < RC(Dirty 방지) < RR(+Non-repeatable) < SERIALIZABLE(+Phantom). 위로 갈수록 병행성↑ |
| 락 | S-S 만 호환. 로킹 단위 작을수록 병행성↑·오버헤드↑ |
| 2PL | 확장(획득만) → 축소(해제만). 직렬성 보장, 교착상태는 못 막음 |
| GRANT / REVOKE | GRANT … TO, REVOKE … FROM. 재부여 = WITH GRANT OPTION |
| 연쇄 회수 | CASCADE = 다시 준 권한까지, RESTRICT = 의존 권한 있으면 거부. 독립 경로 권한은 남는다 |
| ROLE | 권한 묶음. GRANT 역할 TO 사용자 (ON 없음) |