트랜잭션(transaction)은 모두 성공하거나 모두 취소되어야 하는 작업 묶음입니다. 데이터 모델의 필수 관계를 보면 어떤 데이터를 한 트랜잭션으로 함께 넣어야 하는지 알 수 있습니다.
계좌 이체에서 출금만 되고 입금이 안 되면 돈이 사라지듯, 주문만 저장되고 상품 저장이 실패하면 상품 없는 주문이 남습니다. 모델에서 두 엔터티가 서로 필수로 그려져 있으면 이런 묶음이 필요하다는 신호입니다. 그림의 주문(ORDERS)·주문상품(ORDER_ITEM)은 이 레슨의 예제에서 만드는 테이블이고, 주문은 영업부 최유나(1004)가 받습니다.
1서로 필수인 관계는 한 트랜잭션
주문에는 상품이 하나 이상 있어야 하고 주문상품은 꼭 한 주문에 속해야 하므로, 한쪽만 저장된 상태가 남으면 모델의 규칙을 어깁니다. 그래서 주문 1과 상품 두 건, INSERT(테이블에 새 행을 넣는 SQL 문장 — DML 레슨에서 자세히) 세 문장을 한 묶음으로 다룹니다. 묶음 안의 행은 COMMIT(변경을 데이터베이스에 확정하는 명령)하기 전까지 확정 전이라 다른 사용자에게 보이지 않습니다. 도중에 막힌 문장이 있으면 COMMIT 대신 ROLLBACK(아직 확정하지 않은 변경을 되돌리는 명령)으로 묶음 전체를 되돌립니다. Oracle은 오류가 난 문장 하나만 취소하고 앞에서 넣은 주문은 남겨 두므로, ROLLBACK을 직접 써야 상품 없는 주문이 남지 않습니다.
2넣는 차례와 따로 넣어도 되는 것
넣는 차례는 FK가 정합니다. 주문상품은 주문을 가리키므로, 주문 3보다 주문상품이 먼저 들어가면 가리킬 부모가 없어 막힙니다(ORA-02291). 차례만 지키면 주문을 COMMIT하기 전이어도, 같은 트랜잭션 안의 주문상품은 그 주문을 가리킬 수 있습니다. 지울 때는 이 차례가 거꾸로입니다.
부서와 사원은 양쪽이 모두 선택이라 사원 없는 부서도 올바른 데이터입니다. 그래서 새 부서 50 총무는 사원 없이 따로 넣고 COMMIT해도 됩니다.
| 관계 | 한 트랜잭션으로 | 넣는 차례 |
|---|---|---|
| 주문·주문상품(서로 필수) | 꼭 함께 | 주문 → 주문상품 |
| 부서·사원(선택) | 따로 넣어도 됨 | 부서 → 사원 |
3제약 조건이 못 막는 규칙
같은 관계선이라도 한쪽 규칙은 제약 조건이 막고, 다른 쪽은 막지 못합니다.
| 규칙 | 무엇이 막나 |
|---|---|
| 주문상품은 반드시 주문에 속한다 | 주문상품의 FK · NOT NULL |
| 주문에는 상품이 하나 이상 있다 | 기본 제약 조건으로는 못 막음 |
FK는 자식이 가리키는 부모가 있는지만 보고, CHECK는 그 행 안의 값만 봅니다.
그래서 PK·FK·UNIQUE(같은 값이 두 번 들어가지 못하게 하는 제약)·NOT NULL·CHECK 가운데 어느 것도 '부모에 자식이 있는가'를 따지지 못합니다.
주문 4만 넣고 COMMIT해도 오류 없이 들어가는 까닭이고, 이 규칙은 주문과 주문상품을 한 트랜잭션으로 넣는 쪽이 지킵니다.
참고로 Oracle 23.26.1부터는 여러 테이블에 걸친 제약 CREATE ASSERTION으로 이런 규칙도 막을 수 있습니다. SQLD 시험 범위 밖입니다.
예제
블록마다 주문·주문상품 테이블을 먼저 만듭니다. 주문상품의 주식별자는 '주문번호 + 상품번호'이고, 주문번호는 NOT NULL인 FK입니다. 아래 쿼리는 주문 1과 상품 두 건을 한 트랜잭션으로 넣어 COMMIT하고, 이어서 주문 4를 상품 없이 넣어 COMMIT합니다.
CREATE TABLE ORDERS (
ORDNO NUMBER PRIMARY KEY,
EMPNO NUMBER NOT NULL
REFERENCES EMP (EMPNO)
);
CREATE TABLE ORDER_ITEM (
ORDNO NUMBER NOT NULL
REFERENCES ORDERS (ORDNO),
PRODNO NUMBER NOT NULL,
QTY NUMBER NOT NULL,
PRIMARY KEY (ORDNO, PRODNO)
);
INSERT INTO ORDERS
VALUES (1, 1004);
INSERT INTO ORDER_ITEM
VALUES (1, 101, 2);
INSERT INTO ORDER_ITEM
VALUES (1, 102, 1);
COMMIT;
INSERT INTO ORDERS
VALUES (4, 1004);
COMMIT;
SELECT O.ORDNO,
COUNT(I.PRODNO) AS ITEM_CNT
FROM ORDERS O
LEFT OUTER JOIN ORDER_ITEM I
ON I.ORDNO = O.ORDNO
GROUP BY O.ORDNO
ORDER BY O.ORDNO;| ORDNO | ITEM_CNT |
|---|---|
| 1 | 2 |
| 4 | 0 |
주문 4는 상품이 하나도 없는데 오류 없이 확정되었습니다.
다음 쿼리는 잘못되는 경우 둘을 봅니다.
CREATE TABLE ORDERS (
ORDNO NUMBER PRIMARY KEY,
EMPNO NUMBER NOT NULL
REFERENCES EMP (EMPNO)
);
CREATE TABLE ORDER_ITEM (
ORDNO NUMBER NOT NULL
REFERENCES ORDERS (ORDNO),
PRODNO NUMBER NOT NULL,
QTY NUMBER NOT NULL,
PRIMARY KEY (ORDNO, PRODNO)
);
INSERT INTO ORDERS
VALUES (2, 1004);
INSERT INTO ORDER_ITEM
VALUES (2, 101, NULL);
ROLLBACK;
INSERT INTO ORDER_ITEM
VALUES (3, 101, 1);
SELECT COUNT(*) AS CNT
FROM ORDERS;| CNT |
|---|
| 0 |
- 둘째 INSERT는 수량 QTY가 비어 막힙니다(ORA-01400).
- 막힌 문장만 취소되고 주문 2는 남으므로, ROLLBACK으로 주문 2까지 취소합니다.
- 그 뒤 주문상품 (3, 101, 1)을 먼저 넣으면 부모인 주문 3이 없어 막힙니다(ORA-02291).
- 그래서 ORDERS는 비어 있습니다.
COMMIT·ROLLBACK처럼 트랜잭션을 확정하거나 되돌리는 명령을 TCL(트랜잭션 제어어)이라 하며, TCL 레슨에서 자세히 봅니다.
시험 포인트
- 트랜잭션은 하나의 논리적 작업 단위라, 묶음 안의 작업이 모두 COMMIT되거나 모두 ROLLBACK됩니다. 계좌 이체의 출금과 입금이 대표적인 예입니다.
- 서로 필수인 관계로 이어진 엔터티는 한 트랜잭션으로 함께 넣고, 양쪽이 모두 선택인 관계는 따로 넣을 수 있습니다.
- 넣을 때는 부모를 먼저, 지울 때는 자식을 먼저 처리합니다.
- FK와 NOT NULL은 '자식에는 부모가 있다'를 지킵니다. '부모에는 자식이 하나 이상 있다'는 기본 제약 조건으로 막지 못해 트랜잭션으로 지킵니다.
- Oracle은 오류가 난 문장 하나만 취소합니다. 묶음 전체를 취소하려면 ROLLBACK을 실행합니다.
확인 문제
1. 두 관계가 IE 표기로 그려져 있습니다. 한 트랜잭션으로 함께 넣어야 하는 쪽은 어느 것이고, 그 안에서는 무엇을 먼저 넣을까요?
- (가)
[고객] ||──○< [청구] - (나)
[청구] ||──|< [청구항목]
정답
(나)이고, 청구를 먼저 넣습니다.
청구항목 쪽 끝 |<는 청구마다 항목이 하나 이상, 청구 쪽 끝 ||는 항목마다 청구가 꼭 하나라는 뜻이라 서로 필수입니다.
청구항목이 청구를 가리키므로 청구가 먼저입니다.
(가)는 청구 쪽 끝이 ○<라 청구 없는 고객도 있을 수 있어, 고객은 따로 넣어도 됩니다.
2. 예제 첫 블록에서 주문 4를 넣은 뒤의 COMMIT;만 ROLLBACK;으로 바꾸면 마지막 SELECT의 결과는 어떻게 될까요?
정답
주문 1 한 줄만 나옵니다.
| ORDNO | ITEM_CNT |
|---|---|
| 1 | 2 |
ROLLBACK은 마지막 COMMIT 뒤의 변경만 되돌립니다. 주문 1과 상품 두 건은 이미 확정되어 남고, 확정 전이던 주문 4만 사라집니다.
3. 예제의 주문 1과 그 주문상품 두 건을 지웁니다. 어떤 차례로, 몇 개의 트랜잭션으로 지워야 할까요?
정답
주문상품 두 건을 먼저, 주문 1을 나중에 지우고, 셋을 한 트랜잭션으로 묶어 마지막에 COMMIT합니다.
- 주문을 먼저 지우면 주문상품이 아직 주문 1을 가리키고 있어 막힙니다(ORA-02292).
- 주문상품만 지우고 멈추면 상품 없는 주문 1이 남으므로, 한 트랜잭션으로 묶습니다.