[Advanced Spring Boot] Transaction chuyên sâu trong Spring: propagation, isolation level và rollback rules

create table accounts (
    id      bigint generated by default as identity primary key,
    owner   varchar(100)   not null,
    kind    varchar(20)    not null,
    balance numeric(12, 2) not null
);
 
create table audit_entries (
    id      bigint generated by default as identity primary key,
    message varchar(200) not null
);
 
insert into accounts (owner, kind, balance) values
    ('an', 'CHECKING', 100.00),
    ('an', 'SAVINGS', 100.00),
    ('binh', 'CHECKING', 100.00);

spring.application.name=demo
spring.datasource.url=jdbc:postgresql://localhost:5506/demo
spring.datasource.username=demo
spring.datasource.password=demo
spring.jpa.open-in-view=false
spring.jpa.hibernate.ddl-auto=validate
logging.level.org.springframework.orm.jpa.JpaTransactionManager=DEBUG
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.springframework.jdbc.support.JdbcTransactionManager=DEBUG

docker run -d --name sba-a6-pg -e POSTGRES_USER=demo -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo -p 5506:5432 postgres:18

public interface AccountRepository extends JpaRepository<Account, Long> {
 
    @Query("select sum(a.balance) from Account a where a.owner = :owner")
    BigDecimal totalBalance(String owner);
 
    @Query("select a.balance from Account a where a.id = :id")
    BigDecimal balanceOf(Long id);
 
    // a stand-in for a slow report query
    @Query(value = "select count(*) from pg_sleep(:seconds)", nativeQuery = true)
    long slowReport(int seconds);
}

@Component
public class TxProbe {
 
    private final JdbcClient jdbc;
    private final HikariDataSource dataSource;
 
    TxProbe(JdbcClient jdbc, HikariDataSource dataSource) {
        this.jdbc = jdbc;
        this.dataSource = dataSource;
    }
 
    public String describe() {
        boolean active = TransactionSynchronizationManager.isActualTransactionActive();
        String name = shortName(TransactionSynchronizationManager.getCurrentTransactionName());
        Integer pid = jdbc.sql("select pg_backend_pid()").query(Integer.class).single();
        int busy = dataSource.getHikariPoolMXBean().getActiveConnections();
        return "actualTx=%s name=%s pid=%d hikariActive=%d".formatted(active, name, pid, busy);
    }
 
    // com.example.demo.audit.AuditService.requiresNew -> AuditService.requiresNew
    private static String shortName(String name) {
        return name == null ? null : name.substring(name.lastIndexOf('.', name.lastIndexOf('.') - 1) + 1);
    }
}

@Service
public class AuditService {
 
    private static final Logger log = LoggerFactory.getLogger(AuditService.class);
 
    private final JdbcClient jdbc;
    private final TxProbe probe;
 
    AuditService(JdbcClient jdbc, TxProbe probe) {
        this.jdbc = jdbc;
        this.probe = probe;
    }
 
    @Transactional(propagation = Propagation.REQUIRED)
    public void required(String message, boolean fail) {
        record("REQUIRED", message, fail);
    }
 
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void requiresNew(String message, boolean fail) {
        record("REQUIRES_NEW", message, fail);
    }
 
    @Transactional(propagation = Propagation.SUPPORTS)
    public void supports(String message, boolean fail) {
        record("SUPPORTS", message, fail);
    }
 
    @Transactional(propagation = Propagation.MANDATORY)
    public void mandatory(String message, boolean fail) {
        record("MANDATORY", message, fail);
    }
 
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public void notSupported(String message, boolean fail) {
        record("NOT_SUPPORTED", message, fail);
    }
 
    @Transactional(propagation = Propagation.NEVER)
    public void never(String message, boolean fail) {
        record("NEVER", message, fail);
    }
 
    @Transactional(propagation = Propagation.NESTED)
    public void nested(String message, boolean fail) {
        record("NESTED", message, fail);
    }
 
    @Transactional(noRollbackFor = AuditFailedException.class)
    public void requiredNoRollback(String message, boolean fail) {
        record("REQUIRED, noRollbackFor", message, fail);
    }
 
    private void record(String propagation, String message, boolean fail) {
        log.info("{}: {}", propagation, probe.describe());
        jdbc.sql("insert into audit_entries (message) values (?)").param(message).update();
        if (fail) {
            throw new AuditFailedException(message);
        }
    }
}

@Component
@Profile("lab")
public class OuterService {
 
    private static final Logger log = LoggerFactory.getLogger(OuterService.class);
 
    private final JdbcClient jdbc;
    private final TxProbe probe;
 
    OuterService(JdbcClient jdbc, TxProbe probe) {
        this.jdbc = jdbc;
        this.probe = probe;
    }
 
    @Transactional
    public void inTransaction(String label, Runnable inner) {
        jdbc.sql("insert into audit_entries (message) values (?)").param("outer " + label).update();
        log.info("outer: {}", probe.describe());
        inner.run();
        log.info("outer, after inner: {}", probe.describe());
    }
 
    // catching(...) and failingAfter(...) are shown in the rollback section
}

        step("REQUIRES_NEW, inside a transaction", () -> outer.inTransaction("requires-new", () -> audit.requiresNew("requires-new", false)));
        step("REQUIRES_NEW, no transaction", () -> audit.requiresNew("requires-new", false));

    void step(String name, Runnable body) {
        jdbc.sql("delete from audit_entries").update();
        log.info("=== {} ===", name);
        try {
            body.run();
        } catch (RuntimeException e) {
            chain(e);
        }
        List<String> rows = jdbc.sql("select message from audit_entries order by id").query(String.class).list();
        log.info("committed audit rows: {}", rows);
    }

./gradlew -q bootJar

java -jar build/libs/demo-0.0.1-SNAPSHOT.jar --spring.profiles.active=lab --spring.main.web-application-type=none --lab=propagation '--logging.pattern.console=%logger{0}: %msg%n'

Lab: === REQUIRED, inside a transaction ===
JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.OuterService.inTransaction]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
JpaTransactionManager: Opened new EntityManager [SessionImpl(659416252<open>)] for JPA transaction
JpaTransactionManager: Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@5066a895]
OuterService: outer: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1
JpaTransactionManager: Participating in existing transaction
AuditService: REQUIRED: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1
OuterService: outer, after inner: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1
JpaTransactionManager: Initiating transaction commit
JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(659416252<open>)]
JpaTransactionManager: Closing JPA EntityManager after transaction
Lab: committed audit rows: [outer required, required]

Lab: === REQUIRES_NEW, inside a transaction ===
JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.OuterService.inTransaction]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
JpaTransactionManager: Opened new EntityManager [SessionImpl(662238161<open>)] for JPA transaction
JpaTransactionManager: Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@30a34f07]
OuterService: outer: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1
JpaTransactionManager: Suspending current transaction, creating new transaction with name [com.example.demo.audit.AuditService.requiresNew]
JpaTransactionManager: Opened new EntityManager [SessionImpl(1378219538<open>)] for JPA transaction
JpaTransactionManager: Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@1d3aefd]
AuditService: REQUIRES_NEW: actualTx=true name=AuditService.requiresNew pid=438 hikariActive=2
JpaTransactionManager: Initiating transaction commit
JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(1378219538<open>)]
JpaTransactionManager: Closing JPA EntityManager after transaction
JpaTransactionManager: Resuming suspended transaction after completion of inner transaction
OuterService: outer, after inner: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1
JpaTransactionManager: Initiating transaction commit
JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(662238161<open>)]
JpaTransactionManager: Closing JPA EntityManager after transaction
Lab: committed audit rows: [outer requires-new, requires-new]

JpaTransactionManager: Suspending current transaction, creating new transaction with name [com.example.demo.audit.AuditService.requiresNew]
OuterService: outer synchronization: suspend
AuditService: REQUIRES_NEW: actualTx=true name=AuditService.requiresNew pid=438 hikariActive=2
JpaTransactionManager: Initiating transaction commit
JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(1903221272<open>)]
JpaTransactionManager: Resuming suspended transaction after completion of inner transaction
OuterService: outer synchronization: resume
JpaTransactionManager: Initiating transaction commit
JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(499755936<open>)]
OuterService: outer synchronization: afterCommit

Lab: === SUPPORTS, no transaction ===
AuditService: SUPPORTS: actualTx=false name=AuditService.supports pid=437 hikariActive=1
Lab: committed audit rows: [supports]

Lab: === MANDATORY, no transaction ===
Lab: threw org.springframework.transaction.IllegalTransactionStateException: No existing transaction found for transaction marked with propagation 'mandatory'
Lab: committed audit rows: []
Lab: === NEVER, inside a transaction ===
JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.OuterService.inTransaction]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
OuterService: outer: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1
JpaTransactionManager: Initiating transaction rollback
JpaTransactionManager: Rolling back JPA transaction on EntityManager [SessionImpl(413705230<open>)]
Lab: threw org.springframework.transaction.IllegalTransactionStateException: Existing transaction found for transaction marked with propagation 'never'
Lab: committed audit rows: []

OuterService: outer: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1
JpaTransactionManager: Suspending current transaction
AuditService: NOT_SUPPORTED: actualTx=false name=AuditService.notSupported pid=438 hikariActive=2
JpaTransactionManager: Resuming suspended transaction after completion of inner transaction
OuterService: outer, after inner: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1

Lab: === NESTED, inside a transaction ===
JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.OuterService.inTransaction]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
OuterService: outer: actualTx=true name=OuterService.inTransaction pid=437 hikariActive=1
JpaTransactionManager: Initiating transaction rollback
Lab: threw org.springframework.transaction.NestedTransactionNotSupportedException: Transaction manager does not allow nested transactions by default - specify 'nestedTransactionAllowed' property with value 'true'
Lab: committed audit rows: []

@Configuration
class LabTransactionConfig {
 
    @Bean
    @ConditionalOnBooleanProperty("lab.nested-allowed")
    TransactionManagerCustomizer<AbstractPlatformTransactionManager> allowNested() {
        return transactionManager -> transactionManager.setNestedTransactionAllowed(true);
    }
 
    @Bean
    @ConditionalOnBooleanProperty("lab.validate-existing")
    TransactionManagerCustomizer<AbstractPlatformTransactionManager> validateExisting() {
        return transactionManager -> transactionManager.setValidateExistingTransaction(true);
    }
}

JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.OuterService.catching]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
JpaTransactionManager: Creating nested transaction with name [com.example.demo.audit.AuditService.nested]
JpaTransactionManager: Initiating transaction rollback
Lab: threw org.springframework.transaction.NestedTransactionNotSupportedException: JpaDialect does not support savepoints - check your JPA provider's capabilities
Lab: committed audit rows: []

    public void jdbcNested() {
        JdbcTransactionManager tm = new JdbcTransactionManager(dataSource);
        TransactionTemplate outer = new TransactionTemplate(tm);
        TransactionTemplate nested = new TransactionTemplate(tm);
        nested.setPropagationBehavior(TransactionDefinition.PROPAGATION_NESTED);
        outer.executeWithoutResult(status -> {
            jdbc.sql("insert into audit_entries (message) values ('outer jdbc')").update();
            try {
                nested.executeWithoutResult(inner -> {
                    jdbc.sql("insert into audit_entries (message) values ('nested jdbc')").update();
                    throw new AuditFailedException("nested jdbc");
                });
            } catch (AuditFailedException e) {
                log.info("outer caught: {}", e.getMessage());
            }
        });
    }

JdbcTransactionManager: Creating new transaction with name [null]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
JdbcTransactionManager: Acquired Connection [HikariProxyConnection@310140704 wrapping org.postgresql.jdbc.PgConnection@2db1b657] for JDBC transaction
JdbcTransactionManager: Switching JDBC Connection [HikariProxyConnection@310140704 wrapping org.postgresql.jdbc.PgConnection@2db1b657] to manual commit
JdbcTransactionManager: Creating nested transaction with name [null]
JdbcTransactionManager: Rolling back transaction to savepoint
NestedLab: outer caught: audit failed: nested jdbc
JdbcTransactionManager: Initiating transaction commit
JdbcTransactionManager: Committing JDBC transaction on Connection [HikariProxyConnection@310140704 wrapping org.postgresql.jdbc.PgConnection@2db1b657]
JdbcTransactionManager: Releasing JDBC Connection [HikariProxyConnection@310140704 wrapping org.postgresql.jdbc.PgConnection@2db1b657] after transaction
Lab: committed audit rows: [outer jdbc]

docker exec sba-a6-pg psql -U demo -d demo -c "alter system set log_statement = 'all'" -c "select pg_reload_conf()"

[418] LOG:  execute <unnamed>: insert into audit_entries (message) values ('outer jdbc')
[418] LOG:  execute <unnamed>: SAVEPOINT "SAVEPOINT_1"
[418] LOG:  execute <unnamed>: insert into audit_entries (message) values ('nested jdbc')
[418] LOG:  execute <unnamed>: ROLLBACK TO SAVEPOINT "SAVEPOINT_1"
[418] LOG:  execute <unnamed>: RELEASE SAVEPOINT "SAVEPOINT_1"
[418] LOG:  execute S_1: COMMIT

@Service
public class AccountService {
 
    private final AccountRepository accounts;
    private final AuditService audit;
 
    AccountService(AccountRepository accounts, AuditService audit) {
        this.accounts = accounts;
        this.audit = audit;
    }
 
    @Transactional
    public BigDecimal withdraw(Long id, BigDecimal amount) {
        Account account = accounts.findById(id).orElseThrow();
        account.withdraw(amount);
        audit.requiresNew("withdraw " + amount + " from account " + id, false);
        return account.getBalance();
    }
}

    @PostMapping("/api/accounts/{id}/withdrawals")
    BigDecimal withdraw(@PathVariable Long id, @RequestParam BigDecimal amount) {
        return service.withdraw(id, amount);
    }

java -jar build/libs/demo-0.0.1-SNAPSHOT.jar --server.port=8206 --logging.level.com.zaxxer.hikari.HikariConfig=DEBUG --logging.level.org.springframework.orm.jpa.JpaTransactionManager=INFO --logging.level.org.hibernate.SQL=INFO

ab -n 2000 -c 10 -s 120 -p empty.txt -T application/x-www-form-urlencoded 'http://localhost:8206/api/accounts/3/withdrawals?amount=0.01'

Concurrency Level:      9
Time taken for tests:   1.602 seconds
Complete requests:      2000
Failed requests:        0

Concurrency Level:      10
Time taken for tests:   32.814 seconds
Complete requests:      2000
Failed requests:        10
   (Connect: 0, Receive: 0, Length: 10, Exceptions: 0)
Non-2xx responses:      10

HikariPool-1 - Connection is not available, request timed out after 30006ms (total=10, active=10, idle=0, waiting=1)
Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction] with root cause
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30006ms (total=10, active=10, idle=0, waiting=1)

[http-nio-8206-exec-2] JpaTransactionManager: Creating new transaction with name [com.example.demo.account.AccountService.withdraw]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
[http-nio-8206-exec-2] JpaTransactionManager: Opened new EntityManager [SessionImpl(1827638077<open>)] for JPA transaction
[http-nio-8206-exec-3] JpaTransactionManager: Creating new transaction with name [com.example.demo.account.AccountService.withdraw]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
[http-nio-8206-exec-3] JpaTransactionManager: Opened new EntityManager [SessionImpl(230918494<open>)] for JPA transaction
[http-nio-8206-exec-2] JpaTransactionManager: Participating in existing transaction
[http-nio-8206-exec-3] JpaTransactionManager: Participating in existing transaction
[http-nio-8206-exec-3] JpaTransactionManager: Suspending current transaction, creating new transaction with name [com.example.demo.audit.AuditService.requiresNew]
[http-nio-8206-exec-2] JpaTransactionManager: Suspending current transaction, creating new transaction with name [com.example.demo.audit.AuditService.requiresNew]
[http-nio-8206-exec-3] JpaTransactionManager: Opened new EntityManager [SessionImpl(1111063725<open>)] for JPA transaction
[http-nio-8206-exec-2] JpaTransactionManager: Opened new EntityManager [SessionImpl(588604139<open>)] for JPA transaction
[http-nio-8206-exec-2] error: HikariPool-1 - Connection is not available, request timed out after 5006ms (total=2, active=2, idle=0, waiting=0)
[http-nio-8206-exec-3] error: HikariPool-1 - Connection is not available, request timed out after 5006ms (total=2, active=2, idle=0, waiting=0)
[http-nio-8206-exec-2] JpaTransactionManager: Initiating transaction rollback
[http-nio-8206-exec-3] JpaTransactionManager: Initiating transaction rollback
[http-nio-8206-exec-2] JpaTransactionManager: Rolling back JPA transaction on EntityManager [SessionImpl(1827638077<open>)]
[http-nio-8206-exec-3] JpaTransactionManager: Rolling back JPA transaction on EntityManager [SessionImpl(230918494<open>)]

docker exec sba-a6-pg psql -U demo -d demo -c 'show default_transaction_isolation'

 default_transaction_isolation 
-------------------------------
 read committed
(1 row)

[156] LOG:  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
[156] LOG:  execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ
[156] LOG:  statement: BEGIN
[156] LOG:  execute <unnamed>: select current_setting('transaction_isolation')
[156] LOG:  execute <unnamed>: select current_setting('transaction_read_only')
[156] LOG:  execute S_1: COMMIT
[156] LOG:  execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED

    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void readTwiceReadCommitted(Long id, CountDownLatch firstReadDone, CountDownLatch otherCommitted) {
        readTwice(id, firstReadDone, otherCommitted);
    }
 
    private void readTwice(Long id, CountDownLatch firstReadDone, CountDownLatch otherCommitted) {
        Account account = accounts.findById(id).orElseThrow();
        log.info("first read:  entity {}, query {}", account.getBalance(), accounts.balanceOf(id));
        firstReadDone.countDown();
        await(otherCommitted);
        Account again = accounts.findById(id).orElseThrow();
        log.info("second read: entity {}, query {}", again.getBalance(), accounts.balanceOf(id));
    }
 
    @Transactional
    public void deposit(Long id, BigDecimal amount) {
        Account account = accounts.findById(id).orElseThrow();
        account.deposit(amount);
        log.info("deposit {} into account {}", amount, id);
    }

[tx-A] JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.IsolationLab.readTwiceReadCommitted]: PROPAGATION_REQUIRED,ISOLATION_READ_COMMITTED
[tx-A] SQL: select a1_0.id,a1_0.balance,a1_0.kind,a1_0.owner from accounts a1_0 where a1_0.id=?
[tx-A] SQL: select a1_0.balance from accounts a1_0 where a1_0.id=?
[tx-A] IsolationLab: first read:  entity 100.00, query 100.00
[tx-B] JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.IsolationLab.deposit]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
[tx-B] SQL: select a1_0.id,a1_0.balance,a1_0.kind,a1_0.owner from accounts a1_0 where a1_0.id=?
[tx-B] IsolationLab: deposit 50.00 into account 1
[tx-B] JpaTransactionManager: Initiating transaction commit
[tx-B] JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(19897183<open>)]
[tx-B] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-A] SQL: select a1_0.balance from accounts a1_0 where a1_0.id=?
[tx-A] IsolationLab: second read: entity 100.00, query 150.00
[tx-A] JpaTransactionManager: Initiating transaction commit

    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void withdrawReadCommitted(Long id, BigDecimal amount, CountDownLatch bothRead, CountDownLatch mayWrite) {
        withdraw(id, amount, bothRead, mayWrite);
    }
 
    private void withdraw(Long id, BigDecimal amount, CountDownLatch bothRead, CountDownLatch mayWrite) {
        Account account = accounts.findById(id).orElseThrow();
        log.info("read balance {}", account.getBalance());
        arriveAndAwait(bothRead);
        await(mayWrite);
        account.withdraw(amount);
        log.info("withdraw {}, balance now {} (flushed at commit)", amount, account.getBalance());
    }

            CountDownLatch bothRead = new CountDownLatch(2);
            CountDownLatch aCommitted = new CountDownLatch(1);
            CountDownLatch go = new CountDownLatch(0);
            BigDecimal amount = new BigDecimal("30.00");
            twoThreads(
                    () -> {
                        try {
                            if (rr) iso.withdrawRepeatableRead(1L, amount, bothRead, go);
                            else iso.withdrawReadCommitted(1L, amount, bothRead, go);
                            log.info("committed");
                        } finally {
                            aCommitted.countDown();
                        }
                    },
                    () -> {
                        if (rr) iso.withdrawRepeatableRead(1L, amount, bothRead, aCommitted);
                        else iso.withdrawReadCommitted(1L, amount, bothRead, aCommitted);
                        log.info("committed");
                    });

[tx-A] IsolationLab: read balance 100.00
[tx-B] IsolationLab: read balance 100.00
[tx-A] IsolationLab: withdraw 30.00, balance now 70.00 (flushed at commit)
[tx-A] JpaTransactionManager: Initiating transaction commit
[tx-A] JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(1393197551<open>)]
[tx-A] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-A] Lab: committed
[tx-B] IsolationLab: withdraw 30.00, balance now 70.00 (flushed at commit)
[tx-B] JpaTransactionManager: Initiating transaction commit
[tx-B] JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(44491633<open>)]
[tx-B] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-B] Lab: committed
[main] Lab: balances: [1=70.00, 2=100.00, 3=100.00]

[tx-A] IsolationLab: second read: entity 100.00, query 100.00

[tx-B] IsolationLab: withdraw 30.00, balance now 70.00 (flushed at commit)
[tx-B] JpaTransactionManager: Initiating transaction commit
[tx-B] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-B] error: HHH000247: ErrorCode: 0, SQLState: 40001
[tx-B] error: ERROR: could not serialize access due to concurrent update
[tx-B] JpaTransactionManager: Initiating transaction rollback after commit exception
[tx-B] Lab: threw org.springframework.dao.CannotAcquireLockException: could not execute statement [ERROR: could not serialize access due to concurrent update] [update accounts set balance=?,kind=?,owner=? where id=?]; SQL [update accounts set balance=?,kind=?,owner=? where id=?]
[tx-B] Lab:   caused by org.hibernate.exception.LockAcquisitionException: could not execute statement [ERROR: could not serialize access due to concurrent update] [update accounts set balance=?,kind=?,owner=? where id=?]
[tx-B] Lab:   caused by org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update
[main] Lab: balances: [1=70.00, 2=100.00, 3=100.00]

    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void withdrawKeepingTotalSerializable(Long id, BigDecimal amount, CountDownLatch bothRead, CountDownLatch mayWrite) {
        withdrawKeepingTotal(id, amount, bothRead, mayWrite);
    }
 
    private void withdrawKeepingTotal(Long id, BigDecimal amount, CountDownLatch bothRead, CountDownLatch mayWrite) {
        Account account = accounts.findById(id).orElseThrow();
        BigDecimal total = accounts.totalBalance(account.getOwner());
        log.info("total balance of {} is {}, withdrawing {} from account {}", account.getOwner(), total, amount, id);
        if (total.compareTo(amount) < 0) {
            throw new InsufficientFundsException(account.getOwner(), total, amount);
        }
        arriveAndAwait(bothRead);
        await(mayWrite);
        account.withdraw(amount);
    }

[tx-B] IsolationLab: total balance of an is 200.00, withdrawing 150.00 from account 2
[tx-A] IsolationLab: total balance of an is 200.00, withdrawing 150.00 from account 1
[tx-A] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-A] Lab: committed
[tx-B] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-B] Lab: committed
[main] Lab: balances: [1=-50.00, 2=-50.00, 3=100.00]

[tx-A] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-A] Lab: committed
[tx-B] JpaTransactionManager: Initiating transaction commit
[tx-B] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-B] error: HHH000247: ErrorCode: 0, SQLState: 40001
[tx-B] error: ERROR: could not serialize access due to read/write dependencies among transactions
  Detail: Reason code: Canceled on identification as a pivot, during write.
  Hint: The transaction might succeed if retried.
[tx-B] Lab: threw org.springframework.dao.CannotAcquireLockException: could not execute statement [ERROR: could not serialize access due to read/write dependencies among transactions
  Detail: Reason code: Canceled on identification as a pivot, during write.
  Hint: The transaction might succeed if retried.] [update accounts set balance=?,kind=?,owner=? where id=?]; SQL [update accounts set balance=?,kind=?,owner=? where id=?]
[main] Lab: balances: [1=-50.00, 2=100.00, 3=100.00]

[tx-B] Lab: threw org.springframework.dao.CannotAcquireLockException: Hibernate transaction: Unable to commit against JDBC Connection; ERROR: could not serialize access due to read/write dependencies among transactions
  Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
  Hint: The transaction might succeed if retried.

    @Transactional(isolation = Isolation.READ_UNCOMMITTED)
    public void readUncommitted(Long id) {
        String level = jdbc.sql("select current_setting('transaction_isolation')").query(String.class).single();
        log.info("transaction_isolation = {}, balance {}", level, accounts.balanceOf(id));
    }

[tx-A] SQL: update accounts set balance=?,kind=?,owner=? where id=?
[tx-A] IsolationLab: flushed balance 70.00, not committed
[tx-B] JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.IsolationLab.readUncommitted]: PROPAGATION_REQUIRED,ISOLATION_READ_UNCOMMITTED
[tx-B] SQL: select a1_0.balance from accounts a1_0 where a1_0.id=?
[tx-B] IsolationLab: transaction_isolation = read uncommitted, balance 100.00
[tx-A] JpaTransactionManager: Initiating transaction rollback
[main] Lab: balances: [1=100.00, 2=100.00, 3=100.00]

JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.SettingsLab.defaultCallingSerializable]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
JpaTransactionManager: Participating in existing transaction
InnerOps: inner SERIALIZABLE: postgres isolation=read committed read_only=off | jdbc readOnly=false | hibernate flushMode=AUTO defaultReadOnly=false
JpaTransactionManager: Initiating transaction commit

Lab: threw org.springframework.transaction.IllegalTransactionStateException: Participating transaction with definition [PROPAGATION_REQUIRED,ISOLATION_SERIALIZABLE] specifies isolation level which is incompatible with existing transaction: (unknown)

@Component
public class SerializationRetry {
 
    private static final Logger log = LoggerFactory.getLogger(SerializationRetry.class);
    private static final int MAX_ATTEMPTS = 3;
 
    // transaction must call a @Transactional method on another bean: each attempt is a new transaction
    public void run(Runnable transaction) {
        for (int attempt = 1; ; attempt++) {
            try {
                transaction.run();
                return;
            } catch (ConcurrencyFailureException e) {
                if (attempt == MAX_ATTEMPTS) {
                    throw e;
                }
                log.info("attempt {} failed with {}, retrying", attempt, e.getClass().getSimpleName());
            }
        }
    }
}

[tx-B] error: ERROR: could not serialize access due to read/write dependencies among transactions
[tx-B] SerializationRetry: attempt 1 failed with CannotAcquireLockException, retrying
[tx-B] JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.IsolationLab.withdrawKeepingTotalSerializable]: PROPAGATION_REQUIRED,ISOLATION_SERIALIZABLE
[tx-B] SQL: select sum(a1_0.balance) from accounts a1_0 where a1_0.owner=?
[tx-B] IsolationLab: total balance of an is 50.00, withdrawing 150.00 from account 2
[tx-B] JpaTransactionManager: Initiating transaction rollback
[tx-B] Lab: threw com.example.demo.account.InsufficientFundsException: total balance of an is 50.00, cannot withdraw 150.00

    @Transactional(readOnly = true)
    public void readOnly() {
        Account account = accounts.findById(1L).orElseThrow();
        log.info("readOnly: {} | snapshot kept={}", settings.describe(), snapshotKept(account));
    }
 
    private boolean snapshotKept(Account account) {
        SessionImplementor session = em.unwrap(SessionImplementor.class);
        return session.getPersistenceContextInternal().getEntry(account).getLoadedState() != null;
    }

SettingsLab: read-write: postgres isolation=read committed read_only=off | jdbc readOnly=false | hibernate flushMode=AUTO defaultReadOnly=false | snapshot kept=true
SettingsLab: readOnly: postgres isolation=read committed read_only=on | jdbc readOnly=true | hibernate flushMode=MANUAL defaultReadOnly=true | snapshot kept=false

[156] LOG:  statement: BEGIN
[156] LOG:  execute <unnamed>: select a1_0.id,a1_0.balance,a1_0.kind,a1_0.owner from accounts a1_0 where a1_0.id=$1
[156] LOG:  execute S_1: COMMIT
[156] LOG:  execute <unnamed>: delete from audit_entries
[156] LOG:  statement: BEGIN READ ONLY
[156] LOG:  execute <unnamed>: select a1_0.id,a1_0.balance,a1_0.kind,a1_0.owner from accounts a1_0 where a1_0.id=$1
[156] LOG:  execute S_1: COMMIT

    @Transactional(readOnly = true)
    public void readOnlyDeposit(Long id) {
        Account account = accounts.findById(id).orElseThrow();
        account.deposit(new BigDecimal("5.00"));
        log.info("inner readOnly: {}", settings.describe());
    }

JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.SettingsLab.readWriteCallingReadOnly]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
JpaTransactionManager: Participating in existing transaction
InnerOps: inner readOnly: postgres isolation=read committed read_only=off | jdbc readOnly=false | hibernate flushMode=AUTO defaultReadOnly=false
JpaTransactionManager: Initiating transaction commit
JpaTransactionManager: Committing JPA transaction on EntityManager [SessionImpl(323302928<open>)]
SQL: update accounts set balance=?,kind=?,owner=? where id=?
Lab: committed audit rows: []
Lab: balances: [1=105.00, 2=100.00, 3=100.00]

Lab: threw org.springframework.transaction.IllegalTransactionStateException: Participating transaction with definition [PROPAGATION_REQUIRED,ISOLATION_DEFAULT] is not marked as read-only but existing transaction is

    @Transactional
    public void catching(String label, Runnable inner) {
        jdbc.sql("insert into audit_entries (message) values (?)").param("outer " + label).update();
        try {
            inner.run();
        } catch (AuditFailedException e) {
            log.info("outer caught: {}", e.getMessage());
        }
    }
 
    @Transactional
    public void failingAfter(String label, Runnable inner) {
        jdbc.sql("insert into audit_entries (message) values (?)").param("outer " + label).update();
        inner.run();
        throw new IllegalStateException("outer failed after the inner call");
    }

Lab: === REQUIRES_NEW fails, outer catches ===
JpaTransactionManager: Suspending current transaction, creating new transaction with name [com.example.demo.audit.AuditService.requiresNew]
AuditService: REQUIRES_NEW: actualTx=true name=AuditService.requiresNew pid=438 hikariActive=2
JpaTransactionManager: Initiating transaction rollback
JpaTransactionManager: Rolling back JPA transaction on EntityManager [SessionImpl(1337277302<open>)]
JpaTransactionManager: Resuming suspended transaction after completion of inner transaction
OuterService: outer caught: audit failed: requires-new
JpaTransactionManager: Initiating transaction commit
Lab: committed audit rows: [outer requires-new]
Lab: === outer fails after REQUIRES_NEW ===
JpaTransactionManager: Suspending current transaction, creating new transaction with name [com.example.demo.audit.AuditService.requiresNew]
AuditService: REQUIRES_NEW: actualTx=true name=AuditService.requiresNew pid=438 hikariActive=2
JpaTransactionManager: Initiating transaction commit
JpaTransactionManager: Resuming suspended transaction after completion of inner transaction
JpaTransactionManager: Initiating transaction rollback
JpaTransactionManager: Rolling back JPA transaction on EntityManager [SessionImpl(837508822<open>)]
Lab: threw java.lang.IllegalStateException: outer failed after the inner call
Lab: committed audit rows: [requires-new]

JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.OuterService.catching]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
JpaTransactionManager: Participating in existing transaction
AuditService: REQUIRED, noRollbackFor: actualTx=true name=OuterService.catching pid=437 hikariActive=1
OuterService: outer caught: audit failed: no-rollback
JpaTransactionManager: Initiating transaction commit
Lab: committed audit rows: [outer no-rollback, no-rollback]

@Configuration
@ConditionalOnBooleanProperty("lab.rollback-on-all")
@EnableTransactionManagement(rollbackOn = RollbackOn.ALL_EXCEPTIONS)
class RollbackOnConfig {
}

    @Transactional
    public void importStatement() throws IOException {
        jdbc.sql("insert into audit_entries (message) values ('import started')").update();
        throw new IOException("statement file truncated");
    }
 
    @Transactional(noRollbackFor = IOException.class)
    public void importStatementNoRollback() throws IOException {
        jdbc.sql("insert into audit_entries (message) values ('import started')").update();
        throw new IOException("statement file truncated");
    }

JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.CheckedLab.importStatement]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
JpaTransactionManager: Initiating transaction commit
Lab: committed audit rows: [import started]
JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.CheckedLab.importStatementNoRollback]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,+java.io.IOException
JpaTransactionManager: Initiating transaction commit
Lab: committed audit rows: [import started]

JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.CheckedLab.importStatement]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,-java.lang.Exception
JpaTransactionManager: Initiating transaction rollback
Lab: committed audit rows: []
JpaTransactionManager: Creating new transaction with name [com.example.demo.lab.CheckedLab.importStatementNoRollback]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,+java.io.IOException,-java.lang.Exception
JpaTransactionManager: Initiating transaction commit
Lab: committed audit rows: [import started]

    @Transactional(timeout = 2)
    public void slowQuery() {
        accounts.slowReport(5);
    }
 
    @Transactional(timeout = 2)
    public void slowJavaWithPendingUpdate() {
        Account account = accounts.findById(1L).orElseThrow();
        account.withdraw(new BigDecimal("10.00"));
        sleep(3000);
    }