트랜잭션과 ACID

package board.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.Objects;

import board.application.postcreation.CreatePostUseCase.CreatePostCommand;
import board.application.postcreation.DailyLimitExceededException;

public final class PostRegistrationSql {
    private static final String ADD_WITHIN_LIMIT = """
            update daily_post_stats
               set total_characters = total_characters + ?
             where member_id = ?
               and published_on = ?
               and cast(total_characters as bigint) + ? <= (
                     select daily_character_limit
                       from members
                      where id = ?)
            """;

    private static final String READ_QUOTA = """
            select d.total_characters, m.daily_character_limit
              from daily_post_stats d
              join members m on m.id = d.member_id
             where d.member_id = ? and d.published_on = ?
            """;

    private static final String INSERT_POST = """
            insert into posts(
              member_id, title, content, published_on,
              client_request_id, created_at, version)
            values (?, ?, ?, ?, ?, ?, 0)
            """;

    public long execute(
            Connection connection,
            CreatePostCommand command,
            Instant createdAt
    ) throws SQLException {
        Objects.requireNonNull(connection, "connection");
        Objects.requireNonNull(command, "command");
        Objects.requireNonNull(createdAt, "createdAt");

        int attempted = command.content().length();
        try (var statement = connection.prepareStatement(ADD_WITHIN_LIMIT)) {
            statement.setInt(1, attempted);
            statement.setLong(2, command.memberId());
            statement.setObject(3, command.publishedOn());
            statement.setInt(4, attempted);
            statement.setLong(5, command.memberId());
            if (statement.executeUpdate() != 1) {
                throw quotaFailure(connection, command, attempted);
            }
        }

        try (var statement = connection.prepareStatement(
                INSERT_POST, Statement.RETURN_GENERATED_KEYS)) {
            statement.setLong(1, command.memberId());
            statement.setString(2, command.title());
            statement.setString(3, command.content());
            statement.setObject(4, command.publishedOn());
            statement.setString(5, command.clientRequestId());
            statement.setObject(
                    6, OffsetDateTime.ofInstant(createdAt, ZoneOffset.UTC));
            if (statement.executeUpdate() != 1) {
                throw new SQLException("post insert did not affect one row");
            }
            try (var keys = statement.getGeneratedKeys()) {
                return readSinglePositiveGeneratedPostId(keys);
            }
        }
    }

    static long readSinglePositiveGeneratedPostId(ResultSet keys)
            throws SQLException {
        if (!keys.next()) {
            throw new SQLException("generated post id missing");
        }
        long postId = keys.getLong(1);
        if (postId <= 0) {
            throw new SQLException("generated post id must be positive");
        }
        if (keys.next()) {
            throw new SQLException("multiple generated post ids");
        }
        return postId;
    }

    private RuntimeException quotaFailure(
            Connection connection,
            CreatePostCommand command,
            int attempted
    ) throws SQLException {
        try (var statement = connection.prepareStatement(READ_QUOTA)) {
            statement.setLong(1, command.memberId());
            statement.setObject(2, command.publishedOn());
            try (var result = statement.executeQuery()) {
                if (!result.next()) {
                    throw new SQLException("daily quota row missing");
                }
                int used = result.getInt("total_characters");
                int limit = result.getInt("daily_character_limit");
                if ((long) used + attempted > limit) {
                    return new DailyLimitExceededException(
                            limit, used, attempted);
                }
                return new IllegalStateException(
                        "daily quota update did not affect one row");
            }
        }
    }
}

package board.jdbc;

import static org.assertj.core.api.Assertions.assertThat;

import java.sql.Connection;
import java.time.OffsetDateTime;

import org.h2.jdbcx.JdbcDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

class TransactionVisibilityTest {
    @Test
    void rollback하면_writer만_보던_행도_사라진다() throws Exception {
        var dataSource = dataSource("visibility_rollback");

        try (Connection writer = dataSource.getConnection();
             Connection observer = dataSource.getConnection()) {
            writer.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            observer.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            writer.setAutoCommit(false);

            insert(writer, 1L);

            assertThat(count(writer)).isEqualTo(1);
            assertThat(count(observer)).isZero();

            writer.rollback();

            assertThat(count(writer)).isZero();
            assertThat(count(observer)).isZero();
        }
    }

    @Test
    void commit한_뒤에는_같은_observer_session에_행이_보인다()
            throws Exception {
        var dataSource = dataSource("visibility_commit");

        try (Connection writer = dataSource.getConnection();
             Connection observer = dataSource.getConnection()) {
            writer.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            observer.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            writer.setAutoCommit(false);

            insert(writer, 1L);
            assertThat(count(observer)).isZero();

            writer.commit();

            assertThat(count(observer)).isEqualTo(1);
        }
    }

    private JdbcDataSource dataSource(String name) throws Exception {
        var dataSource = new JdbcDataSource();
        dataSource.setURL("jdbc:h2:mem:" + name
                + ";MODE=PostgreSQL;DB_CLOSE_DELAY=-1");
        dataSource.setUser("sa");
        new ResourceDatabasePopulator(
                new ClassPathResource("schema.sql")).execute(dataSource);
        try (var connection = dataSource.getConnection();
             var statement = connection.createStatement()) {
            statement.execute("""
                    insert into members(
                      id, email, password_hash, name,
                      active, daily_character_limit)
                    values (
                      41, '[email protected]', 'hash', '회원41',
                      true, 10000)
                    """);
        }
        return dataSource;
    }

    private void insert(Connection connection, long id) throws Exception {
        try (var statement = connection.prepareStatement(
                """
                insert into posts(
                  member_id, title, content, published_on,
                  client_request_id, created_at, version)
                values (?, ?, ?, ?, ?, ?, 0)
                """)) {
            statement.setLong(1, 41L);
            statement.setString(2, "가시성 " + id);
            statement.setString(3, "본문");
            statement.setObject(4, java.time.LocalDate.of(2026, 8, 29));
            statement.setString(5, "visible_req_" + id);
            statement.setObject(
                    6, OffsetDateTime.parse("2026-08-29T00:00:00Z"));
            statement.executeUpdate();
        }
    }

    private int count(Connection connection) throws Exception {
        try (var statement = connection.createStatement();
             var result = statement.executeQuery(
                     "select count(*) from posts")) {
            result.next();
            return result.getInt(1);
        }
    }
}

package board.jdbc;

import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.when;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Instant;
import java.time.LocalDate;
import java.util.List;
import java.util.concurrent.CyclicBarrier;
import java.util.concurrent.Executors;

import board.application.postcreation.CreatePostUseCase.CreatePostCommand;
import board.application.postcreation.DailyLimitExceededException;

import org.h2.jdbcx.JdbcDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

class RowLockLifecycleTest {
    @Test
    void normal_read는_마지막_commit을_보고_lock_read는_timeout_뒤_재시도된다()
            throws Exception {
        var dataSource = new JdbcDataSource();
        dataSource.setURL(
                "jdbc:h2:mem:row_lock;MODE=PostgreSQL;DB_CLOSE_DELAY=-1");
        dataSource.setUser("sa");
        new ResourceDatabasePopulator(
                new ClassPathResource("schema.sql")).execute(dataSource);
        seedQuota(dataSource, 480);

        try (Connection owner = dataSource.getConnection();
             Connection contender = dataSource.getConnection()) {
            owner.setAutoCommit(false);
            contender.setAutoCommit(false);
            owner.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            contender.setTransactionIsolation(
                    Connection.TRANSACTION_READ_COMMITTED);

            add(owner, 30);
            assertThat(read(owner, false)).isEqualTo(510);
            assertThat(read(contender, false)).isEqualTo(480);
            try (var statement = contender.createStatement()) {
                statement.execute("set lock_timeout 100");
            }

            SQLException timeout = assertThrows(
                    SQLException.class,
                    () -> read(contender, true));
            assertThat(timeout.getErrorCode()).isEqualTo(50200);

            owner.rollback();
            contender.rollback();

            assertThat(read(contender, true)).isEqualTo(480);
            contender.rollback();
        }
    }

    @Test
    void 동시에_같은_게시일_한도를_늘려도_한_명만_commit한다()
            throws Exception {
        var dataSource = new JdbcDataSource();
        dataSource.setURL(
                "jdbc:h2:mem:daily_race;MODE=PostgreSQL;DB_CLOSE_DELAY=-1");
        dataSource.setUser("sa");
        new ResourceDatabasePopulator(
                new ClassPathResource("schema.sql")).execute(dataSource);
        seedQuota(dataSource, 9_000);

        var barrier = new CyclicBarrier(2);
        var registration = new PostRegistrationSql();
        var publishedOn = LocalDate.of(2026, 8, 29);
        var first = new CreatePostCommand(
                41L, "첫 번째", "가".repeat(600),
                publishedOn, "race_req_A1");
        var second = new CreatePostCommand(
                41L, "두 번째", "나".repeat(600),
                publishedOn, "race_req_B2");

        List<Outcome> outcomes;
        try (var executor = Executors.newVirtualThreadPerTaskExecutor()) {
            var left = executor.submit(
                    () -> attempt(dataSource, registration, barrier, first));
            var right = executor.submit(
                    () -> attempt(dataSource, registration, barrier, second));
            outcomes = List.of(left.get(), right.get());
        }

        assertThat(outcomes).extracting(Outcome::kind)
                .containsExactlyInAnyOrder("committed", "limited");
        var limited = outcomes.stream()
                .filter(outcome -> outcome.failure() != null)
                .findFirst()
                .orElseThrow()
                .failure();
        assertThat(limited.limitCharacters()).isEqualTo(10_000);
        assertThat(limited.usedCharacters()).isEqualTo(9_600);
        assertThat(limited.attemptedCharacters()).isEqualTo(600);

        try (var connection = dataSource.getConnection()) {
            assertThat(scalar(connection, """
                    select total_characters from daily_post_stats
                     where member_id = 41
                       and published_on = date '2026-08-29'
                    """)).isEqualTo(9_600);
            assertThat(scalar(connection,
                    "select count(*) from posts")).isEqualTo(1);
        }

        assertGeneratedKeyContract();
    }

    private void assertGeneratedKeyContract() throws Exception {
        ResultSet valid = generatedKeys(41L, false);
        assertThat(PostRegistrationSql
                .readSinglePositiveGeneratedPostId(valid)).isEqualTo(41L);

        ResultSet missing = mock(ResultSet.class);
        SQLException missingFailure = assertThrows(
                SQLException.class,
                () -> PostRegistrationSql
                        .readSinglePositiveGeneratedPostId(missing));
        assertThat((Throwable) missingFailure)
                .hasMessage("generated post id missing");

        ResultSet nonpositive = generatedKeys(0L, false);
        SQLException nonpositiveFailure = assertThrows(
                SQLException.class,
                () -> PostRegistrationSql
                        .readSinglePositiveGeneratedPostId(nonpositive));
        assertThat((Throwable) nonpositiveFailure)
                .hasMessage("generated post id must be positive");

        ResultSet extra = generatedKeys(41L, true);
        SQLException extraFailure = assertThrows(
                SQLException.class,
                () -> PostRegistrationSql
                        .readSinglePositiveGeneratedPostId(extra));
        assertThat((Throwable) extraFailure)
                .hasMessage("multiple generated post ids");
    }

    private ResultSet generatedKeys(long firstId, boolean hasExtra)
            throws Exception {
        ResultSet keys = mock(ResultSet.class);
        when(keys.next()).thenReturn(true, hasExtra);
        when(keys.getLong(1)).thenReturn(firstId);
        return keys;
    }

    private Outcome attempt(
            JdbcDataSource dataSource,
            PostRegistrationSql registration,
            CyclicBarrier barrier,
            CreatePostCommand command
    ) throws Exception {
        try (Connection connection = dataSource.getConnection()) {
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(
                    Connection.TRANSACTION_READ_COMMITTED);
            barrier.await();
            try {
                long id = registration.execute(
                        connection, command,
                        Instant.parse("2026-08-29T00:00:00Z"));
                connection.commit();
                return new Outcome("committed", id, null);
            } catch (DailyLimitExceededException exception) {
                connection.rollback();
                return new Outcome("limited", 0L, exception);
            } catch (SQLException | RuntimeException exception) {
                connection.rollback();
                throw exception;
            }
        }
    }

    private void seedQuota(JdbcDataSource dataSource, int used)
            throws Exception {
        try (var connection = dataSource.getConnection();
             var member = connection.prepareStatement("""
                    insert into members(
                      id, email, password_hash, name,
                      active, daily_character_limit)
                    values (41, ?, 'hash', '회원41', true, 10000)
                    """);
             var quota = connection.prepareStatement("""
                    insert into daily_post_stats(
                      member_id, published_on, total_characters)
                    values (41, date '2026-08-29', ?)
                    """)) {
            member.setString(1, "[email protected]");
            member.executeUpdate();
            quota.setInt(1, used);
            quota.executeUpdate();
        }
    }

    private void add(Connection connection, int characters) throws Exception {
        try (var statement = connection.prepareStatement("""
                update daily_post_stats
                   set total_characters = total_characters + ?
                 where member_id = 41
                   and published_on = date '2026-08-29'
                """)) {
            statement.setInt(1, characters);
            statement.executeUpdate();
        }
    }

    private int read(Connection connection, boolean forUpdate)
            throws Exception {
        String sql = """
                select total_characters from daily_post_stats
                 where member_id = 41
                   and published_on = date '2026-08-29'
                """ + (forUpdate ? " for update" : "");
        try (var statement = connection.createStatement();
             var result = statement.executeQuery(sql)) {
            result.next();
            return result.getInt(1);
        }
    }

    private int scalar(Connection connection, String sql) throws Exception {
        try (var statement = connection.createStatement();
             var result = statement.executeQuery(sql)) {
            result.next();
            return result.getInt(1);
        }
    }

    private record Outcome(
            String kind,
            long postId,
            DailyLimitExceededException failure
    ) {}
}