PDO 심화 - 트랜잭션과 리포지토리

이 장에서 배우는 것

기본서에서 PDO 로 질의를 보내고 결과를 읽는 방법을 익혔다. 이 장에서는 그 위에 실무 코드가 지켜야 할 규칙을 얹는다. 오류를 놓치지 않고 받는 방법, 값을 안전하게 묶는 방법, 여러 쓰기를 하나로 묶는 방법, SQL 을 한곳에 모으는 방법, 그리고 질의 횟수가 데이터 양에 따라 불어나는 문제를 막는 방법이다. 예제는 학원의 강좌와 수강 신청 테이블을 SQLite 메모리 데이터베이스에 만들어 다룬다.

• ERRMODE_EXCEPTION 으로 데이터베이스 오류를 예외로 받아 처리한다.
• 준비된 문장(prepared statement)에 바인딩 타입을 지정해 값을 묶고, 묶을 수 없는 자리를 구분한다.
• 트랜잭션(transaction)으로 여러 쓰기를 전부 반영하거나 전부 취소한다.
• 리포지토리(repository)로 SQL 을 한 클래스에 모으고, 트랜잭션 경계는 서비스에 둔다.
• N+1 질의를 알아보고 조인과 IN 절로 질의 횟수를 줄인다.

문제 상황

수강 신청 화면에서 친구 세 명을 한 번에 신청하는 기능을 만든다고 하자. 코드는 학생마다 INSERT 를 한 번씩 보낸다. 정원이 두 자리만 남은 강좌라면 앞의 두 명은 들어가고 세 번째에서 실패한다. 사용자는 오류 화면을 보고 다시 시도하는데, 데이터베이스에는 이미 두 명이 들어 있어서 재시도가 중복 신청 오류로 또 실패한다. 한 번의 요청이 반쪽만 반영된 상태가 남은 것이다.

문제는 이것만이 아니다. 컨트롤러 곳곳에 SQL 문자열이 흩어져 있어서 테이블 이름 하나를 바꾸려면 파일 열 군데를 고쳐야 한다. 강좌 목록 화면은 강좌마다 수강생 수를 세는 질의를 따로 보내서, 강좌가 100개이면 질의가 101번 나간다. 개발 환경에서는 데이터가 몇 건 안 되어 눈치채지 못하다가 운영에서 화면이 느려진다.

이 세 가지, 즉 반쪽 반영, 흩어진 SQL, 질의 폭증을 차례로 해결한다. 데이터베이스는 SQLite 메모리 DB 이므로 앞 장에서 다룬 테스트에서도 같은 방식으로 쓸 수 있다.

오류 모드와 준비된 문장

오류를 예외로 받는다

PDO 는 질의가 실패했을 때 어떻게 알릴지 오류 모드로 정한다. 모드마다 호출하는 쪽이 해야 할 일이 다르다.

PDO 오류 모드별 실패 시 동작

모드	실패했을 때	호출부가 할 일
ERRMODE_SILENT	false 를 돌려주고 조용히 넘어간다	매 호출마다 반환값과 errorInfo 를 확인한다
ERRMODE_WARNING	경고를 내고 계속 진행한다	경고 처리기를 따로 둔다
ERRMODE_EXCEPTION	PDOException 을 던진다	try/catch 로 처리하거나 위로 올린다

PHP 8 부터 기본값이 ERRMODE_EXCEPTION 이지만, 연결을 만드는 곳에서 옵션으로 명시해 두는 편이 낫다. 설정이 코드에 보이면 다른 사람이 동작을 짐작하지 않아도 된다. 예외 모드에서는 반환값을 일일이 검사하지 않아도 되고, 실패가 트랜잭션의 catch 블록으로 곧바로 흘러간다. 이 점이 뒤에 나올 롤백의 전제다.

PDOException 의 getCode() 는 SQLSTATE 문자열을 돌려준다. 유일성 제약이나 외래 키 위반 같은 무결성 오류는 23000 으로 시작하는 계열이다. 오류 메시지는 드라이버마다 다르므로 분기에 쓰지 않고 로그에만 남긴다.

값은 바인딩으로 묶는다

준비된 문장은 SQL 의 구조를 먼저 데이터베이스에 보내고, 값은 나중에 따로 묶어 보낸다. 값이 SQL 구문으로 해석될 일이 없으므로 따옴표를 이스케이프하는 코드가 필요 없다. 값을 묶을 때는 bindValue() 에 타입 상수를 함께 준다.

자주 쓰는 바인딩 타입 상수

상수	쓰는 값	예
PDO::PARAM_INT	정수	id, 정원, 개수
PDO::PARAM_STR	문자열	강좌 이름, 학생 이름
PDO::PARAM_BOOL	참/거짓	개설 여부 플래그
PDO::PARAM_NULL	NULL	비어 있는 선택 항목

execute() 에 배열을 넘기면 모든 값이 문자열로 묶인다. SQLite 는 열의 타입에 맞춰 변환해 주므로 대개 동작하지만, 다른 데이터베이스에서는 LIMIT 값처럼 정수여야 하는 자리에서 오류가 난다. 타입을 코드에 적어 두면 데이터베이스를 바꿔도 같은 의도가 유지된다.

바인딩에는 한계가 있다. 자리표시자는 값이 들어갈 자리에만 쓸 수 있고, 테이블 이름, 열 이름, 정렬 방향은 묶을 수 없다. 이런 자리에 사용자 입력을 넣어야 한다면 허용할 값의 목록을 코드에 두고 그 안에 있는지 확인한다. 이 내용은 뒤의 틀리기 쉬운 사례에서 다시 다룬다.

조회 결과의 타입도 확인해 둘 점이다. SQLite 드라이버는 정수 열을 PHP 정수로 돌려주지만, 드라이버와 설정에 따라 문자열로 오기도 한다. 그래서 리포지토리가 객체를 만들 때 (int) 같은 형 변환을 거치게 한다. 앞에서 엄격 모드를 쓴 이유가 여기서 드러난다. 문자열 "3" 이 생성자의 int 매개변수로 그대로 들어가면 TypeError 가 나기 때문이다.

트랜잭션과 리포지토리

트랜잭션은 전부 아니면 전무다

트랜잭션은 여러 쓰기를 하나의 작업 단위로 묶는다. beginTransaction() 으로 시작하고, 끝까지 성공하면 commit() 으로 확정하며, 중간에 실패하면 rollBack() 으로 시작 시점의 상태로 되돌린다(롤백, rollback). 문제 상황의 세 명 신청은 이 구조에 그대로 들어맞는다.

트랜잭션 코드의 뼈대는 항상 같다. 시작하고, try 안에서 작업하고, 성공하면 commit 하고, catch 에서 rollBack 한 뒤 예외를 다시 던진다. 예외를 다시 던지지 않으면 호출한 쪽은 신청이 성공한 줄 안다. 롤백 전에는 inTransaction() 으로 열린 트랜잭션이 있는지 확인한다. commit 자체가 실패한 경우처럼 이미 트랜잭션이 닫혀 있을 때 rollBack() 을 호출하면 또 다른 예외가 나기 때문이다.

이 장의 코드는 정원 확인을 위해 현재 인원을 세고 나서 INSERT 한다. 한 프로세스에서 차례로 실행하면 문제가 없지만, 운영 환경에서 두 요청이 동시에 같은 자리를 확인하면 정원을 넘길 수 있다. 이를 막으려면 데이터베이스의 행 잠금이나 쓰기 잠금, 또는 제약 조건이 필요하다. 사용하는 데이터베이스의 문서에서 잠금 구문을 확인하자. 이 장은 트랜잭션의 원자성에 집중한다.

리포지토리는 SQL 의 집이다

리포지토리(repository)는 한 종류의 데이터를 읽고 쓰는 SQL 을 한 클래스에 모은 것이다. 호출하는 쪽은 find(), add() 같은 도메인의 언어로 말하고, 테이블과 열의 이름은 리포지토리 안에만 남는다. 조회 결과는 배열 대신 Course 같은 객체로 돌려준다. 앞 장에서 만든 값 객체와 테스트 방식이 그대로 이어진다.

트랜잭션의 경계는 리포지토리가 아니라 그 위의 서비스에 둔다. 한 번의 신청이 강좌 조회, 인원 확인, 신청 기록이라는 여러 리포지토리 호출로 이루어지기 때문이다. 리포지토리 메서드 안에서 beginTransaction() 을 호출하면, 서비스가 여러 메서드를 묶으려 할 때 이미 열린 트랜잭션과 충돌한다.

N+1 질의 피하기

목록 하나를 가져오는 질의 1번과, 목록의 항목마다 보내는 질의 N번을 합쳐 N+1 질의라고 부른다. 항목 수에 비례해 질의가 늘고, 질의마다 준비와 실행 비용이 든다. 코드만 읽으면 눈에 띄지 않는다. 반복문 안에서 리포지토리 메서드를 부르는 모양이 자연스럽기 때문이다.

해법은 두 가지다. 집계 값처럼 한 행에 붙는 정보는 LEFT JOIN 과 GROUP BY 로 한 번에 가져온다. 강좌에 수강생이 없어도 목록에 남아야 하므로 INNER 가 아니라 LEFT JOIN 을 쓴다. 수강생 이름처럼 여러 행으로 이루어진 정보는 강좌 번호들을 IN 절에 넣어 한 번에 가져오고, PHP 에서 강좌별로 나눈다. 이 경우 강좌가 몇 개든 질의는 2번이다.

질의 횟수를 눈으로 확인하려고 이 장의 코드는 PDO 를 상속한 작은 클래스로 prepare() 호출을 센다. 실무에서는 데이터베이스의 질의 로그나 프레임워크의 프로파일러를 쓰지만, 원리는 같다. 횟수를 숫자로 보면 개선 효과가 말이 아니라 값으로 남는다.

완성 코드

파일 하나로 구성했다. 연결, 도메인 객체, 리포지토리, 서비스, 실행부가 위에서 아래로 놓여 있다.

main.php

<?php
declare(strict_types=1);

final class CourseFullException extends RuntimeException
{
}

final class CountingPDO extends PDO
{
    public int $prepared = 0;

    public function prepare(string $query, array $options = []): PDOStatement|false
    {
        $this->prepared++;
        return parent::prepare($query, $options);
    }
}

final readonly class Course
{
    public function __construct(
        public int $id,
        public string $title,
        public int $capacity,
        public int $enrolled = 0,
    ) {
    }
}

function connect(): CountingPDO
{
    $pdo = new CountingPDO('sqlite::memory:', null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    $pdo->exec('PRAGMA foreign_keys = ON');
    $pdo->exec('CREATE TABLE courses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        capacity INTEGER NOT NULL CHECK (capacity > 0)
    )');
    $pdo->exec('CREATE TABLE enrollments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_id INTEGER NOT NULL REFERENCES courses(id),
        student TEXT NOT NULL,
        UNIQUE (course_id, student)
    )');
    return $pdo;
}

final class CourseRepository
{
    public function __construct(private PDO $pdo)
    {
    }

    public function add(string $title, int $capacity): int
    {
        $stmt = $this->pdo->prepare('INSERT INTO courses (title, capacity) VALUES (:title, :capacity)');
        $stmt->bindValue(':title', $title, PDO::PARAM_STR);
        $stmt->bindValue(':capacity', $capacity, PDO::PARAM_INT);
        $stmt->execute();
        return (int) $this->pdo->lastInsertId();
    }

    public function find(int $id): ?Course
    {
        $stmt = $this->pdo->prepare('SELECT id, title, capacity FROM courses WHERE id = :id');
        $stmt->bindValue(':id', $id, PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch();
        return $row === false ? null : $this->hydrate($row);
    }

    public function findByTitle(string $title): ?Course
    {
        $stmt = $this->pdo->prepare('SELECT id, title, capacity FROM courses WHERE title = :title');
        $stmt->bindValue(':title', $title, PDO::PARAM_STR);
        $stmt->execute();
        $row = $stmt->fetch();
        return $row === false ? null : $this->hydrate($row);
    }

    public function countEnrolled(int $courseId): int
    {
        $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM enrollments WHERE course_id = :id');
        $stmt->bindValue(':id', $courseId, PDO::PARAM_INT);
        $stmt->execute();
        return (int) $stmt->fetchColumn();
    }

    public function addEnrollment(int $courseId, string $student): void
    {
        $stmt = $this->pdo->prepare('INSERT INTO enrollments (course_id, student) VALUES (:course, :student)');
        $stmt->bindValue(':course', $courseId, PDO::PARAM_INT);
        $stmt->bindValue(':student', $student, PDO::PARAM_STR);
        $stmt->execute();
    }

    /** @return list<Course> 강좌마다 질의를 한 번씩 더 보낸다 */
    public function listWithCountsNaive(): array
    {
        $stmt = $this->pdo->prepare('SELECT id, title, capacity FROM courses ORDER BY id');
        $stmt->execute();
        $list = [];
        foreach ($stmt->fetchAll() as $row) {
            $list[] = $this->hydrate($row, $this->countEnrolled((int) $row['id']));
        }
        return $list;
    }

    /** @return list<Course> 질의 한 번으로 인원까지 가져온다 */
    public function listWithCounts(): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT c.id, c.title, c.capacity, COUNT(e.id) AS enrolled
             FROM courses c
             LEFT JOIN enrollments e ON e.course_id = c.id
             GROUP BY c.id, c.title, c.capacity
             ORDER BY c.id'
        );
        $stmt->execute();
        $list = [];
        foreach ($stmt->fetchAll() as $row) {
            $list[] = $this->hydrate($row, (int) $row['enrolled']);
        }
        return $list;
    }

    /**
     * @param list<int> $courseIds
     * @return array<int, list<string>>
     */
    public function studentsByCourse(array $courseIds): array
    {
        if ($courseIds === []) {
            return [];
        }
        $marks = implode(',', array_fill(0, count($courseIds), '?'));
        $stmt = $this->pdo->prepare(
            "SELECT course_id, student FROM enrollments
             WHERE course_id IN ($marks) ORDER BY course_id, student"
        );
        foreach (array_values($courseIds) as $i => $id) {
            $stmt->bindValue($i + 1, $id, PDO::PARAM_INT);
        }
        $stmt->execute();
        $result = [];
        foreach ($stmt->fetchAll() as $row) {
            $result[(int) $row['course_id']][] = (string) $row['student'];
        }
        return $result;
    }

    private function hydrate(array $row, int $enrolled = 0): Course
    {
        return new Course((int) $row['id'], (string) $row['title'], (int) $row['capacity'], $enrolled);
    }
}

final class EnrollmentService
{
    public function __construct(private PDO $pdo, private CourseRepository $courses)
    {
    }

    /** @param list<string> $students */
    public function enrollGroup(int $courseId, array $students): void
    {
        $course = $this->courses->find($courseId)
            ?? throw new InvalidArgumentException('없는 강좌: ' . $courseId);

        $this->pdo->beginTransaction();
        try {
            foreach ($students as $student) {
                if ($this->courses->countEnrolled($courseId) >= $course->capacity) {
                    throw new CourseFullException(
                        sprintf('정원 %d명이 찼다: %s', $course->capacity, $student)
                    );
                }
                $this->courses->addEnrollment($courseId, $student);
            }
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }
}

$pdo = connect();
$courses = new CourseRepository($pdo);
$service = new EnrollmentService($pdo, $courses);

$python = $courses->add('파이썬 기초', 2);
$web = $courses->add('웹 입문', 3);
$courses->add('데이터 읽기', 3);

echo '[1] 오류는 예외로 온다', PHP_EOL;
try {
    $pdo->prepare('SELECT * FROM no_such_table');
} catch (PDOException $e) {
    echo '준비 단계 실패: ', $e::class, PHP_EOL;
}

$service->enrollGroup($python, ['가은']);
try {
    $service->enrollGroup($python, ['가은']);
} catch (PDOException $e) {
    echo '중복 신청: SQLSTATE ', $e->getCode(), PHP_EOL;
}
echo '파이썬 기초 수강생 수: ', $courses->countEnrolled($python), PHP_EOL;
$hit = $courses->findByTitle("' OR '1'='1");
echo '악의적 제목 검색: ', $hit === null ? '결과 없음' : '노출됨', PHP_EOL;

echo '[2] 트랜잭션', PHP_EOL;
try {
    $service->enrollGroup($web, ['나래', '다온', '라온', '마루']);
} catch (CourseFullException $e) {
    echo $e->getMessage(), PHP_EOL;
}
echo '롤백 후 웹 입문 수강생 수: ', $courses->countEnrolled($web), PHP_EOL;
$service->enrollGroup($web, ['나래', '다온']);
echo '재신청 후 웹 입문 수강생 수: ', $courses->countEnrolled($web), PHP_EOL;

echo '[3] N+1', PHP_EOL;
$pdo->prepared = 0;
$naive = $courses->listWithCountsNaive();
echo 'N+1 방식: ', $pdo->prepared, '회 준비', PHP_EOL;

$pdo->prepared = 0;
$joined = $courses->listWithCounts();
echo '조인 방식: ', $pdo->prepared, '회 준비', PHP_EOL;

$pdo->prepared = 0;
$names = $courses->studentsByCourse(array_map(fn (Course $c): int => $c->id, $joined));
echo 'IN 방식: ', $pdo->prepared, '회 준비', PHP_EOL;
echo '결과 동일: ', $naive == $joined ? '예' : '아니오', PHP_EOL;

foreach ($joined as $c) {
    $list = implode(', ', $names[$c->id] ?? []);
    printf('%s (%d/%d): %s' . PHP_EOL, $c->title, $c->enrolled, $c->capacity, $list === '' ? '-' : $list);
}

줄별 해설

연결과 계수기

CountingPDO 는 PDO 를 상속해 prepare() 가 불릴 때마다 prepared 를 1 올린다. 부모의 prepare() 를 그대로 호출하므로 동작은 같다. connect() 는 옵션 배열에서 오류 모드를 ERRMODE_EXCEPTION 으로, 기본 가져오기 방식을 연관 배열로 정한다. SQLite 는 외래 키 검사가 기본으로 꺼져 있어서 PRAGMA 로 켠다. 테이블 정의의 CHECK 는 정원이 0 이하인 강좌를, UNIQUE 는 같은 학생의 중복 신청을 데이터베이스 수준에서 막는다. DDL 은 값이 없으므로 exec() 로 보내고, 그래서 계수기에도 잡히지 않는다.

Course 와 리포지토리

Course 는 readonly 클래스라 만들어진 뒤 바뀌지 않는다. 리포지토리의 모든 질의는 이름 있는 자리표시자와 bindValue() 로 값을 묶고, 정수에는 PARAM_INT 를, 문자열에는 PARAM_STR 를 준다. add() 는 lastInsertId() 가 문자열을 돌려주므로 int 로 바꿔서 돌려준다. find() 와 findByTitle() 은 행이 없으면 fetch() 가 false 를 돌려주는 점을 null 로 바꾸어 호출부가 ?Course 로 다루게 한다. hydrate() 는 행을 Course 로 바꾸는 유일한 자리이고, 여기서 형 변환을 모아 처리한다.

세 가지 목록 메서드

listWithCountsNaive() 는 목록을 fetchAll() 로 모두 읽은 다음에 반복하면서 countEnrolled() 를 부른다. 먼저 다 읽어 두는 이유는, 결과를 읽는 도중에 같은 연결로 다른 질의를 보내면 드라이버에 따라 충돌할 수 있기 때문이다. listWithCounts() 는 LEFT JOIN 과 GROUP BY 로 같은 정보를 질의 한 번에 만든다. studentsByCourse() 는 번호 개수만큼 물음표를 만들어 IN 절을 조립한다. 조립에 쓰이는 것은 물음표뿐이고, 실제 값은 위치 번호 1부터 bindValue() 로 묶는다.

서비스의 트랜잭션

enrollGroup() 은 트랜잭션을 열기 전에 강좌를 찾는다. 없으면 InvalidArgumentException 을 던지는데, 이 시점에는 열린 트랜잭션이 없으므로 롤백이 필요 없다. 트랜잭션 안에서는 학생마다 인원을 확인하고 정원에 닿았으면 CourseFullException 을, 아니면 신청을 기록한다. 끝까지 성공하면 commit() 한다. catch 는 Throwable 전체를 받아서 롤백한 뒤 같은 예외를 다시 던진다. 도메인 예외든 PDOException 이든 같은 경로로 롤백된다.

실행부

[1] 은 존재하지 않는 테이블로 prepare() 를 호출해 예외가 오는 것을 보이고, 같은 학생을 두 번 신청해 유일성 위반의 SQLSTATE 를 확인한다. 중복 시도는 트랜잭션 안에서 실패하므로 롤백되고 인원은 1명으로 남는다. 따옴표가 섞인 제목으로 검색해도 구문이 바뀌지 않고 값으로만 취급되어 결과가 없다. [2] 는 정원 3명인 강좌에 4명을 신청해 앞의 3명이 기록된 뒤 마지막에서 실패하게 하고, 롤백으로 0명이 된 것을 보인다. [3] 은 계수기를 0으로 되돌려 가며 세 방식의 준비 횟수를 비교한다. 강좌가 3개이므로 N+1 방식은 1+3 인 4회다.

실행 결과

$ php main.php
[1] 오류는 예외로 온다
준비 단계 실패: PDOException
중복 신청: SQLSTATE 23000
파이썬 기초 수강생 수: 1
악의적 제목 검색: 결과 없음
[2] 트랜잭션
정원 3명이 찼다: 마루
롤백 후 웹 입문 수강생 수: 0
재신청 후 웹 입문 수강생 수: 2
[3] N+1
N+1 방식: 4회 준비
조인 방식: 1회 준비
IN 방식: 1회 준비
결과 동일: 예
파이썬 기초 (1/2): 가은
웹 입문 (2/3): 나래, 다온
데이터 읽기 (0/3): -

실무에서 자주 틀리는 것

값을 SQL 문자열에 이어 붙인다

틀린 코드는 입력이 따옴표를 포함하면 SQL 구조가 바뀐다.

$title = $_GET['title'];
$rows = $pdo->query("SELECT * FROM courses WHERE title = '$title'");

고친 코드는 구조와 값을 분리한다.

$stmt = $pdo->prepare('SELECT * FROM courses WHERE title = :title');
$stmt->bindValue(':title', $title, PDO::PARAM_STR);
$stmt->execute();

예외를 삼키고 트랜잭션을 연 채로 둔다

틀린 코드는 오류를 출력만 하고 롤백도 재던지기도 하지 않는다. 연결에는 열린 트랜잭션이 남아서 다음 beginTransaction() 이 실패하고, 호출한 쪽은 성공한 것으로 안다.

$pdo->beginTransaction();
try {
    $repo->addEnrollment(1, '가은');
    $repo->addEnrollment(1, '나래');
    $pdo->commit();
} catch (Exception $e) {
    echo '실패';
}

고친 코드는 롤백하고 예외를 다시 던진다. 처리할 수 없는 오류는 위로 올리는 것이 원칙이다.

} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

반복문 안에서 질의한다

틀린 코드는 강좌 수만큼 질의가 늘어난다.

foreach ($courses->listAll() as $course) {
    $count = $courses->countEnrolled($course->id);
}

고친 코드는 목록 질의에 집계를 합친다. 이 장의 listWithCounts() 가 그 예다. 반복문 안에서 리포지토리 메서드를 부르고 있다면 질의 횟수를 한 번 세어 보자.

정렬 열을 바인딩하려 한다

틀린 코드는 열 이름을 값처럼 묶는다. 이렇게 하면 열이 아니라 문자열 상수로 정렬되어 순서가 바뀌지 않는다.

$stmt = $pdo->prepare('SELECT * FROM courses ORDER BY :col');
$stmt->bindValue(':col', $_GET['sort'], PDO::PARAM_STR);

고친 코드는 허용 목록에서 골라 SQL 에 넣는다. 목록에 없으면 기본값을 쓴다.

$allowed = ['id', 'title', 'capacity'];
$col = in_array($_GET['sort'] ?? '', $allowed, true) ? $_GET['sort'] : 'id';
$stmt = $pdo->prepare("SELECT * FROM courses ORDER BY $col");

한눈에 보기

이 장의 도구와 쓰임새

주제	도구	해결하는 문제
오류 처리	ERRMODE_EXCEPTION	실패를 놓치지 않고 catch 로 모은다
값 전달	bindValue 와 PARAM 상수	SQL 주입을 막고 타입 의도를 남긴다
여러 쓰기	beginTransaction, commit, rollBack	반쪽만 반영된 상태를 없앤다
SQL 정리	리포지토리	질의를 한곳에 모으고 객체로 돌려준다
질의 폭증	JOIN 과 IN 절	질의 횟수를 항목 수와 무관하게 유지한다

트랜잭션 코드의 단계별 규칙

단계	할 일	빠뜨리면
시작	작업 직전에 beginTransaction	쓰기가 각자 확정된다
성공	끝에서 commit	변경이 반영되지 않는다
실패	inTransaction 확인 후 rollBack	트랜잭션이 열린 채 남는다
전달	예외를 다시 던진다	호출부가 실패를 모른다

연습 문제

1. CourseRepository 에 수강 취소 메서드 cancel(int $courseId, string $student): bool 을 추가하자. 실제로 지워졌으면 true 를 돌려준다.
2. enrollGroup() 에서 try/catch 를 모두 지우고 예외가 나면 어떤 일이 생기는지, 같은 연결로 다음 enrollGroup() 을 호출하면 무엇이 일어나는지 설명하자.
3. listWithCounts() 의 SQL 을 바꾸어 정원이 남은 강좌만 돌려주도록 하자.
4. 강좌가 10개일 때 listWithCountsNaive() 가 준비하는 문장 수와 listWithCounts() 의 수를 각각 구하자.

정답과 해설

1번. DELETE 후 영향받은 행 수를 확인한다.

public function cancel(int $courseId, string $student): bool
{
    $stmt = $this->pdo->prepare('DELETE FROM enrollments WHERE course_id = :course AND student = :student');
    $stmt->bindValue(':course', $courseId, PDO::PARAM_INT);
    $stmt->bindValue(':student', $student, PDO::PARAM_STR);
    $stmt->execute();
    return $stmt->rowCount() > 0;
}

rowCount() 는 DELETE 와 UPDATE 에서 영향받은 행 수를 돌려준다. 해당 신청이 없으면 0이므로 false 가 된다.

2번. 예외가 호출한 쪽으로 곧바로 나가고 롤백 코드가 실행되지 않으므로 트랜잭션이 열린 채 남는다. 이미 기록된 INSERT 도 확정되지 않은 상태로 연결에 걸려 있다. 같은 연결에서 beginTransaction() 을 다시 부르면 이미 활성 트랜잭션이 있다는 PDOException 이 난다. 스크립트가 끝나면 확정되지 않은 변경은 버려진다. 하지만 그 사이의 동작은 의도와 다르므로 catch 에서 롤백하는 구조를 지켜야 한다.

3번. 집계 조건은 WHERE 가 아니라 HAVING 에 쓴다. 집계가 끝난 뒤에 걸러야 하기 때문이다.

GROUP BY c.id, c.title, c.capacity
HAVING COUNT(e.id) < c.capacity
ORDER BY c.id

수강생이 없는 강좌는 COUNT 가 0이므로 정원이 1 이상이면 결과에 남는다. LEFT JOIN 을 유지한 덕분이다.

4번. N+1 방식은 목록 질의 1회와 강좌마다 1회씩 10회를 더해 11회다. 조인 방식은 강좌 수와 상관없이 1회다. 이름 목록까지 필요하면 IN 방식 1회를 더해 2회로 끝난다.