NULL과 트랜잭션 - 3값 논리 COMMIT ROLLBACK SAVEPOINT 와 DDL DML DCL (SQLD 기본 6장)

이 장에서 배우는 것

5장까지 표의 모양을 정했다. 모델링의 마지막 판단은 두 가지다. 어떤 속성이 비어 있어도 되는가(NULL 허용), 어떤 변경들이 함께 성공하거나 함께 실패해야 하는가(트랜잭션 단위). 둘 다 SQL 결과를 예측할 때 가장 자주 틀리는 부분과 맞닿아 있다. 이 장은 1과목과 2과목을 잇는 다리다.

• NULL 이 산술·비교·논리·집계에서 어떻게 퍼지는지 3값 논리로 정리한다.
• 트랜잭션의 네 성질과 COMMIT·ROLLBACK·SAVEPOINT 의 범위를 이체 예제로 확인한다.
• 오류가 난 문장만 취소되고 트랜잭션은 살아 있는 동작, 그리고 DDL·DML·DCL·TCL 분류를 정리한다.

핵심 개념

포인트가 NULL 인 회원에게 "포인트 10점 추가" 이벤트를 돌렸더니 그 회원만 포인트가 여전히 비어 있었다는 문의가 들어온다. 쿼리는 point = point + 10 이었다. NULL 에 무엇을 더해도 NULL 이다. 모델을 만들 때 "포인트가 없음"을 NULL 로 둘지 0 으로 둘지 정하지 않은 대가다.

NULL 은 값이 아니라 "모름"이다

NULL 은 0 도 빈 문자열도 아니다. 값이 없거나 아직 모른다는 표시다. 그래서 NULL 과의 연산 결과도 "모름"이 된다.

• 산술: NULL + 1 은 NULL.
• 비교: NULL = NULL 도 참이 아니라 UNKNOWN. 그래서 NULL 검사는 IS NULL 로만 한다.
• 논리: 참·거짓·UNKNOWN 의 3값 논리. UNKNOWN AND 거짓 은 거짓, UNKNOWN OR 참 은 참, 나머지 조합은 대개 UNKNOWN.
• WHERE 는 참인 행만 남긴다. UNKNOWN 은 거짓처럼 버려진다.
• 집계 함수(COUNT(*) 제외)는 NULL 을 건너뛴다.

표 · 3값 논리 진리표 (06-truth-table 실행 결과: 1·0·NULL 을 SQLite 가 계산한 값)

p	q	p AND q	p OR q
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	UNKNOWN	UNKNOWN	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	UNKNOWN	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	TRUE
UNKNOWN	FALSE	FALSE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

모델링에서 NULL 허용 판단

주식별자는 존재성 때문에 NULL 불가다. 일반 속성은 "값이 없는 상태가 업무적으로 의미가 있는가"로 판단한다. 퇴사일은 재직 중이면 없는 것이 자연스러우므로 NULL 허용이 맞다. 반면 포인트처럼 "없음"과 "0" 이 같은 뜻이라면 NOT NULL DEFAULT 0 으로 두는 편이 SQL 을 단순하게 만든다.

트랜잭션

트랜잭션은 더 이상 나눌 수 없는 작업 단위다. 이체는 "보내는 계좌에서 빼기"와 "받는 계좌에 더하기"가 한 단위다.

성질	뜻
원자성(Atomicity)	전부 반영되거나 전혀 반영되지 않는다
일관성(Consistency)	트랜잭션 전후로 DB 가 규칙(제약)을 만족한다
고립성(Isolation)	실행 중인 트랜잭션의 중간 결과를 다른 트랜잭션이 보지 못한다
지속성(Durability)	커밋된 결과는 장애가 나도 남는다

SQL 명령은 네 무리로 나눈다. DDL(CREATE, ALTER, DROP, RENAME, TRUNCATE)은 구조를, DML(SELECT, INSERT, UPDATE, DELETE, MERGE)은 데이터를, DCL(GRANT, REVOKE)은 권한을, TCL(COMMIT, ROLLBACK, SAVEPOINT)은 트랜잭션을 다룬다. SELECT 를 DQL 로 따로 떼는 분류도 있다.

예제 스키마

잔액이 음수가 될 수 없는 계좌와, 별명·포인트가 비어 있을 수 있는 회원이다. 다솜의 별명은 NULL 이 아니라 빈 문자열('')이다. 표에서 빈 칸으로 보이는 것이 그것이다.

-- 6장: 계좌 이체와 NULL (가상 데이터)
CREATE TABLE account (
  acct_id INTEGER PRIMARY KEY,
  owner   TEXT NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  nickname  TEXT,
  point     INTEGER
);
INSERT INTO account VALUES (1, '가람', 1000), (2, '나무', 500);
INSERT INTO member VALUES (1, '가람', '람이', 100), (2, '나무', NULL, NULL), (3, '다솜', '', 0);

member_id	name	nickname	point
1	가람	람이	100
2	나무	NULL	NULL
3	다솜		0

SQL과 실행 결과

SQLite 는 참을 1, 거짓을 0 으로 표시하고, UNKNOWN 은 NULL 로 표시한다.

NULL 과의 연산

SELECT NULL + 1     AS plus,
       NULL = NULL  AS eq,
       NULL IS NULL AS is_null,
       NULL <> 1    AS ne,
       'a' || NULL  AS concat;

실행 결과:

plus  eq    is_null  ne    concat
----  ----  -------  ----  ------
NULL  NULL  1        NULL  NULL

NULL IS NULL 만 참(1)이고, 나머지는 모두 NULL 이다. 문자열 연결도 NULL 이 된다.

3값 논리와 IN

SELECT 1 IN (1, NULL)     AS in_hit,
       2 IN (1, NULL)     AS in_miss,
       2 NOT IN (1, NULL) AS not_in,
       (NULL AND 0)       AS null_and_false,
       (NULL OR 1)        AS null_or_true,
       (NULL AND 1)       AS null_and_true;

실행 결과:

in_hit  in_miss  not_in  null_and_false  null_or_true  null_and_true
------  -------  ------  --------------  ------------  -------------
1       NULL     NULL    0               1             NULL

2 IN (1, NULL) 은 2 = 1 OR 2 = NULL 이므로 거짓 OR UNKNOWN = UNKNOWN 이다. 2 NOT IN (1, NULL) 은 2 <> 1 AND 2 <> NULL 이므로 참 AND UNKNOWN = UNKNOWN 이다. NOT IN 목록에 NULL 이 하나라도 있으면 어떤 행도 참이 되지 못한다. 7장과 10장에서 이 규칙이 다시 나온다.

NULL 이 섞인 행과 집계

SELECT name, nickname,
       COALESCE(nickname, name) AS shown,
       point,
       point + 10 AS plus10
FROM member ORDER BY member_id;

실행 결과:

name  nickname  shown  point  plus10
----  --------  -----  -----  ------
가람  람이      람이   100    110
나무  NULL      나무   NULL   NULL
다솜                   0      10

SELECT COUNT(*) AS all_rows, COUNT(point) AS with_point,
       SUM(point) AS total, AVG(point) AS avg_point,
       ROUND(AVG(COALESCE(point, 0)), 2) AS avg_as_zero
FROM member;

실행 결과:

all_rows  with_point  total  avg_point  avg_as_zero
--------  ----------  -----  ---------  -----------
3         2           100    50.0       33.33

포인트가 있는 회원은 2명이고 합계는 100 이다. AVG(point) 는 100 ÷ 2 = 50 이고, NULL 을 0 으로 보고 셋으로 나누면 33.33 이다. 평균의 분모가 무엇인지는 업무가 정한다.

그림 · NULL 은 집계에서 빠진다 — member 세 행의 point 는 100, NULL, 0 이다. COUNT(*) 는 3, COUNT(point) 는 2, SUM 은 100 이고, AVG 는 NULL 을 분모에서도 빼서 50.0 이 된다. NULL 을 0 으로 보려면 COALESCE 를 먼저 씌운다(33.33).

ROLLBACK 과 SAVEPOINT

BEGIN;
UPDATE account SET balance = balance - 300 WHERE acct_id = 1;
SELECT acct_id, balance FROM account;
ROLLBACK;
SELECT acct_id, balance FROM account;

실행 결과:

acct_id  balance
-------  -------
1        700
2        500
acct_id  balance
-------  -------
1        1000
2        500

BEGIN;
UPDATE account SET balance = balance - 100 WHERE acct_id = 1;
SAVEPOINT sp1;
UPDATE account SET balance = balance - 400 WHERE acct_id = 1;
ROLLBACK TO sp1;
UPDATE account SET balance = balance + 100 WHERE acct_id = 2;
COMMIT;
SELECT acct_id, balance FROM account;

실행 결과:

acct_id  balance
-------  -------
1        900
2        600

ROLLBACK TO sp1 은 세이브포인트 이후의 변경(400 빼기)만 취소하고 트랜잭션은 계속된다. 커밋할 때 반영된 것은 100 빼기와 100 더하기다.

오류가 나도 트랜잭션은 끝나지 않는다

나무(잔액 500)가 가람에게 700 을 보낸다. 받는 쪽을 먼저 더하고, 보내는 쪽을 빼는 순서로 짰다. 이 예제만 -bail 없이 실행했다. 오류 뒤에도 다음 문장을 계속 실행하는 도구의 동작을 보기 위해서다.

-- 나무(2) → 가람(1) 으로 700 이체. 받는 쪽에 먼저 더하고, 보내는 쪽에서 뺀다
BEGIN;
UPDATE account SET balance = balance + 700 WHERE acct_id = 1;
UPDATE account SET balance = balance - 700 WHERE acct_id = 2;
COMMIT;
SELECT acct_id, balance, (SELECT SUM(balance) FROM account) AS total FROM account;

실행 결과:

Runtime error near line 4: CHECK constraint failed: balance >= 0 (19)
acct_id  balance  total
-------  -------  -----
1        1700     2200
2        500      2200

두 번째 UPDATE 가 CHECK 제약에 걸렸다. 취소된 것은 그 문장 하나뿐이다. 트랜잭션은 살아 있었고, 이어진 COMMIT 이 첫 UPDATE(가람 +700)를 확정했다. 총액이 1,500 에서 2,200 으로 늘었다. 원자성은 DB 가 저절로 지켜 주는 것이 아니라 오류를 받으면 애플리케이션이 ROLLBACK 해야 지켜진다. 대부분의 프레임워크가 예외 발생 시 자동 롤백하는 이유다.

그림 · SAVEPOINT 와 문장 실패가 트랜잭션에 남기는 것 — ROLLBACK TO sp1 은 sp1 뒤의 변경만 되돌리고 트랜잭션은 계속된다. 문장 하나가 제약을 어겨 실패해도 트랜잭션은 끝나지 않아, 앞선 +700 이 COMMIT 으로 확정되고 합계가 1500 에서 2200 으로 늘었다.

DDL 과 트랜잭션

BEGIN;
CREATE TABLE audit_log (msg TEXT);
INSERT INTO audit_log VALUES ('temp');
ROLLBACK;
SELECT COUNT(*) AS audit_log_tables FROM sqlite_schema WHERE name = 'audit_log';

실행 결과:

audit_log_tables
----------------
0

TRUNCATE TABLE member;

실행 결과:

Parse error near line 1: near "TRUNCATE": syntax error
  TRUNCATE TABLE member;
  ^--- error here

표준과 구현의 차이

이 절의 차이가 이 책에서 가장 시험에 자주 걸린다. 아래 Oracle·SQL Server 동작은 비교 설명이며 실행하지 않았다.

항목	SQLite(실행)	Oracle	SQL Server
빈 문자열 ''	NULL 이 아닌 값	NULL 로 취급	NULL 이 아닌 값
자동 커밋	BEGIN 없이 실행한 문장은 즉시 커밋	기본은 명시적 COMMIT 필요(도구 설정에 따라 다름)	기본 자동 커밋
DDL 과 트랜잭션	DDL 도 ROLLBACK 된다	DDL 실행 전후로 자동 커밋. 앞선 DML 까지 확정된다	DDL 도 명시적 트랜잭션 안에서 ROLLBACK 된다
TRUNCATE	없음. DELETE FROM 표 사용	DDL. 되돌릴 수 없다	트랜잭션 안에서는 ROLLBACK 가능
세이브포인트 문법	SAVEPOINT a / ROLLBACK TO a	SAVEPOINT a / ROLLBACK TO a	SAVE TRANSACTION a / ROLLBACK TRANSACTION a
GRANT·REVOKE	없음(파일 권한으로 관리)	있음	있음

[구현 차이] Oracle 에서 INSERT 후 CREATE TABLE 을 실행하고 ROLLBACK 하면, DDL 의 자동 커밋 때문에 앞선 INSERT 까지 이미 확정되어 되돌아가지 않는다. 위 SQLite 예제와 결과가 정반대다.

[구현 차이] Oracle 은 '' 를 NULL 로 보기 때문에, 위 회원 예제의 다솜도 nickname IS NULL 에 걸린다. 같은 SQL 이 제품에 따라 다른 행 수를 낸다.

시험에서 헷갈리는 지점

판단 1. "WHERE bonus = NULL 은 bonus 가 NULL 인 행을 찾는다"

틀렸다. 비교 결과가 언제나 UNKNOWN 이라 0행이 나온다. IS NULL 을 쓴다.

판단 2. "ROLLBACK TO 세이브포인트를 실행하면 트랜잭션이 끝난다"

틀렸다. 세이브포인트 이후만 취소하고 트랜잭션은 계속된다. 끝내려면 COMMIT 이나 ROLLBACK 을 따로 실행한다.

판단 3. "DELETE, TRUNCATE, DROP 은 모두 DML 이다"

틀렸다. DELETE 만 DML 이고, TRUNCATE 와 DROP 은 DDL 이다. DELETE 는 행을 하나씩 지우며 로그를 남겨 되돌릴 수 있고, TRUNCATE 는 저장 공간째 비우며(Oracle 기준) 되돌릴 수 없고, DROP 은 테이블 정의까지 지운다.

판단 4. "제약 조건 위반으로 문장이 실패하면 그 트랜잭션의 앞선 변경도 모두 자동 취소된다"

틀렸다(적어도 SQLite 의 기본 동작과 대부분 제품에서). 위 이체 예제처럼 실패한 문장만 취소된다. 전체 취소는 ROLLBACK 을 실행해야 일어난다.

연습 문제

1. (NULL = 1) OR (1 = 1), (NULL = 1) AND (1 = 1), NOT (NULL = 1) 의 결과를 참·거짓·UNKNOWN 으로 쓰라.
2. 예제 회원 테이블에서 COUNT(nickname), COUNT(*), 별명이 NULL 인 행 수, 별명이 빈 문자열인 행 수를 한 번에 구하라. SQLite 에서의 결과를 예측하고, Oracle 이라면 어떻게 달라지는지 쓰라.
3. 다음을 DDL·DML·DCL·TCL 로 나누라. (가) TRUNCATE (나) MERGE (다) REVOKE (라) SAVEPOINT (마) ALTER
4. 잔액 1,000 인 계좌 1에 대해 다음을 실행한 뒤 잔액을 예측하라. BEGIN; 200 빼기; SAVEPOINT a; 200 빼기; SAVEPOINT b; 200 빼기; ROLLBACK TO a; COMMIT;
5. 위 이체 예제에서 돈이 생겨나지 않게 하려면 무엇을 바꿔야 하는가? 두 가지 방법을 쓰라.

정답과 해설

1. 참, UNKNOWN, UNKNOWN. UNKNOWN OR 참은 참이고, UNKNOWN AND 참은 UNKNOWN 이며, UNKNOWN 의 부정도 UNKNOWN 이다.

2. SQLite 에서는 2, 3, 1, 1 이다. 다솜의 '' 는 값이므로 COUNT(nickname) 에 포함된다.

SELECT COUNT(nickname) AS a, COUNT(*) AS b,
       SUM(CASE WHEN nickname IS NULL THEN 1 ELSE 0 END) AS c,
       SUM(CASE WHEN nickname = '' THEN 1 ELSE 0 END) AS d
FROM member;

실행 결과:

a  b  c  d
-  -  -  -
2  3  1  1

Oracle 이라면 '' 가 NULL 로 저장되므로 1, 3, 2, 0 이 된다(비교 설명). 빈 문자열로 저장된 값이 없기 때문에 마지막 값은 0 이다.

3. DDL: (가), (마). DML: (나). DCL: (다). TCL: (라).

4. 800. 세이브포인트 a 는 첫 200 을 뺀 직후에 만들어졌으므로, a 로 돌아가면 두 번째·세 번째 빼기가 취소된다.

BEGIN;
UPDATE account SET balance = balance - 200 WHERE acct_id = 1;
SAVEPOINT a;
UPDATE account SET balance = balance - 200 WHERE acct_id = 1;
SAVEPOINT b;
UPDATE account SET balance = balance - 200 WHERE acct_id = 1;
ROLLBACK TO a;
COMMIT;
SELECT balance FROM account WHERE acct_id = 1;

실행 결과:

balance
-------
800

5. 첫째, 오류를 받으면 COMMIT 대신 ROLLBACK 을 실행한다(애플리케이션의 예외 처리). 둘째, 보내는 쪽 빼기를 먼저 실행해 실패하면 아무것도 바뀌지 않게 순서를 바꾼다. 둘째 방법만으로는 부족하다. 받는 쪽 UPDATE 가 실패하는 경우가 남으므로 첫째 방법이 반드시 필요하다.

다음 장부터 2과목 SQL 로 넘어간다. 첫 주제는 WHERE 절의 결과를 손으로 예측하는 연습이다.

참고 자료

• SQLite NULL 처리 비교
• SQLite 트랜잭션
• SQLite SAVEPOINT