0

For the life of me, I'm not able to see what's wrong with the code causing the update query to get stuck. I tested the query directly on the database and it's fine.

I can confirm the application can connect to the database. The application can start fine. I can execute a findBy query just fine. It's just the update query that's stuck.

Repository class:

@Repository
public interface TestRepository extends CrudRepository<Test, String> {
    // Tried with @Modifying(flushAutomatically = true, clearAutomatically = true).  Still no luck.
    @Modifying
    @Query("UPDATE Test SET stopAllPayment = 'Y' WHERE location = 'london'")
    int stopAllPayment();
}

Service class

@Service
@RequiredArgsConstructor
public class StopPaymentService {
    private final TestRepository testRepository;

    @Transactional
    public void run() {

        // Stuck here.  Blocking.
        // In debug mode, the thread is waiting at SocketDispatcher.read0()
        testRepository.stopAllPayments();  
    }
}

Main class

@SpringBootApplication
@RequiredArgsConstructor
@EnableTransactionManagement
public class Main implements CommandLineRunner {
    private final StopPaymentService service;

    @Override
    public void run(String... args) throws Exception {
        service.run();
    }

    public static void main(String[] args) {
        SpringApplication.run(Main.class, args);
    }
}

application.yml

spring:
  datasource:
    url: jdbc:oracle:thin:@dbdev:1521/LT110
    username: user
    password: password
    driver-class-name: oracle.jdbc.OracleDriver
  jpa:
    hibernate:
      ddl-auto: none

pom.xml

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
        <relativePath />
    </parent>

   
    <properties>
        <java.version>1.8</java.version>
    </properties>


    <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-actuator</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>

        <!-- Oracle JDBC driver -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>21.1.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.4.5</version>
        </dependency>
    </dependencies>
</project>
His
  • 5,891
  • 15
  • 61
  • 82
  • it seems that your application can't connect to the database. Make sure your database connection properties are correct. Please, provide application.properties and pom.xml. It is not clear, which database you are using. – Mykhailo Skliar May 15 '21 at 16:46
  • @MykhailoSkliar, I added the application yml and pom.xml. The app can execute find query just fine. It's the update query that's an issue. – His May 15 '21 at 16:56
  • 1
    check for locks on the database. – Jens Schauder May 15 '21 at 16:57
  • you should turn on this property to see what query is being executing that is causing your issue of being stuck `spring.jpa.show-sql=true` – duppydodah May 15 '21 at 16:59
  • 1
    Verify this [link](https://stackoverflow.com/questions/17121620/spring-data-jpa-update-query-not-updating), i am thinking may this will helps you! – AnilkumarReddy May 15 '21 at 17:01
  • @JensSchauder, already checked. Nothing is locking the database. I have a nodejs application executing the same update query fine. It's something to do with the spring boot app. – His May 15 '21 at 17:04
  • As per the issue, the reason will be the application is executing a SQL statement (a query or an update) against Oracle db and is waiting for a response from the database server. By default, the thread will continue to wait indefinitely until it receives a response. This means that the thread could be hung in this state for a very long time if the SQL statement is very complex and takes a long time to complete, if the database is performing very slowly, if the db server becomes unresponsive, or if there is a problem in the network connection between the application server and the db. – AnilkumarReddy May 15 '21 at 17:04
  • @AnilkumarReddy, nothing is majorly complex in my query. Take a look. It's a simple update query. The fact that my nodejs app can execute it tells me I'm missing something in my spring boot app. – His May 15 '21 at 17:06
  • Where exactly do you call StopPaymentService.run() method? I don't see any code in your example. SpringApplication.run() has nothing to do with StopPaymentService.run() – Mykhailo Skliar May 15 '21 at 17:12
  • @MykhailoSkliar, updated the question. It's in Main.java. – His May 15 '21 at 17:23
  • @His Yes, now I see it. But do you still have the issue? If so, check my answer anyway, maybe you need to extend SpringBootServletInitializer, like in my example. – Mykhailo Skliar May 15 '21 at 17:25
  • 1
    If it is not waiting for the database, it waits on something else. Use the debugger to find the exact code location and stack trace where it is waiting. Look at the threads to see what it is waiting for. – Jens Schauder May 16 '21 at 05:03
  • @JensSchauder For me it also seems to be the threading issue. For example, if transaction manager is in one thread and command line runner in another thread, then they might be blocking each other or work incorrectly. This is why I suggest to extend SpringBootServletInitializer (see my answer). This might solve the threading issue. – Mykhailo Skliar May 16 '21 at 14:54
  • @MykhailoSkliar you should elaborate on that in your answer, because right now I have no idea why you think your answer helps. – Jens Schauder May 16 '21 at 18:16
  • 1
    @Jens Schauder, turned out you're right. It's a database lock issue. Do you want to add this as your answer for me to approve? – His May 24 '21 at 07:31

2 Answers2

1

When find method work and only the update hangs, the most likely explanation is a lock on the database.

Run the update, then check your database for blocking locks. The details of how to do that are database specific.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
0

According to this article: https://howtodoinjava.com/spring-boot/command-line-runner-interface-example/

correct code for spring boot application, which implement CommandLineRunner, is the following:

@SpringBootApplication
public class SpringBootWebApplication extends SpringBootServletInitializer implements CommandLineRunner {

    @Autowired
    private StopPaymentService stopPaymentService;
 
    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
        return application.sources(SpringBootWebApplication.class);
    }
 
    public static void main(String[] args) throws Exception {
        SpringApplication.run(SpringBootWebApplication.class, args);
    }
 
 
    @Override
    public void run(String... args) throws Exception {
        stopPaymentService.run();
        logger.info("Application Started !!");
    }
}
Mykhailo Skliar
  • 1,242
  • 1
  • 8
  • 19