[DB] 데이터베이스 트랜잭션과 SQL 실행 과정

이미지 출처: Dall-E 3

SQL

SQL(Structured Query Language)

SQL은 관계형 데이터베이스인 RDBMS와 소통하기 위한 언어로, 데이터 처리와 관련된 문법들을 가집니다. C/C++, Java, Python과 같이 컴퓨터에 다양한 연산과 명령을 수행하기 위해 쓰이는 프로그래밍 언어와는 다른 목적을 가진다고 볼 수 있습니다.

SQL의 실행 과정

SQL문이 DBMS에 전달되면, DBMS에서는 SQL 실행을 위해 아래와 같은 과정들이 필요합니다.

1) 파싱 및 구문 분석
전달받은 SQL문을 파싱하여, 문법적 오류나 컬럼, 테이블 검사등 타입 검사를 진행합니다. 이 과정에서 SQL문은 트리 구조로 변환됩니다.

2) 옵티마이저를 통한 최적화
SQL문을 어떤 방식으로 실행하는 것이 효율적인지에 대한 최적화가 진행 됩니다.
이는 크게 인덱스 활용, 조인 방식 등과 관련된 논리적 최적화와 물리적 최적화가 이루어집니다.

3) 실행 계획 생성 및 캐싱
최적화를 통해 결정된 연산이나 접근 방식에 기반하여 DB엔진 내부에서 실행 계획을 생성합니다. 또한 반복적인 SQL문 실행이 발견 된다면, 파싱이나 최적화 과정을 재사용 할 수 있도록 캐싱 작업이 이루어지기도 합니다. MySQL은 쿼리 캐시가 기본적으로 비활성화된 경우가 많다고 하네요.

4) 실행
실행 과정은 크게 3단계로 이루어집니다.
실행 계획에 따라 연산을 수행하고,
파일 및 스토리지 시스템과 연동이 필요한 부분에 대하여 조회 및 변경 작업,
앞서 처리된 작업들이 일관성과 무결성을 지니는지에 따라 적절한 트랜잭쳔 처리(commit, rollback)을 수행합니다.

5) 결과 반환
최종적으로 반환된 결과를 SQL문을 전송한 클라이언트 측에 전송합니다.

SQL 문법

DML(Data Manipulation Language)

데이터를 조작하는데 사용되는 문법으로, CRUD 기반의 연산 작업을 수행합니다.
INSERT, SELECT, UPDATE, DELETE 등이 있습니다.

특히, SELECT절은 WHERE,HAVING,GROUP BY 등 흔히 여러 구문과 함께 사용이 됩니다. 이때, 어떤 연산이 먼저 이루어지는지에 대해 간략히 살펴보겠습니다.

FROM 및 JOIN: 우선 어떤 테이블,뷰,서브쿼리 등에서 데이터를 가져올지 결정
WHERE: 가져온 데이터들을 필터링
GROUP BY: 필터링된 데이터들을 기준으로 그룹핑
HAVING: 그룹화된 결과에 대해 새로운 조건으로 다시 필터링
SELECT: 최종적으로 남은 레코드들에 대하여, 어떤 컬럼을 추출할지 결정(실제 반환 데이터 결정)
ORDER BY: 정렬
LIMIT/OFFSET: 최종 반환할 레코드를 별도로 재선정(페이징 등에 자주 사용됨)

SELECT DISTINCT
       a.customer_id,
       MONTH(t.transaction_date) AS transaction_month,
       AVG(t.transaction_amount) AS avg_amount,
       SUM(t.transaction_amount) AS total_amount
FROM accounts a
     INNER JOIN transactions t 
             ON a.account_id = t.account_id  -- (1) FROM (조인)
WHERE t.transaction_date BETWEEN '2024-01-01' AND '2024-12-31' -- (2) WHERE (행 필터링)
GROUP BY a.customer_id, MONTH(t.transaction_date)              -- (3) GROUP BY (그룹화)
HAVING SUM(t.transaction_amount) > 1000                        -- (4) HAVING (그룹 필터링)
ORDER BY avg_amount DESC                                       -- (5) ORDER BY (정렬)
LIMIT 10;  

DDL(Data Definition Language)

데이터베이스의 구조를 정의할 때 사용되는 문법입니다. 테이블을 생성하거나, 컬럼 추가, 뷰 생성 등 구조를 이루는 오브젝트 자체를 다룹니다.
CREATE, ALTER, DROP, TRUNCATE 등이 있습니다.

DCL(Data Control Langauage)

데이터베이스의 권한 및 보안 등을 관리하기 위한 문법입니다.
GRANT(권한 부여), REVOKE(권한 회수) 등이 있습니다.

Join

Join이란 두 개 이상의 테이블을 연결하여 데이터를 처리할 때 사용하는 개념입니다. 여러 테이블에 분산되어 있는 데이터들을 일정 조건에 따라 연결시켜, 한번에 탐색해와야 할 때 유용합니다.

Join의 종류

이미지 출처: C.L. Moffatt, 2008

테이블을 어떤 조건으로 연결시키느냐에 따라, join의 종류가 달라집니다.

Inner Join

Join하려는 모든 테이블에 존재하는 데이터만 반환합니다.

Outer Join

Join하려는 테이블 중에 어떤 테이블을 중점으로 볼건지에 따라 종류가 나뉩니다.
왼쪽/오른쪽 테이블 개념이 나오는데, 왼쪽 테이블은 먼저 제시된 테이블 오른쪽 테이블은 그 다음으로 나온 테이블을 의미합니다.

• Left Outer Join
  왼쪽 테이블의 모든 데이터는 반환되고, 오른쪽 테이블에는 교집합 데이터만 반환됩니다. 따라서, 왼쪽 테이블의 일부 데이터들은 교집합 데이터가 없을 수도 있는데 이러한 경우 왼쪽 테이블의 데이터와 NULL 값으로 구성됩니다.

SELECT STUDENT.name, STUDENT.email, CLASS.name 
FROM STUDENT
LEFT JOIN CLASS ON CLASS.student_id = STUDENT.id;

STUDENT 테이블의 모든 데이터는 반환되고, CLASS에는 겹치는 데이터만 반환됩니다.

• Right Outer Join
  오른쪽 테이블의 모든 데이터는 반환되고, 왼쪽 테이블에는 일치하는 데이터만
  반환됩니다. 만약 왼쪽 테이블에 일치하는 데이터가 없는 경우, 오른쪽 테이블의 데이터와 NULL 값으로 구성됩니다.

SELECT Student.name, Student.email, Class.name
FROM Student
RIGHT JOIN Class ON Class.student_id = Student.id;

• Full Outer Join

두 테이블의 모든 레코드를 반환하며, mysql에서는 union을 통해 구현됩니다.

SELECT Student.name, Student.email, Class.name 
FROM Student
LEFT JOIN Class ON CLASS.student_id = Student.id

UNION

SELECT Student.name, Student.email, Class.name
FROM Student
RIGHT JOIN Class ON Class.student_id = Student.id;

일반적으로는 Outer Join보다는 Inner Join이 처리하는 데이터양이 더 적기 때문에, 속도도 더 빠릅다고 간주됩니다. 하지만, 실제 성능은 다른 요인(쿼리, 데이터 베이스 및 인덱스 구조 등)에 따라 달라질 수 있습니다.

Cross Join

Cross Join(교차 조인)은 연결 하려는 각 테이블의 모든 행의 조합을 생성합니다. 예를 들어, 교과목의 종류를 나타내는 Class 테이블과 분반 수준을 나누는 Level테이블이 있다고 합시다.

SELECT Class.name, Level.level
FROM Class
Cross Join Level;

만약 Class 테이블에 운영체제, 데이터베이스, 네트워크가 있고,
Level에 상,중,하가 있다면,
위의 쿼리 결과는 9개의 데이터가 반환될 것 입니다.(3*3)

이처럼 교차조인은 테이블 간의 조인 조건이 별도로 없고, 각 테이블의 모든 행으로부터 카테시안 곱을 형성할 때 쓰일 수 있습니다. 다만, 데이터의 양이 N*N이므로 성능은 좋지 않은 편입니다.

서브 쿼리

메인 쿼리안에서 필요한 데이터들을 별도로 가져오기 위해, 내부적으로 SQL문 안에 중첩으로 사용되는 SQL 쿼리입니다. 서브쿼리 독립적으로 실행되기 때문에 성능면에서 비효율적일 수도 있습니다. 서브 쿼리 대신 JOIN 등을 활용하는 방식으로 대체하여 성능을 개선할 수도 있지만, 이 또한 상황에 따라 다릅니다. 다만, JOIN으로 변경하면 좋은것이 JOIN한 데이터 결과는 메모리에 올라가기 때문에 추가 연산 등을 할 때 계속 재사용 할 수 있다는 장점은 있습니다.

DISTINCT

DISTINCT는 중복을 제거한 결과를 반환하고 싶을때 사용하며, SELECT 구문과 함께 사용됩니다. 기본적으로 SELECT절에 포함되는 모든 컬럼을 기준으로 중복된 레코드를 제거하지만, 비교하고 싶은 특정 컬럼만을 명시할 수도 있습니다.

예를 들어, 유저들의 구독 정보를 저장하는 테이블이 있고, 유저들은 여러개의 플랜을 동시에 구독할 수 있습니다. 이때, 어떤 플랜이든 상관 없이,
현재 구독하고 있는 유저 수를 구해야 한다면, 이때 COUNT안에서 user에 대한 distinct를 걸어줄 수 있습니다. 그렇지 않으면, a라는 유저가 2개의 플랜을 구독중이라면 구독자수에 +1이 아닌, +2로 계산이 되기 때문입니다.(이건 제가 실제 사용했던 사례입니다😁)

추가 필수 개념

트랜잭션

데이터베이스에서 한번에 이루어져야 하는 논리적인 작업 단위입니다. 즉, 하나의 트랜잭션으로 묶인 작업들은(여러개의 쿼리겠죠?) 모두 수행되던지 모두 수행되지 않던지 해야하는 원자성을 지닙니다.
이를 위해 commit과 rollback이라는 개념이 사용됩니다.
commit은 하나의 트랜잭션이 성공적으로 완료되었음을 의미하며, 트랜잭션에 의해 변경된 내용이 영구적으로 적용됩니다. 즉, commit은 트랜잭션 단위로 발생합니다.
만약, 일부 작업이 실패했거나 어떤 이슈로 인해 트랜잭션 전으로 돌려야 한다면 어떻게 할까요? 이때 필요한 것이 rollback입니다. 지금까지 부분적으로 수행된 트랜잭션 내의 일부 작업을 다시 취소시키는 것 입니다.
이러한 특성때문에, rollback될 수 있는 하나의 트랜잭션 안에서는 외부 api 호출을 자제해야 합니다.(외부 api호출에 대한 롤백은 번거롭기 때문)

트랜잭션은 commit/rollback을 통해서, 데이터베이스 내의 무결성을 유지할 수 있습니다.

트랜잭션 전파

트랜잭션이 실행 되고 있을때, 다른 트랜잭션이 발생한다면 어떻게 처리할지에 대한 문제입니다. 기존의 트랜잭션을 그대로 이어 받을지, 별도의 트랜잭션으로 분리해야하는지 등에 대해 결정해두는 것을 트랜잭션 전파라고 합니다.

예를 들어, 아래와 같이 주문 정보를 저장 + 결제 처리하는 트랜잭션이 있다고 합시다. 이때, 결제 처리는 또 다른 트랜잭션으로 구분됩니다.

@Transactional // REQUIRED가 기본 설정
public void placeOrder(Order order) {
    orderRepository.save(order); // 주문 저장
    paymentService.processPayment(order); // 결제 처리
}

@Transactional
public void processPayment(Order order) {
    paymentRepository.save(order.getPayment()); // 결제 데이터 저장
}

이때, 주문 저장까지는 잘 되었는데 결제 처리에서 에러가 발생하면 어떻게 되어야 할까요? 주문 저장까지 롤백이 되어야 할까요? 아니면, 주문은 저장되고 결제 처리만 다시 이루어져야 할까요. 이런 부분을 어떻게 전파 시킬지에 대한 내용이 트랜잭션 전파입니다. Spring Boot에서는 @Transaction에 사용되는 속성들로 이러한 transaction context를 관리할 수 있습니다.

위의 사례에서, processPayment는 별도의 트랜잭션이지만, 이를 호출한 placeOrder 트랜잭션에 묶여서 함께 롤백되거나 함께 커밋되어야 한다면 REQUIRED 속성으로 전파해주면 됩니다. 만약, 하위 메서드는 별도의 트랜잭션으로 관리하고 싶다고 하면 REQUIRES_NEW를 설정해주면 됩니다.

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void sendOrderNotification(Order order) {
    notificationRepository.save(order.getNotification()); // 알림 정보 저장
    // 실제 알림 전송 로직
}

REQUIRES_NEW는 기존 트랜잭션과 구분되는 별도의 물리 커넥션을 생성하여, 트랜잭션을 분리시켜버립니다. 이때, 하나의 요청 안에 2개의 커넥션을 통해 DB와 통신하므로 성능면에서는 상대적으로 더 부담이 큰 방식이 됩니다.

Lock

트랜잭션이 수행되는 동안 특정 행들을 잠금(Lock)처리 하여, 다른 트랜잭션에서 해당 행을 수정하지 못하도록 하는 경우가 있습니다. 금융 서비스와 같이 동시성 제어가 예민한 비즈니스 로직에서 많이 쓰입니다. 애플리케이션단에서 이를 처리할 수도 있지만, DB단에서 잠금을 처리하는 것이 더 안전하다고 알고 있습니다.
예를 들어, SELECT ~ FOR UPDATE 문이 데이터베이스단의 Lock으로 사용됩니다.

BEGIN;

-- (1) 특정 계좌 정보를 읽기, FOR UPDATE로 잠금을 획득
SELECT account_id,
       balance
FROM bank_accounts
WHERE account_id = 123456
FOR UPDATE;

-- (2) 필요한 로직 수행 (예: balance를 검증한 후, 조건 충족 시 잔액 업데이트)
UPDATE bank_accounts
   SET balance = balance - 100.00
 WHERE account_id = 123456;

-- (3) 트랜잭션 종료 (COMMIT하면 변경 사항이 반영되고, 잠금 해제)
COMMIT;

저의 경우, 관리자가 설문지를 생성하는 api를 개발할 때 사용해본 경험이 있습니다. 기존 설문지 양식을 수정하거나 새로운 설문지 양식을 생성할 때마다 가장 마지막 버전 +1한 값을 수정 및 생성된 설문의 버전으로 할당해줬어야 했습니다. 이때, 동시에 여러 요청이 오게 되면, 각 트랜잭션마다 마지막 버전으로 같은 값을 읽게 되고 이에 따라 동일한 버전의 설문 레코드가 여러개 생성될 수 있기 때문에 이때 DB단의 lock을 사용했던 사례가 있었습니다.

SQL Injection

SQL 쿼리문에 사용자가 입력한 값을 적절히 검증하지 않아, 데이터베이스에 보안상 공격이 생길 수 있는 문제입니다. 대표적인 예로, 아래와 같은 공격은 password 대신 조작된 값을 넣어 비밀번호 없이 데이터에 접근할 수 있습니다.

SELECT user FROM user_table WHERE id='admin' AND password=' ' OR '1' = '1';

SQL 안티패턴

성능, 유지보수, 설계 등의 측면에서 지양되는 SQL 사용패턴입니다. 예를 들어,

• SELECT문에서 특정 필요한 컬럼을 명시하는 대신 단순히 '*'로 전체 컬럼을 모두 반환
• 파라미터를 바인딩 하지 않고, 하드코딩 된 값을 넣는 것
• 외래키 제약을 설정하지 않는것(무결성 및 일관성 문제와 이어질 수 있음)
  등등이 안티 패턴의 예시라고 볼 수 있습니다.

Reference
[스프링 DB] 트랜잭션 전파(propagation)
[인프런 강의] CS 지식의 정석 | 디자인패턴 네트워크 운영체제 데이터베이스 자료구조