why do we have to use @Modifying annotation for queries in Data Jpa

CAUTION!

Using @Modifying(clearAutomatically=true) will drop any pending updates on the managed entitites in the persistence context spring states the following :

Doing so triggers the query annotated to the method as an updating query instead of a selecting one. As the EntityManager might contain outdated entities after the execution of the modifying query, we do not automatically clear it (see the JavaDoc of EntityManager.clear() for details), since this effectively drops all non-flushed changes still pending in the EntityManager. If you wish the EntityManager to be cleared automatically, you can set the @Modifying annotation’s clearAutomatically attribute to true.

Fortunately, starting from Spring Boot 2.0.4.RELEASE Spring Data added flushAutomatically flag (https://jira.spring.io/browse/DATAJPA-806) to auto flush any managed entities on the persistence context before executing the modifying query check reference https://docs.spring.io/spring-data/jpa/docs/2.0.4.RELEASE/api/org/springframework/data/jpa/repository/Modifying.html#flushAutomatically

So the safest way to use @Modifying is :

@Modifying(clearAutomatically=true, flushAutomatically=true)

What happens if we don't use those two flags??

Consider the following code :

repo {
   @Modifying
   @Query("delete User u where u.active=0")
   public void deleteInActiveUsers();

}

Scenario 1 why flushAutomatically

 service {
        User johnUser = userRepo.findById(1); // store in first level cache
        johnUser.setActive(false);
        repo.save(johnUser);

        repo.deleteInActiveUsers();// BAM it won't delete JOHN
        
        // JOHN still exist since john with active being false was not 
        // flushed into the database when @Modifying kicks in
    }

Scenario 2 why clearAutomatically In following consider johnUser.active is false already

service {
       User johnUser = userRepo.findById(1); // store in first level cache
       repo.deleteInActiveUsers(); // you think that john is deleted now 
       System.out.println(userRepo.findById(1).isPresent()) // TRUE!!!
       System.out.println(userRepo.count()) // 1 !!!
       
       // JOHN still exist since in this transaction persistence context
       // John's object was not cleared upon @Modifying query execution, 
       // John's object will still be fetched from 1st level cache 
       // `clearAutomatically` takes care of doing the 
       // clear part on the objects being modified for current 
       // transaction persistence context
}

So if - in the same transaction - you are playing with modified objects before or after the line which does @Modifying, then use clearAutomatically & flushAutomatically if not then you can skip using these flags

BTW this is another reason why you should always put @Transactional annotation on service layer, so that you only can have one persistence context for all your managed entities in the same transaction. Since persistence context is bounded to hibernate session, you need to know that a session can contain couple of transactions see this answer for more info https://stackoverflow.com/a/5409180/1460591 The way spring data works is that it joins the transactions together (aka transaction isolation) into one transaction (default isolation (required)) see this answer for more info https://stackoverflow.com/a/25710391/1460591

To connect things together if you have multiple isolated transactions (e.g not having a transactional annotation on the service) hence you would have multiple sessions following the way spring data works hence you have multiple persistence contexts that mean you might delete/modify an entity in a persistence context even with using flushAutomatically the same deleted/modified entity might was fetched and cached in another transaction's persistence context already that would cause wrong business decisions due to wrong or un-synced data

This will trigger the query annotated to the method as updating query instead of a selecting one. As the EntityManager might contain outdated entities after the execution of the modifying query, we automatically clear it (see JavaDoc of EntityManager.clear() for details). This will effectively drop all non-flushed changes still pending in the EntityManager. If you don't wish the EntityManager to be cleared automatically you can set @Modifying annotation's clearAutomatically attribute to false;

for further detail you can follow this link:-

http://docs.spring.io/spring-data/jpa/docs/1.3.4.RELEASE/reference/html/jpa.repositories.html

Queries that require a @Modifying annotation include INSERT, UPDATE, DELETE, and DDL statements.

Adding @Modifying annotation indicates the query is not for a SELECT query.