2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

SpringBoot SQLServer+JDBC @Transactional(readOnly=true)が効かない

Last updated at Posted at 2024-09-07

はじめに

SpringBoot SQLServer+JDBCにおいて、@Transactional(readOnly=true)の設定が効かずに、
普通にそのままUpdateされてしまった。

前提知識

Springトランザクションについては下記がすごい分かりやすい

readOnly=trueについて

javadoc
https://spring.pleiades.io/spring-framework/docs/current/javadoc-api/org/springframework/transaction/annotation/Transactional.html#readOnly()

先に結論

このメソッドは、SQL Server 用 Microsoft JDBC ドライバー ではサポートされていません。

SQLServerで試し見る

SQLServer

Docker使用。イメージ下記。(2024/9/7)
mcr.microsoft.com/mssql/server:2019-latest

コード

build.gradle
implementation "org.springframework.boot:spring-boot-starter-jdbc"
implementation 'com.microsoft.sqlserver:mssql-jdbc'
Controller.java
    @GetMapping("/jdbcReadOnlyTest")
    public void jdbcReadOnlyTest() {
        testService.execute();
    }
TestService.java
@Service
@Transactional(readOnly=true) // ←こいつ
public class testService {

// DI等省略

    public void execute() {
        testRepository.queryEexecute();
    }
TestRepository.java
@Repository
public class TestRepository {

// DI等省略

    public void queryEexecute(){
        jdbcTemplate.update("update Testtable set testcolum = '更新'");
    }

結果

  • ふつうに更新されちゃった
testcolum
更新

PostgreSQLで試してみる

build.gradle
implementation "org.springframework.boot:spring-boot-starter-jdbc"
implementation 'org.postgresql:postgresql'

結果

  • ロールバックしてくれた
org.postgresql.util.PSQLException: ERROR: リードオンリーのトランザクションでは UPDATE を実行できません
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:268) ~[postgresql-42.5.1.jar:42.5.1]
        at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119) ~[HikariCP-5.0.1.jar:na]
        at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:na]
        at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:546) ~[spring-jdbc-6.0.4.jar:6.0.4]
        at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:543) ~[spring-jdbc-6.0.4.jar:6.0.4]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ~[spring-jdbc-6.0.4.jar:6.0.4]
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:558) ~[spring-jdbc-6.0.4.jar:6.0.4]
        at com.nbatracker.Repository.MsplayerRepositorySQL.sqlTest(MsplayerRepositorySQL.java:18) ~[main/:na]
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
        at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]

終わり

SQLServer使ってる人は気を付けましょう。
使おうとしてはまっている方がいたらこの記事で救われることを祈ります。

2
1
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?