Ray`s Tech Lab

1. 트리거란?

RDB에서 특정 테이블에 이벤트(CREATE or UPDATE or DELETE)가 발생했을 때 수행할 작업들을 정의할 수 있는데, 이를 트리거라고 부릅니다. 즉 DB 테이블에 어떠한 변경이 일어날 때 이를 방아쇠(Trigger)삼아 미리 정의된 SQL 코드가 자동으로 실행되는 것으로 이해할 수 있습니다.

트리거는 실행 시점을 기준으로 크게 다음과 같이 2종류로 구분할 수 있습니다.

1) BEFORE 트리거

UPDATE, INSERT, DELETE 실행 전에 실행되는 트리거입니다. 애플리케이션으로부터 온 잘못된 요청을 거부하는 방식으로 활용 가능하며, UPDATE와 INSERT의 경우 실제 값이 수정/삭제되기 전 그 값을 가공할 수도 있습니다.

2) AFTER 트리거

UPDATE, INSERT, DELETE 실행 후에 실행되는 트리거입니다. 주로 변경된 데이터를 기반으로 다른 테이블을 업데이트하는 후속 조치를 남기는 식으로 많이 활용합니다.

참고) BEFORE 트리거든 AFTER 트리거든, 트리거에 정의된 작업들은 트리거를 실행시킨 작업과 같은 트랜잭션에서 수행됩니다. 즉 트리거가 실패하면 트리거를 실행시킨 작업도 실패합니다. (https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html)

만약 트리거를 적용 범위 수준으로 구분하면 다음과 같이 2종류로 구분 가능합니다.

1) Row-level 트리거

이벤트가 발생된 행별로 수행되는 트리거입니다. 예를 들어 UPDATE절에 의해 10개의 행이 바뀌면, 이 트리거는 10번 실행됩니다.

2) Statement-level 트리거

변경된 행 수에 관련없이 조작되는 전체 문장(statement)단위로 실행되는 트리거입니다. 전체 배치 작업 전/후 하나의 로직만 실행하면 되는 경우 등에 사용할 수 있습니다. (MySQL의 경우 지원하지 않습니다)

참고) SQL Server나 Oracle의 경우 DDL에도 트리거를 만들 수 있습니다.

2. 트리거 생성 방법 (MySQL 기준)

트리거 생성은 CREATE TRIGGER를 통해 수행할 수 있고, 해당 테이블에 대한 Trigger 권한이 필요합니다. 세부적인 생성 문법은 다음과 같습니다.

-- 대괄호는 Optional을 의미합니다.

CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

-- 각 필드
-- 1. trigger_time: { BEFORE | AFTER }
-- 2. trigger_event: { INSERT | UPDATE | DELETE }
-- 3. trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

• DEFINER : 트리거 정의자를 어떤 계정으로 할 것인지 지정합니다. 트리거는 실행되면 트리거를 실행시킨 계정의 권한이 아니라 트리거를 정의한 사람의 권한으로 실행되는데, 그때의 트리거 정의자 계정을 지정하는 것으로 이해할 수 있습니다. 이 필드를 따로 작성하지 않으면 트리거를 생성한 사람이 자동으로 DEFINER가 됩니다. DEFINER로 지정되는 계정에는 해당 테이블(트리거가 붙는 테이블)에 대한 TRIGGER 권한이 필요하며, trigger_body에서 참조되는 테이블들에는 TRIGGER 권한은 필요없으나 적절한 DML 권한이 필요합니다.
• trigger_name : 트리거의 이름으로, 같은 스키마 내에서 트리거 이름은 유일해야 합니다.
• trigger_time : 트리거의 실행시점을 지정합니다.
• trigger_event : 트리거를 발생시킬 이벤트 종류를 지정합니다.
• trigger_order : 같은 테이블의 동일한 이벤트에 대한 트리거가 여러 개일때, 각각의 실행 순서를 지정합니다. FOLLOWS 사용시 지정한 트리거 실행 후에 실행되고, PRECEDES 사용시 지정한 트리거 실행 전에 실행됩니다. 참고로 MySQL 5.7버전부터만 동일 이벤트에 대한 복수의 트리거 정의가 가능합니다.
• trigger_body : 실행할 작업을 정의합니다. BEGIN ... END를 작성하면 여러 작업을 정의할 수 있습니다.

trigger_body에서는 OLD와 NEW라는 키워드를 통해 변경 전후의 데이터에 접근할 수 있습니다.

• OLD : UPDATE 또는 DELETE 문에서 변경/삭제 전의 기존 데이터를 가리킵니다. OLD.column_name을 통해 해당 데이터의 컬럼값에 접근할 수 있습니다.
• NEW: INSERT 또는 UPDATE 문에서 새로 추가되거나 변경될 행의 데이터를 가리킵니다. NEW.column_name을 통해 해당 데이터의 컬럼값에 접근할 수 있습니다.

UPDATE 문에서는 OLD & NEW를 둘 다 접근할 수 있는데, 당연히 BEFORE 트리거에서는 NEW만 수정 가능합니다.

위 내용들을 토대로 트리거 생성 예제를 보면 다음과 같습니다. employees 테이블에 있는 직원 연봉(salary)가 변경될 때마다 변경내용을 salary_audit 테이블에 기록하는 AFTER 트리거입니다.

-- 트리거 생성도 세미콜론으로 끝나는데, trigger_body 내에서 사용되는 세미콜론이 트리거 종료로 인식되는걸 방지
DELIMITER $$

CREATE DEFINER = 'admin_user'@'localhost'
TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 연봉이 변경되었을 때만 로그를 남김
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audit (emp_no, old_salary, new_salary, changed_by)
        VALUES (OLD.emp_no, OLD.salary, NEW.salary, USER());
    END IF;
END$$

DELIMITER ;

여기서 질문. USER()를 통해 변경을 실행한 DB 계정을 기록하는 건 알겠는데, 트리거를 실행시킨 DB 계정이 기록될까요 아니면 DEFINER로 정의된 DB계정이 기록될까요?

실제 예제를 통해 보겠습니다. 현재 employees 테이블에 다음과 같은 레코드가 있습니다.

그리고 admin_user, app_user 계정을 만든 뒤 다음처럼 권한을 부여했습니다.

• admin_user
  1. employees 테이블에 대한 SELECT, TRIGGER 권한
  2. salary_audit 테이블에 대한 INSERT 권한
• app_user
  1. employees 테이블에 대한 SELECT, INSERT 권한

그리고 app_user 계정으로 접속하여 salary를 20000으로 바꿔주었습니다.

salary_audit 테이블에 적재된 결과는..

다음과 같이 트리거 정의자 계정이 아닌, 트리거를 실행시킨 DB 계정이 기록된 것을 볼 수 있었습니다.

사실 이 내용은 MySQL 공식 문서 내에서도 확인 가능합니다 (https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html)

3. ⭐️ 어디에, 그리고 왜 트리거를 쓸까

우선 트리거는 특정 테이블에 이벤트가 발생됐을 때 수행하고 싶은 기능을 자동화하는 것으로 볼 수 있습니다. 애플리케이션에서 반복 작성해야 할 로직을 트리거를 쓰면 한 번만 구현하면 되고, 중요한 데이터에 대한 감사 로깅이 필요한 경우 트리거를 통해 별도의 로그 테이블에 자동으로 기록하게 하거나, 유저 테이블의 회원 상태가 '탈퇴'로 변경됐을 때 관련된 게시판 테이블의 게시물 상태들을 '비공개'로 변경하는 작업 등에 활용 가능합니다. 외부로의 이벤트/메시지 전파를 위해 같은 트랜잭션 내에서 비즈니스 테이블에 데이터를 연동하고 outbox 테이블에 발행할 이벤트를 적재하는 '트랜잭셔널 아웃박스 패턴'의 구현 방법으로 트리거를 채택할 수도 있습니다.

하지만 단순히 '자동화'를 넘어서 트리거의 핵심 역할을 DB 스스로가 데이터의 정합성과 일관성을 유지하도록 강제하는 것으로 바라볼 수 있습니다. 장애라고 하는 것은 트래픽으로 인해서 뿐만 아니라 여러 원인으로 인해 논리적으로 잘못된 형태의 데이터가 적재되면서도 발생할 수 있는데요. 물론 개발자가 애플리케이션 레벨에서 최대한 조심하며 개발할 수 있겠지만 결국 개발자 스스로도 휴먼 에러를 낼 수 있고(예상치 못하게 로직이 꼬인다든가.. 등) 이로 인해 잘못된 데이터가 들어올 수 있습니다. 물론 테이블에 CHECK 등의 제약 조건을 붙이며 최대한 데이터 무결성을 지킬 순 있겠으나, 'VIP 단계의 고객이 주문한 아이템들의 총액은 10,000원을 넘어야 한다' 등의 복잡한 비즈니스 제약들을 표현하고 지키기는 힘듭니다.

이럴 때 트리거를 사용해서 복잡한 데이터의 유효성 검사를 담당하게 할 수 있고, 단순 참조 무결성을 넘어서 복잡한 비즈니스 제약을 DB 스스로가 지키도록 강제할 수 있습니다. 또한 트리거 정의시 작성하는 DEFINER 계정에만 A 테이블에 대한 권한을 준다면, 해당 트리거 실행을 제외하고는 A 테이블에 데이터가 넣어질 수 없도록 아예 틀어막아 버릴 수도 있습니다. (개발자 실수 등이 생겨도 잘못된 데이터가 들어가는 일 자체가 안 생기게 됨)

그렇다고 트리거가 늘 좋냐!고 하면 당연히 아닙니다. 다음과 같은 단점들이 있습니다.

1) 성능 저하

애플리케이션에서 실행한 DB 작업 외에도 트리거를 통한 부가 작업이 수행되는 만큼, 성능 저하가 발생됩니다. 특히 MySQL은 Row-level 트리거이므로 1,000개의 행이 갱신되면 트리거가 1,000번 실행되므로.. 만약 트리거가 부착된 테이블의 변경이 잦다면 그만큼 성능 오버헤드도 비례해서 올라갑니다.

2) 불투명성

"A 테이블에 데이터를 넣으면 B 테이블에도 데이터를 추가한다" 라는 비즈니스 로직이 있다고 가정해보겠습니다. 이 비즈니스 로직이 애플리케이션 레벨에서 우리에게 친숙한 자바 코드 등으로 작성되어 있다면 처음 서비스를 접하는 개발자도 이런 로직이 있다고 쉽게 파악할 수 있습니다. 하지만 트리거를 통해 DB 레벨에서 이 비즈니스 로직이 구현되어 있다면, 개발자가 이런 비즈니스 로직이 있다고 인지하기도 어려울 뿐더러 문제 발생 시 디버깅도 어렵게 됩니다. 더 나아가 한 테이블에 부착된 트리거들이 FOLLOWS 등을 통해 여러 선후행 관계를 가진다면 골치가 더 아파지게 됩니다.

3) 작동 확인 방법의 번거로움

트리거는 작동을 확인하기 위해 명시적으로 실행해볼 수 있는 방법이 없어서, 잘 동작하는지 확인하려면 직접 부착된 테이블에 INSERT나 UPDATE, DELETE를 해줘야 합니다.

트리거 사용의 장단점을 표로 정리하면 다음과 같습니다.

그러면 트리거는 어떤 상황에 쓰면 좋고 어떤 상황에는 안 쓰는게 좋을까요? 개인적으로 생각해본 건 다음과 같습니다.

• 써도 괜찮을 때
  1. DB에 직접 접근해서 데이터를 수동으로 수정하는 경우가 많아, 휴먼 에러를 방지하고 데이터 무결성을 반드시 지켜주고 싶을 때 (앞서 말한 것처럼 DEFINER로 작성하는 계정에만 특정 테이블에 대한 권한을 주는 식으로 등..)
  2. 여러 애플리케이션이 하나의 DB를 공유하고 있어서 모두에게 동일한 규칙을 강제하고 싶을 때
• 쓰면 안 좋을 때
  1. 트리거를 부착하고 싶은 테이블의 데이터 변경이 잦아서 성능 저하가 예상될 때 (MySQL은 특히나 Row-level 트리거이므로)
  2. 비즈니스 로직이 자주 변경될 때 (대부분의 경우 애플리케이션 코드를 수정하는게 더 쉬울 것 같습니다)
  3. 서비스의 로직을 애플리케이션에서만 관리하는게 좋다고 판단될 때

4. 알아두면 좋은 트리거 주의사항

1. "INSERT INTO ... ON DUPLICATE SET" 는 중복이 없다면 BEFORE INSERT와 AFTER INSERT 이벤트를 발생시키고, 중복이 있다면 BEFORE UPDATE와 AFTER UPDATE 이벤트를 발생시킵니다.
2. 테이블에 외래키 관계에 의해 자동으로 변경된 경우는 트리거가 호출되지 않습니다.
3. 트리거 작성 시 BEGIN .. END 블록 내에서는 ROLLBACK / COMMIT 등 트랜잭션을 명시적으로 시작하거나 종료하는 SQL 문장을 작성할 수 없습니다.
4. mysql과 information_schema, performance_schema 데이터베이스에 존재하는 테이블들에는 트리거를 생성할 수 없습니다.

5. 레퍼런스

https://dev.mysql.com/doc/refman/8.4/en/trigger-syntax.html 

https://dev.mysql.com/doc/refman/8.4/en/create-trigger.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/dml-triggers.html#GUID-E76C8044-6942-4573-B7DB-3502FB96CF6F

https://www.ibm.com/docs/en/ias?topic=triggers-types

들어가며

BE 개발자라면 DB를 공부하면서 트랜잭션 격리 수준(Transaction Isolation Level)을 접한 적이 있을 겁니다. 저 역시 취준 시절 기술 면접을 준비하면서, 그리고 DB를 공부하면서 트랜잭션 격리 수준을 공부한 적이 있는데요. 사실 공부한 적만 있지 사이드 프로젝트 또는 실무에서 트랜잭션 격리 수준으로 인한 문제를 마주쳤던 적은 없었습니다. 하지만 이번에 사내에서 낙관적 락으로 동시성 문제를 방지하는 기능을 개발하는 과정에서 트랜잭션 격리 수준에 대해 어설프게 알고 있던 덕에 뜻밖의 곤욕을 치르게 됐는데요. 아무래도 저처럼 이론으로만 트랜잭션 격리 수준을 공부했던 분들에게 도움이 될 수도 있는 내용인 것 같아 공유하고자 이 글을 쓰게 됐습니다.

※ 하기 내용은 트랜잭션 격리 수준, 그 중에서도 REPEATABLE_READ에 대한 지식이 있음을 바탕으로 작성했습니다.

※ 보안 문제 등으로 제가 담당한 시스템의 정확한 명칭과 세부 업무, 기능은 언급하지 않고자 했습니다. 이 점 양해 부탁드립니다.

개발 과정 & 고민했던 점

제가 이번에 개발한 기능은 "통보서 중복 작성 방지" 기능이었습니다. 기존 통보서 작성 프로세스는 다음과 같았습니다.

1. 통보서를 보낼 업무(BUSINESS_INFO 테이블의 레코드)에 대해 특정 프로세스 진행
2. 고객에게 보낼 통보서 내용을 DB에 INSERT ( = 통보서 작성)
3. 해당 업무의 통보상태를 NOT_REGISTER(통보 내용 미작성)에서 REGISTER(통보 내용 작성)으로 UPDATE

해당 비즈니스 로직은 코드 상에서는 다음처럼 구현되어 있었습니다.

@Transactional
public void processNotice(String bizSeqNbr) {
    // 통보서를 보낼 업무 정보 조회
    BusinessInfo businessInfo = readBusinessInfo(bizSeqNbr);

    // .. 생략 (특정 프로세스 진행)

    // 고객에게 보낼 통보서 내용 INSERT
    insertNotice(notice);

    // 해당 업무의 통보상태를 UPDATE
    updateBusinessNoticeStatus(bizSeqNbr, NoticeStatus.REGISTER);
}

-- updateBusinessNoticeStatus

UPDATE BUSINESS_INFO
SET NOTICE_STATUS    = #{BUSINESS_NOTICE_STATUS}
  , LAST_CHANGE_ID   = #{EMPNO}
  , LAST_CHANGE_DTTM = NOW()
WHERE BUSINESS_SEQUENCE_NUMBER = #{BIZ_SEQ_NBR};

통보서 작성 후 해당 업무의 통보상태(BUSINESS_INFO.NOTICE_STATUS)를 REGISTER로 갱신하므로, 중복 작성을 방지하는 가장 쉬운 방법은 해당 업무의 통보상태 값이 REGISTER라면  통보서를 작성하지 않도록 하는 것이라고 생각했습니다. 하지만 단순히 이 상태 조회 체크 로직만 둔다면 동시성 문제가 발생할 수 있으니 이를 막기 위해서는 업무 정보를 조회하는 시점부터 비관적 락을 적용하는 방법도 있었으나, 이 시스템은 소수의 직원이 사용하는 사내 시스템이었기 때문에 중복 작성이 흔하게 일어나진 않을 거라 판단하여 다음과 같은 낙관적 락 형태를 적용한 쿼리를 활용하기로 했습니다. 

-- updateBusinessNoticeStatusToRegister

UPDATE BUSINESS_INFO
SET NOTICE_STATUS    = #{BUSINESS_NOTICE_STATUS}
  , LAST_CHANGE_ID   = #{EMPNO}
  , LAST_CHANGE_DTTM = NOW()
WHERE BUSINESS_SEQUENCE_NUMBER = #{BIZ_SEQ_NBR}
AND NOTICE_STATUS = 'NOT_REGISTER'; 

-- version 등의 컬럼을 추가할 수 없던 상황이라.. 통보서 작성 전의 통보상태는 NOT_REGISTER임을 활용
-- 갱신된 레코드가 0개라면 예외를 일으켜 롤백

그러나 사용 중인 DB(=MySQL)은 트랜잭션 격리 수준으로 디폴트인 REPEATABLE_READ를 사용중이었습니다. 동일 트랜잭션에서는 여러 번 SELECT를 해도 항상 같은 결과가 나오도록 보장하는 격리 수준으로, 트랜잭션 시작 후 최초로 읽기 시작한 시점의 스냅샷을 참조하는 원리인데요. 저는 이 스냅샷을 UPDATE 시에도 동일하게 활용할 것이라고 생각해서, 다음처럼 낙관적 락을 적용해도 통보서를 중복으로 작성되는 문제가 생길 수 있다는 생각이 들었습니다.

하지만 여러 번의 테스트 결과, 중복 갱신은 발생하지 않았습니다. 벌어지지 않을 일에 대해 "헉 이거 문제 생기는 거 아냐?"라고 생각하고 있던 거죠.. 근데 왜 발생하지 않았을까요? 저는 트랜잭션 안에서 SELECT와 UPDATE가 같은 시점의 데이터(스냅샷)를 바라보고 수행된다고 생각했지만, 실제로는 SELECT는 과거의 스냅샷을 바라보고 UPDATE는 현재를 바라보고 수행되기 때문이었습니다.

어설프게 알면 당하는 MySQL REPEATABLE_READ의 함정

이런 현상은 MySQL 공식 문서에서도 찾아볼 수 있었습니다.

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

• 일관된 읽기(Consistent Read)란 InnoDB가 MVCC을 사용해, 쿼리에 특정 시점의 DB 상태를 스냅샷으로 제공하는 것.
• 쿼리는 해당 시점 이전에 커밋된 트랜잭션의 변경분은 볼 수 있지만, 그 이후에 커밋되었거나 아직 커밋되지 않은 트랜잭션의 변경분은 볼 수 없다. (= REPEATABLE_READ의 원리)
• 단, 같은 트랜잭션 내에서 수행된 DML(INSERT, UPDATE 등)로 인한 변경분은 볼 수 있다.
• 이 때문에 아래와 같은 비일관성(anomaly) 이 발생 가능하다.
  1. 같은 트랜잭션 내에서 일부 레코드만 UPDATE한 후 SELECT를 실행하면, 업데이트된 레코드만 최신 버전으로 보인다.
  2. 여러 트랜잭션이 동시에 동일 테이블에 대해 업데이트를 하고 있다면, 트랜잭션 내에서 SELECT로 조회되는 결과는 실제 데이터베이스에 존재한 적 없던 상태를 보여줄 수도 있다. (트랜잭션 내에서는 동일 트랜잭션의 DML로 인한 변경분만 보이므로)

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction.

• 스냅샷은 트랜잭션 내의 SELECT 문에만 적용되며, DML(INSERT, UPDATE 등)에도 적용되어야 하는 건 아니다.
• A 트랜잭션이 일부 레코드를 INSERT 또는 UPDATE한 뒤 커밋했을 때, 동시에 실행 중이던 REPEATABLE_READ 수준의 B 트랜잭션에서 실행되는 DELETE나 UPDATE 문은 A 트랜잭션이 방금 커밋한 행에도 영향을 줄 수 있다(B 트랜잭션에서는 그 레코드들을 SELECT로 조회할 수 없더라도)
  • >> UPDATE / DELETE는 특정 시점의 스냅샷이 아닌 현재를 바라보고 수행되기 때문으로 이해할 수 있습니다.
• 한 트랜잭션이 다른 트랜잭션에서 커밋한 레코드를 UPDATE하거나 DELETE할 경우에는 그로 인한 변경사항이 현재 트랜잭션 내에서 즉시 반영되어 보이게 된다. (하단 예제 이미지 참고)

이를 토대로 정리해보겠습니다. UPDATE / DELETE는 특정 시점의 스냅샷이 아닌 현재를 바라보고 수행됩니다. 따라서 하나의 트랜잭션 안에서 SELECT한 결과를 조건으로 활용해 UPDATE / DELETE 등을 해줄 때, 이런 트랜잭션이 여러 개가 동시에 실행된다면 내가 예상한 레코드가 UPDATE / DELETE되지 않거나 내가 예상하지 않은 레코드가 UPDATE / DELETE되는, 즉 예상치 못한 동작이 발생할 수 있습니다. 

결국 트랜잭션 격리 수준은 읽기와 관련된 문제들(Dirty Read, Non-Repeatable Read 등)들에 대한 해결책은 될 수 있으나, 쓰기에 대한 문제들(Lost Update 등)은 여전히 발생 가능합니다. 이를 위해 낙관적 락, 비관적 락(사실 끝나서 얘기하지만 저는 낙관적 동시성 제어 등으로 부르는게 더 맞다고 생각하는 편)을 적절히 적용해서 쓰기와 관련된 문제들을 해결할 수 있어야 하죠. 다행히 제가 이번 기능 개발에서 적용한 낙관적 락 형태의 쿼리는 시스템 특성상 해당 쿼리를 활용하는 코드가 제한되어 있어 동시성 문제가 나지 않았지만, 이번 삽질을 통해 낙관적 락 적용시에는 version등의 컬럼을 활용해 UPDATE 조건을 정확하고 유일하게 적용해야 쓰기 관련된 동시성 문제들을 깔끔하게 해결할 수 있다는 인사이트를 얻었습니다. 단순한 CRUD를 넘어 격리 수준 등을 토대로 나올 수 있는 복합적인 동시성 문제들까지 고려하는 습관을 잡아야 할 것 같습니다 :)

Reference

https://dev.mysql.com/doc/refman/8.4/en/innodb-consistent-read.html

https://stackoverflow.com/questions/59287861/repeatable-read-isolation-level-select-vs-update-where 

1. 트랜잭션이란?

데이터베이스 관리 시스템 내의 논리적인 작업 단위를 말합니다. 10,000원 단위로 돈을 내라고 하는데 5,000원을 내면 안되는 것처럼, 작업 단위라는 말은 이 작업이 반만 실행되는 일은 없어야 한다는 것, 다시 말하면  전체적으로 실행되거나 전혀 실행되지 않아야 한다는 것을 뜻합니다. 즉 트랜잭션은 데이터베이스가 처리해야 하는 일종의 작업 시나리오인데, 데이터베이스의 일관성과 신뢰성을 보장하는 All or Nothing의 시나리오를 말하는 것으로 이해할 수 있습니다.

• 일관성 : 트랜잭션 전후로 데이터베이스 내 데이터는 무결해야 한다는 것, 즉 데이터베이스 내 데이터들은 어떤 순간에도 정확하고 완전해야 하며 이 데이터를 신뢰할 수 있어야 한다는 것을 말합니다. 예를 들어 같은 DB에 있는 A계좌에서 B계좌로 돈을 보냈는데, A계좌에 출금 기록은 있지만 B계좌에 입금 기록이 없다면 이 DB는 일관된 상태로 볼 수 없습니다.(참고로 이 케이스는 명시되지 않은 제약 조건으로 볼 수 있으며, 반대로 명시적인 제약 조건으로는 외래 키 제약 등이 있습니다.)
• 신뢰성 : 데이터베이스 내 데이터가 안전하게 저장되고 정확하게 관리되어, 시스템 장애나 오류 상황에서도 데이터가 손실되거나 손상되지 않도록 보장하는 특성을 말합니다.

트랜잭션은 보통 다음과 같은 과정으로 진행됩니다.

1. 트랜잭션 시작(BEGIN)
2. 작업 수행(데이터 read/write 등)
3. 일련의 과정들이 문제없이 수행됐다면 COMMIT (작업 내용이 DB에 영구저장됨)
4. 중간에 문제가 발생했다면 ROLLBACK (트랜잭션 시작 전으로 되돌아감)

ex)

-- 트랜잭션 시작
BEGIN;

-- 작업 1
UPDATE people
SET name = 'Ray'
WHERE id = 1;

-- 작업 2
UPDATE people
SET lastname = 'Cho'
WHERE id = 10;

-- 일련의 작업이 문제없이 진행되면 DB에 반영
COMMIT;

2. 트랜잭션의 ACID란?

데이터베이스의 일관성과 신뢰성을 위해, 앞서 소개한 All or Nothing을 포함해 트랜잭션은 ACID라고 불리는 4가지 성질을 가져야 합니다.

1) Atomicity (원자성)

트랜잭션으로 처리할 작업 시나리오는 모두 처리되든지 아니면 하나도 처리되지 말아야 하든지에 대한 것으로, 앞서 설명한 All or Nothing에 해당합니다. COMMIT 실행 시 작업 내용을 DB에 쓰는 것과 ROLLBACK 실행시 DB를 되돌리는 것은 DBMS가 해주는 부분이므로, 개발자는 트랜잭션을 만들면서 언제 COMMIT을 하고 어떤 경우에 ROLLBACK을 할 지를 잘 챙겨야 합니다.

2) Consistency (일관성)

트랜잭션 처리 전후로 DB는 일관된 상태를 유지해야 한다는 것을 말합니다. 물론 뜯어보면 트랜잭션 처리 전과 후는 데이터베이스가 다른 상태이겠지만, 어쨌든 둘 다 똑같이 일관된 상태를 유지해야 한다는 뜻입니다. 외래 키 또는 CHECK문을 통한 명시적인 제약을 위반했는지는 DBMS가 COMMIT 전에 확인하고 알려주지만, Application 관점에서 트랜잭션이 일관성을 보장할 수 있도록 하기 위해서는 개발자가 트랜잭션을 잘 정의할 필요가 있습니다.

3) Isolation (독립성)

데이터베이스에서 여러 트랜잭션이 동시에 수행되어도 각 트랜잭션들이 서로 영향을 주지 않아야 함을 말합니다. Atomicity(원자성)는 어떤 트랜잭션을 처리하기 전까지 다른 트랜잭션을 처리하지 않는다는 개념이 아니라 단순히 "작업"이라는 관점에서 이 작업이 모두 수행되냐 아니냐의 개념입니다. 따라서 여러 트랜잭션이 동시에 실행될 수 있고, 이로 인한 동시성 문제들이 발생할 수 있기 때문에 트랜잭션들을 각각 따로따로 실행되는 것처럼 독립시켜 각 트랜잭션들이 서로 영향을 주지 않게 할 필요가 있습니다.

이를 만족하는 가장 쉽고 강력한 방법은 모든 트랜잭션을 직렬로 수행하는 것이나 이는 동시성이 감소하므로 DB 퍼포먼스에 심각한 악영향을 끼칩니다. 따라서 DBMS는 보통 여러 종류의 Isolation Level을 만들어 트랜잭션들이 서로 영향을 주는 정도를 설정할 수 있도록 제공합니다. 이를 통해 개발자는 어떤 Level로 트랜잭션을 동작시킬 지 설정할 수 있으며, 각 Level에서 발생 가능한 문제들을 인지하고 다룰 수 있어야 합니다. (아래에서 상세히 다뤄보겠습니다)

4) Durability (영속성)

트랜잭션이 완료되면 그 결과는 영구적으로 저장되어야 함을 말합니다. 시스템이 고장 나더라도 트랜잭션의 결과는 손실되지 않아야 하며, 데이터베이스에 영구적으로 반영되어야 함을 뜻합니다. 기본적으로 DBMS가 보장합니다.

3. 트랜잭션 동시 실행으로 발생 가능한 대표적인 문제들

1) Dirty Read

COMMIT되지 않은 변화분을 읽는 문제를 말합니다.

가령 x에 10, y에 20이 저장되어 있을 때

• Tx A : X에 Y를 더함
• Tx B : Y에 10을 더함

을 한다고 가정해보겠습니다. 어떠한 사유로 인해 Tx B가 롤백되는 케이스 중 다음과 같은 케이스가 발생 가능합니다.

Tx A 입장에선 y값을 20으로 읽어서 최종적으로 x에 30을 write했습니다. 그러나 Tx B가 마지막에 롤백되어 Tx A는 유효하지 않은 y값 20을 읽었던 셈이 됐고 결과적으로 x에 write했던 30도 유효하지 않은 값이 됐습니다. 이 현상을 Dirty Read라고 부르며 일관되지 않은 결과를 초래하는 원인이 될 수 있습니다.

2) Non-Repeatable Read

한 트랜잭션에서 어떠한 데이터를 두 번 이상 읽을 때각 읽기 결과가 달라지는 것을 말합니다. 

가령 x에 10이 있을 때

• Tx A : x를 한 번 읽고 어떤 로직을 처리하다가 x를 한 번 더 읽음
• Tx B : x에 20을 더함

을 한다고 가정해보겠습니다. Tx A에서 진행되는 두 번의 읽기 사이에 Tx B가 실행&COMMIT되면 다음 현상이 발생 가능합니다.

Tx A 입장에선 같은 데이터인 x를 한 트랜잭션에서 두 번 읽었는데 결과가 달라졌습니다. DB 관점에서 보면 Tx B가 실행되기 전후에 맞춰 데이터를 잘 읽은 것으로 볼 수도 있습니다. 그러나 Application 관점에서 보면 첫 번째 읽기를 통해 처리한 로직이 왜곡되든가 중복 처리가 되는 현상 등이 발생 가능합니다. 즉 "두 번의 읽기 결과값이 달라지는 것이 문제"라기보다는 "트랜잭션이 시작된 후 읽은 데이터를 신뢰할 수 없게 된다는 것이 문제"라고 봐야 합니다.

3) Phantom Read

한 트랜잭션에서 같은 조건의 데이터를 여러 번 읽을 때 각 읽기 결과가 달라지는 것(읽었던 행이 없어지거나 새 행이 읽히는)을 말합니다.

가령 v라는 컬럼값을 각각 10, 20으로 갖는 x1과 x2라는 레코드가 있을 때

• Tx A : v=10인 레코드를 한 번 읽고 어떤 로직을 처리하다가 같은 조건으로 레코드를 한 번 더 읽음
• Tx B : x2의 v를 10으로 바꿈

을 한다고 가정해보겠습니다. Tx A에서 진행되는 두 번의 읽기 사이에 Tx B가 실행&COMMIT되면 다음 현상이 발생 가능합니다.

한 트랜잭션에서 같은 조건으로 데이터를 읽었는데 첫 번째 읽기에선 없던 레코드가 두 번째 읽기에서 생겼습니다. 만약 Tx B에서 x1을 지웠다면 Tx A의 두 번째 읽기에선 어떤 레코드도 잡히지 않을 텐데 이것도 Phantom Read입니다. Non-Repeatable Read와 비슷하게 DB 관점에서 보면 Tx B가 실행되기 전후에 맞춰 데이터를 잘 읽은 것으로 볼 수 있으나, Application 관점에서 보면 첫 번째 읽기를 통해 처리한 로직이 왜곡되는 현상 등이 발생 가능합니다. 이 역시도 "두 번째 읽기에서 새로운 행이 추가되거나 읽었던 행이 삭제되는 게 문제"라기보다는 "트랜잭션이 시작된 후 특정 조건으로 읽은 결과를 신뢰할 수 없게 되는게 문제"라고 봐야 합니다.

4. 트랜잭션 격리 수준 (Transaction Isolation Level)

위에서 살펴봤던 동시성에 관련된 문제들을 방지하려면 각 트랜잭션들이 서로에게 영향을 주지 않도록 격리해야 하나, 그 정도를 강하게 할 경우 동시성이 낮아지게 됩니다. 즉 동시에 처리 가능한 트랜잭션 수가 줄어드는 것이고 이는 곧 DB 퍼포먼스 저하로 이어집니다. 그래서 DBMS는 보통 여러 Isolation Level을 만들어 트랜잭션이 서로 영향을 주는 정도를 개발자들이 선택할 수 있도록 해뒀습니다. SQL 표준에서 정의한 Isolation Level에선 가장 낮은 레벨이 Read Uncommitted, 가장 높은 레벨이 Serializable로 레벨이 높아질수록 동시성이 낮아지나 격리 정도가 높아지고, 레벨이 낮아질수록 동시성이 높아지나 격리 정도가 낮아집니다.

1) Read Uncommitted

트랜잭션 실행 중에 다른 트랜잭션에서 커밋되지 않은 데이터도 읽을 수 있도록 하는 레벨입니다. 사실상 트랜잭션 격리를 하지 않는 레벨로 Dirty Read, Non-Repeatable Read, Phantom Read를 비롯한 모든 동시성 문제가 발생 가능합니다. 다만 동시성은 가장 높은 레벨입니다.

2) Read Committed

트랜잭션 실행 중엔 다른 트랜잭션에서 커밋된 데이터만 읽을 수 있도록 하는 레벨입니다. Dirty Read 문제가 발생되지 않으나 Non-Repeatable Read, Phantom Read 문제는 여전히 발생 가능합니다.

3) Repeatable Read

SELECT로 읽은 레코드에 락을 거는 방법 등을 통해 동일 트랜잭션 실행 중 한 레코드를 여러 번 읽어도 항상 같은 결과가 나오도록 보장하는 레벨입니다. Dirty Read, Non-Repeatable Read 문제는 발생되지 않으나 Phantom Read 문제는 여전히 발생 가능합니다.

4) Serializable

트랜잭션이 읽거나 쓰는 모든 데이터에 락을 거는 방법 등을 통해 트랜잭션들을 직렬로 실행하는 것처럼 보이게 하는 레벨입니다. Dirty Read, Non-Repeatable Read, Phantom Read를 비롯한 모든 동시성 문제가 발생되지 않는 레벨입니다. 다만 동시성이 가장 낮습니다.

표로 정리하면 다음과 같습니다.

5. 사실 다른 문제들도 있습니다

널리 알려진 문제가 Read Uncommitted, Non-Repeatable Read, Phantom Read일 뿐 DB에서 발생 가능한 동시성 관련 문제들은 더 많습니다. 대표적으로 Lost Update, Write Skew 등이 있습니다.

1) Lost Update

두 트랜잭션이 동시에 같은 데이터를 업데이트하는 경우, 한 트랜잭션의 변경분이 다른 트랜잭션에 의해 덮어쓰여지는 것을 말합니다.

가령 x에 10이 있을 때

• Tx A : X에 20을 더함
• Tx B : X에 10을 더함

을 한다고 가정해보겠습니다. x에는 총 30이 더해질 것으로 예상되나, Tx A가 진행되는 사이 Tx B가 실행되면 다음 문제가 발생 가능합니다.

Tx B가 실행되어 x에 20이 저장됐으나, Tx A는 본인이 원래 읽었던 10에 20을 더한 30을 write하게 되어 결론적으로 x값은 30이 아닌 20만 증가됐습니다. 즉 Tx B가 수행한 변경분이 Tx A에 의해 덮어씌워진 셈이며, 이는 일관되지 않은 결과를 초래할 수 있습니다.

2) Write Skew

두 트랜잭션이 서로 독립적으로 데이터를 읽고 검증한 후 업데이트할 때 데이터 무결성이 깨지는 것을 말합니다.

가령 x, y가 각각 1이고 x + y >= 1이어야 한다는 제약이 있다고 할 때

• Tx A : x에서 1을 뺌
• Tx B : y에서 1을 뺌

을 한다고 가정해보겠습니다. 현실 세계에선 현재 병원에 의사가 두 명 재직 중인데, 뭐가 됐든 의사 한 명은 재직 중이어야 하는 제약이 있는 상황에서 두 의사가 동시에 휴직 신청을 한 경우로 빗댈 수 있습니다.

Tx A에서 먼저 x를 0으로 업데이트했습니다. 만약 트랜잭션이 직렬로 수행됐다면 Tx B는 실행되지 않았어야 했을 것입니다. 그러나 Tx B가 데이터를 읽은 시점에서는 x, y 모두 1이었으니 Tx B도 y를 0으로 업데이트해줬습니다. 결국 각 트랜잭션이 서로 다른 데이터를 업데이트한 것이지만 결과적으론 x, y가 모두 0이 되어 데이터 무결성이 깨지게 된 셈입니다.

이 문제들 외에도 여러 문제가 존재합니다. 물론 어떤 문제들은 적절히 격리 수준을 설정하는 것으로 막아줄 수 있고, DB 엔진들은 저마다 각 격리 수준을 구현하는 방법이 다르기 때문에 생각보다 더 넓은 범위의 문제들을 방지해주기도 합니다. 예를 들어 MySQL InnoDB는 Repeatable Read 수준을 사용해도 Next-Key Lock 등을 통해 Phantom Read 문제를 방지해주고, PostgreSQL은 Snaption Isolation이란 형태로 Repeatable Read를 구현하여 Phantom Read 문제를 방지해줍니다.

그러나 DB에서 제공하는 격리 수준으로 방지할 수 없는 문제들도 있습니다. 그리고 격리 수준은 데이터를 읽거나 쓰는 시점에서의 충돌을 막아주는 것으로 처리된 결과가 논리적으로 무결한 상태임을 보장하진 않습니다. Write Skew같은 문제는 Repeatable Read같은 격리 수준에서도 발생 가능한 것을 예시로 들 수 있습니다.

즉 개발자는 시스템의 요구사항에 맞는 적절한 격리 수준을 선택하는 것도 중요하나, 다른 전략을 추가적으로 활용해서 DB에서 발생되는 동시성 문제들을 해결할 수 있어야 합니다. 이를 위해 낙관적 동시성 제어, 비관적 동시성 제어로 대표되는 동시성 제어 전략을 사용할 수 있습니다.

TMI : 두 동시성 제어 전략은 낙관적 락, 비관적 락이라는 이름으로 더 많이 알려져 있는 것으로 보입니다. 그러나 둘 다 락을 사용하는 전략은 아니기 때문에 동시성 제어가 더 정확한 표현이라고 생각되어 이 글을 쓰면서 동시성 제어라는 이름으로 작성했습니다.

6. 동시성 제어 전략 : 낙관적 동시성 제어와 비관적 동시성 제어

이름에서 알 수 있듯이 낙관적 동시성 제어는 "충돌이 나지 않을 거야"라고 가정하여 최대한 동시성을 확보한 뒤 커밋 직전에 충돌 여부를 확인하는 방법이고, 비관적 동시성 제어는 "충돌이 분명 날 거야"라고 가정하여 처음부터 충돌 자체를 방지하는 메커니즘입니다. 자세히 살펴보면 다음과 같습니다.

1) 낙관적 동시성 제어 (Optimistic Concurrency Control)

자원에 락을 걸지 않고 작업을 수행하다가 커밋 시점에 다른 트랜잭션에 의해 데이터가 변경됐는지 확인하는 전략으로, 충돌이 나지 않을 것이라 가정하고 자원에 대한 잠금 없이 높은 동시성을 유지하면서 작업을 처리하는 개념입니다. 보통 데이터를 읽을 때 그 시점의 상태 정보(버전 정보 등)을 저장해뒀다가 데이터 변경 시점에 상태 정보를 비교하여 변화가 있다면 다른 트랜잭션이 데이터를 변경한 것으로 간주하고 갱신을 취소하거나 롤백하는 형태로 구현됩니다. 만약 상태 정보가 같다면 상태 정보 갱신도 함께 수행됩니다. 락을 사용하지 않아 높은 동시성을 제공할 수 있으므로 데이터 충돌이 적은 환경에서 사용한다면 성능 최적화를 기대할 수 있다는 장점이 있으나, 충돌이 자주 발생하는 환경이라면 갱신 실패 또는 롤백이 반복되며 성능 저하의 주범이 될 수 있다는 단점이 있습니다.

2) 비관적 동시성 제어 (Pessimistic Concurrency Control)

자원(테이블, 레코드 등)에 락을 걸고 작업을 수행하여 다른 트랜잭션의 접근을 차단하는 전략으로, 충돌이 발생함을 가정하고 리소스에 대한 락을 미리 획득하여 특정 기간 동안 해당 리소스에 대한 독점적인 액세스를 유지하는 개념입니다. 보통 SELECT FOR UPDATE를 사용해 데이터를 읽는 시점부터 배타 락을 설정하는 형태 등으로 구현됩니다. 충돌 가능성을 사전에 차단하는 만큼 데이터 무결성을 강력히 보장하고 충돌이 자주 발생하는 상황에선 낙관적 락보다 좀 더 나은 성능을 기대할 수 있다는 장점이 있으나, 락으로 인한 동시성 저하 및 락 획득을 위한 대기 시간 증가, 데드락 발생 가능성이 존재한다는 단점이 있습니다.

• 공유 락(Shared Lock) : Read Lock으로도 불리며 자원에 대한 쓰기는 안 되지만 자원을 읽는 동안 다른 트랜잭션에서 데이터를 읽는 것은 허용하는 개념의 락입니다.
• 배타 락(Exclusive Lock) : Write Lock으로도 불리며 자원에 대해 다른 트랜잭션의 읽기와 쓰기를 모두 막는 개념의 락입니다.

참고

https://cybernerdie.medium.com/database-transactions-explained-a-deep-dive-into-reliability-17ab4e17117a

https://d2.naver.com/helloworld/407507

https://www.youtube.com/watch?v=sLJ8ypeHGlM&t=804s

https://www.youtube.com/watch?v=bLLarZTrebU&list=LL&index=9

https://medium.com/@iamssrofficial/concurrency-in-databases-database-isolation-levels-dirty-read-phantom-read-non-repeatable-read-320ff3553d6d

https://medium.com/@abhirup.acharya009/managing-concurrent-access-optimistic-locking-vs-pessimistic-locking-0f6a64294db7

Subquery란

SQL 쿼리 내에서 또다른 SELECT절을 사용하는 문법을 쓸 때, 내부에 포함되는 SELECT절을 서브쿼리(Subquery)라 부른다. SELECT 절에서 사용되는 서브쿼리를 스칼라 서브쿼리(Scala Subquery), FROM절에서 사용되는 서브쿼리를 인라인뷰(Inline View), WHERE절에서 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 부른다.

Scala Subquery란

SELECT절에서 사용되는 서브쿼리로, 반드시 하나의 record(행)을 리턴해야하며 그 행도 하나의 컬럼값만 가지고 있어야 한다. (즉 단일행, 단일 컬럼을 반환해야 한다)

예제로, 다음과 같은 두 개의 테이블이 있다고 하자. DEPARTMENTS는 회사에 있는 부서들의 부서id와 부서명을, EMPLOYEES는 임직원들의 이름과 소속 부서id 등을 가진다.

이 때, 임직원들의 이름과 그들이 속한 부서의 이름을 함께 조회하고 싶다고 하고, 스칼라 서브쿼리를 사용해본다고 하자. 이때, 스칼라 서브쿼리의 FROM절에서 DEPARTMENTS를 참조하는가, EMPLOYEES를 참조하는가를 참조하는가를 주의해야 한다. 

스칼라 서브쿼리의 FROM절에서 DEPARTMENTS를 참조할 경우의 쿼리와 결과는 다음과 같다.

SELECT EMP_NAME, (
    SELECT DEPT_NAME
    FROM DEPARTMENTS d
    WHERE d.DEPT_ID = e.DEPT_ID) 
FROM 
    EMPLOYEES e;

스칼라 서브쿼리의 FROM절에서 EMPLOYEES를 참조할 경우의 쿼리와 결과는 다음과 같다.

SELECT (
        SELECT EMP_NAME
        FROM EMPLOYEES e
        WHERE d.DEPT_ID = e.DEPT_ID
    ), DEPT_NAME 
FROM 
    DEPARTMENTS d;

서브쿼리의 FROM절에서 DEPARTMENTS를 참조하는 경우, d.DEPT_ID = e.DEPT_ID를 만족하는 레코드가 하나만 나오기 때문에 문제가 되지 않는다. 하지만 서브쿼리의 FROM절에서 EMPLOYEES를 참조하는 경우, d.DEPT_ID = e.DEPT_ID를 만족하는 레코드가 다수가 나오기 때문에 문제가 되고, 위와 같은 오류가 생기는 것이다.

즉, 스칼라 서브쿼리는 반드시 하나의 레코드와 하나의 컬럼값을 리턴해야 한다.

스칼라 서브쿼리의  성능 측면에서의 문제점 & LEFT OUTER JOIN으로 변환

쿼리는 FROM(과 JOIN) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 순으로 실행된다. 즉 특정 테이블에서 특정 조건에 맞는 레코들들을 모두 뽑아오고, 해당 레코드들에 대해 select절에서 특정 컬럼들을 뽑아내는 식으로 동작한다.  스칼라 서브쿼리는 건수만큼 반복해서 수행된다. 데이터가 많아질수록 성능 저하의 주범이 될 수 있는 것이다.

따라서, LEFT OUTER JOIN과 Inline View를 활용하는 식으로 쿼리를 바꿔 성능을 개선시킬 수 있다. 다음 쿼리를 살펴보자.

SELECT DEPT_NAME, (
    SELECT COUNT(*)
    FROM EMPLOYEES e
    WHERE e.DEPT_ID = d.DEPT_ID
) NUMS
FROM DEPARTMENTS d;

부서별로 부서명과 소속 인원들의 이름을 보는, 스칼라 서브쿼리를 쓰는 쿼리다. 이를 LEFT OUTER JOIN과 Inline View을 활용하는 형태로 다음과 같이 바꿔쓸 수 있다.

SELECT DEPT_NAME, c.NUMS
FROM DEPARTMENTS d
LEFT OUTER JOIN (
    SELECT e.DEPT_ID, COUNT(*) AS NUMS
    FROM EMPLOYEES e
    GROUP BY e.DEPT_ID
) c
ON d.DEPT_ID = c.DEPT_ID;

기존에는 해당 서브쿼리가 건수만큼 반복돼서 수행됐지만, 이렇게하면 한 번만 수행되고 끝난다. 

하지만 늘상 스칼라 서브쿼리를 LEFT OUTER JOIN으로 바꾼다고 성능 향상이 되는 건 아니다(그러면 스칼라 서브쿼리 아무도 안 쓰지..). 상황별로 스칼라 서브쿼리를 쓰는게 이득일 때도 있다. 다만 스칼라 서브쿼리를 쓸 때는 속도를 고려하는 습관을 가지자.

다음과 같은 테이블이 있다고 하자.

SALES_HISTORY 테이블은 특정 상품들과 해당 상품들이 판매된 시간, 판매된 가격 정보를 가진다.

1. GROUP BY를 통한 집계

GROUP BY를 통해 특정 컬럼(들)을 기준으로 그룹핑한 다음 집계함수를 활용한 합이나 평균값의 산출이 가능하다. 본 글에서는 SUM을 활용한 상품들의 일자별 매출액을 조회하는 상황을 가정한다.

SELECT
  PRODUCT_ID,
  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DAY,
  SUM(PRICE) AS TOTAL_SALES
FROM
  SALES_HISTORY
GROUP BY
  PRODUCT_ID, SALES_DAY;

2. WITH ROLLUP을 가미한 집계

WITH ROLLUP을 가미하면, GROUP BY로 보여지는 결과물 뿐만 아니라 그룹핑한 각각의 소그룹별 집계와 전체 집계를 볼 수 있다.

SELECT
  PRODUCT_ID,
  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DAY,
  SUM(PRICE) AS TOTAL_SALES
FROM
  SALES_HISTORY
GROUP BY
  PRODUCT_ID, SALES_DAY WITH ROLLUP;

NULL값으로 표기된 부분들이 WITH ROLLUP을 통해 산출된 집계값이다. 1000번 상품 그룹에 대한 집계와 1001번 상품에 대한 집계, 1002번 상품에 대한 집계와 더불어 전체 집계 또한 나온 것을 확인할 수 있다.