Back to @Transactional Basics
Q6Question 6 of 7

How does @Modifying + @Transactional work on @Query?

Key Concepts
  • @Modifying tells Spring Data to call executeUpdate() instead of expecting a result set on a @Query UPDATE or DELETE
  • A modifying query must run inside a transaction or you get TransactionRequiredException
  • Bulk queries bypass the persistence context (first-level cache), leaving in-memory entities stale
  • clearAutomatically = true clears the persistence context after the query so subsequent reads fetch fresh data
  • flushAutomatically = true flushes pending in-memory changes to the database before the query runs
  • Bulk queries do not cascade, do not trigger lifecycle callbacks (@PreUpdate), and do not increment @Version

Definition

@Modifying marks a @Query as an UPDATE or DELETE so Spring calls executeUpdate(), and clearAutomatically = true clears the stale persistence context afterward so reads fetch fresh data from the database.
  • @Modifying – tells Spring Data JPA that the @Query is a modifying statement (UPDATE or DELETE) so it should call Query.executeUpdate() and return the affected row count instead of expecting a result set.
  • @Transactional – required because a modifying query changes data; without an active transaction, JPA throws TransactionRequiredException.
  • Stale persistence context – a bulk query runs as SQL directly against the database, bypassing the first-level cache, so entities already loaded into memory become out of date.
  • clearAutomatically – when true, Spring clears the persistence context after the query runs, forcing the next read to fetch fresh data from the database.
  • flushAutomatically – when true, Spring flushes pending in-memory changes to the database before the query runs, ensuring the bulk statement operates on the latest state.

At the National Bank of India, a clerk has photocopies of several account pages on his desk (the persistence context). The manager issues a bulk order straight into the master ledger: "add 1% interest to every savings account." The clerk's photocopies are now out of date. flushAutomatically makes the clerk first write any pending edits into the master ledger before the bulk order runs. clearAutomatically makes the clerk throw away his stale photocopies after the bulk order so that next time he needs an account, he fetches a fresh page.

Understanding the Concepts

What is @Modifying?

@Modifying is a Spring Data JPA annotation placed on a repository method alongside @Query to indicate that the JPQL or native SQL is an UPDATE, DELETE, or INSERT statement rather than a SELECT. Without it, Spring assumes the query returns a result set and calls Query.getResultList() or Query.getSingleResult(), which fails on a modifying statement.

JAVA
package com.bank.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.math.BigDecimal;

public interface AccountRepository extends JpaRepository<Account, String> {

    @Modifying
    @Query("UPDATE Account a SET a.balance = a.balance + :interest " +
           "WHERE a.type = 'SAVINGS'")
    int applyInterest(@Param("interest") BigDecimal interest);
}

The method returns int (the number of affected rows) or void.

Why is @Transactional required?

A modifying query changes data in the database, so it must run inside a transaction. If there is no active transaction, JPA throws javax.persistence.TransactionRequiredException. You can put @Transactional on the repository method itself, but the cleaner practice is to place it on the service method that calls the repository, so the entire business operation runs in one transaction.

JAVA
package com.bank.service;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class InterestService {

    private final AccountRepository accountRepo;

    public InterestService(AccountRepository accountRepo) {
        this.accountRepo = accountRepo;
    }

    @Transactional
    public int applyMonthlyInterest(BigDecimal rate) {
        int updated = accountRepo.applyInterest(rate);
        System.out.println("Interest applied to " + updated + " accounts");
        return updated;
    }
}
TEXT
Interest applied to 12500 accounts

What is the stale persistence context problem?

A bulk modifying query runs directly as SQL against the database and completely bypasses the persistence context (the first-level cache). Hibernate does not know which in-memory entities the bulk statement affected. If you loaded Rajesh's account SAV2001 into the persistence context before the bulk UPDATE, the in-memory copy still shows the old balance. Reading it again in the same transaction returns the stale cached value, not the new database value.

JAVA
package com.bank.service;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class StaleContextDemo {

    private final AccountRepository accountRepo;

    public StaleContextDemo(AccountRepository accountRepo) {
        this.accountRepo = accountRepo;
    }

    @Transactional
    public void demonstrateStaleness() {
        // Step 1: load Rajesh's account (balance = Rs.100,000)
        Account rajesh = accountRepo.findById("SAV2001").orElseThrow();
        System.out.println("Before bulk update: " + rajesh.getBalance());

        // Step 2: bulk update adds Rs.1,000 interest to all savings accounts
        accountRepo.applyInterest(new BigDecimal("1000"));

        // Step 3: read Rajesh's account again
        Account rajeshAgain = accountRepo.findById("SAV2001").orElseThrow();
        // STALE: still shows Rs.100,000 because persistence context cached it
        System.out.println("After bulk update: " + rajeshAgain.getBalance());
    }
}
TEXT
Before bulk update: 100000
After bulk update: 100000

The second read returns 100000 instead of 101000 because Hibernate served the cached copy.

How does clearAutomatically solve staleness?

When clearAutomatically = true, Spring clears the entire persistence context after the modifying query runs. Hibernate forgets all cached entities. The next read of any entity forces a fresh database query.

JAVA
package com.bank.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.math.BigDecimal;

public interface AccountRepository extends JpaRepository<Account, String> {

    @Modifying(clearAutomatically = true)
    @Query("UPDATE Account a SET a.balance = a.balance + :interest " +
           "WHERE a.type = 'SAVINGS'")
    int applyInterest(@Param("interest") BigDecimal interest);
}

Now the same service code produces correct results:

TEXT
Before bulk update: 100000
After bulk update: 101000

The default value of clearAutomatically is false, which is why developers who do not set it get confused by stale reads.

How does flushAutomatically work?

When flushAutomatically = true, Spring flushes any pending in-memory changes to the database before the modifying query runs. This matters when you have made changes to entities in the persistence context that have not been written to the database yet, and your bulk query depends on seeing those changes.

JAVA
package com.bank.service;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class FlushBeforeBulkDemo {

    private final AccountRepository accountRepo;

    public FlushBeforeBulkDemo(AccountRepository accountRepo) {
        this.accountRepo = accountRepo;
    }

    @Transactional
    public void changeTypeAndApplyInterest() {
        // Step 1: change Priya's account type from CURRENT to SAVINGS
        Account priya = accountRepo.findById("SAV3002").orElseThrow();
        priya.setType("SAVINGS");
        // Change is in memory, NOT yet flushed to the database

        // Step 2: bulk update interest for all SAVINGS accounts
        // Without flushAutomatically, Priya's type change is not in the DB yet,
        // so the bulk query misses her account
        int updated = accountRepo.applyInterest(new BigDecimal("1000"));
        // With flushAutomatically=true, Priya's type change is flushed first,
        // and the bulk query correctly includes her
    }
}

The recommended combination for most bulk operations is @Modifying(clearAutomatically = true, flushAutomatically = true): flush pending changes first, run the bulk statement, then clear the cache.

How is a bulk query different from entity-level update?

| Aspect | Bulk @Modifying Query | Entity-Level Update |
|--------|-----------------------|---------------------|
| Execution | Single SQL, direct to DB | Per-entity UPDATE via flush |
| Persistence context | Bypassed | Tracked and flushed |
| Cascading | Not supported | Cascade rules apply |
| Lifecycle callbacks | Skipped (@PreUpdate) | Triggered |
| Optimistic locking | @Version not incremented | @Version incremented |
| Performance | Fast for millions of rows | Slow for large batches |
| Use case | Mass updates (interest, status) | Single entity changes |

JAVA
package com.bank.service;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class UpdateComparisonService {

    private final AccountRepository accountRepo;

    public UpdateComparisonService(AccountRepository accountRepo) {
        this.accountRepo = accountRepo;
    }

    // Entity-level: loads entity, modifies it, Hibernate generates UPDATE on flush
    @Transactional
    public void depositEntityLevel(String accountId, BigDecimal amount) {
        Account account = accountRepo.findById(accountId).orElseThrow();
        account.credit(amount);
        // Hibernate detects the change via dirty-checking and flushes an UPDATE
        // @PreUpdate callback fires, @Version increments
    }

    // Bulk: single SQL UPDATE, bypasses persistence context entirely
    @Transactional
    public int applyInterestBulk(BigDecimal interest) {
        return accountRepo.applyInterest(interest);
        // One SQL statement updates all SAVINGS accounts
        // No @PreUpdate, no @Version increment, no cascade
    }
}

What happens without @Modifying?

Without @Modifying, Spring Data treats the @Query as a SELECT and calls Query.getResultList(). For an UPDATE statement this throws an exception because the database returns an update count, not a result set.

JAVA
package com.bank.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.math.BigDecimal;

public interface BrokenAccountRepository extends JpaRepository<Account, String> {

    // WRONG: missing @Modifying
    @Query("UPDATE Account a SET a.status = 'FROZEN' WHERE a.balance < 0")
    int freezeOverdrawn();
}
TEXT
org.hibernate.hql.internal.QueryExecutionRequestException:
Not supported for DML operations [UPDATE Account a SET a.status = 'FROZEN' WHERE a.balance < 0]

What are common mistakes?

Mistake 1: forgetting clearAutomatically

JAVA
// WRONG: stale reads after bulk update
@Modifying
@Query("UPDATE Account a SET a.status = 'FROZEN' WHERE a.balance < 0")
int freezeOverdrawn();
JAVA
// CORRECT: clear cache after bulk update
@Modifying(clearAutomatically = true)
@Query("UPDATE Account a SET a.status = 'FROZEN' WHERE a.balance < 0")
int freezeOverdrawn();

Mistake 2: expecting @Version to work with bulk queries

JAVA
// Bulk update does NOT increment the @Version field
@Modifying(clearAutomatically = true)
@Query("UPDATE Account a SET a.balance = a.balance + :interest WHERE a.type = 'SAVINGS'")
int applyInterest(@Param("interest") BigDecimal interest);
// If another transaction loaded an entity before this bulk update,
// it will NOT get an OptimisticLockException because the version was not incremented

For operations where version tracking matters, use entity-level updates instead of bulk queries.

What should a senior engineer know?

  • The right combination for most bulk operations is @Modifying(clearAutomatically = true, flushAutomatically = true) plus @Transactional on the service method.
  • Bulk DELETE operations have the same issues: they bypass cascade rules, so related entities are not deleted automatically. Use entity-level removal when cascade is needed.
  • Native queries (using nativeQuery = true) work the same way with @Modifying and have the same stale-context issues.
  • For very large bulk operations (millions of rows), consider running the SQL directly via JdbcTemplate or stored procedures for maximum performance.
  • The TRUNCATE statement is not supported via @Modifying in JPQL; use a native query for it.
JAVA
package com.bank.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

public interface AuditLogRepository extends JpaRepository<AuditLog, Long> {

    // Native query bulk delete with both flags
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query(value = "DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL '90 days'",
           nativeQuery = true)
    int purgeOldLogs();
}

Visual Overview

DIAGRAM
  Service method (@Transactional)
       │
       ├── 1. Load Rajesh account → Persistence Context caches it
       │
       ├── 2. flushAutomatically=true → flush pending changes to DB
       │
       ├── 3. @Modifying @Query runs bulk UPDATE directly in DB
       │       (bypasses persistence context entirely)
       │
       ├── 4. clearAutomatically=true → clear persistence context
       │
       └── 5. Read Rajesh account → fetches FRESH data from DB

  ┌─────────────────────────┐     ┌─────────────────────┐
  │  Persistence Context     │     │  Database            │
  │  (First-Level Cache)     │     │                     │
  │                          │     │  Rajesh: 100,000    │
  │  Rajesh: 100,000 (stale) │ ──► │  Bulk UPDATE +1000  │
  │  CLEARED after bulk      │     │  Rajesh: 101,000    │
  │  Rajesh: 101,000 (fresh) │ ◄── │                     │
  └─────────────────────────┘     └─────────────────────┘

Key Takeaway

When writing a custom @Query in Spring Data JPA that performs an UPDATE or DELETE, the @Modifying annotation is required so Spring calls executeUpdate() and returns the affected row count instead of expecting a result set. The query must run inside a transaction, so the service method needs @Transactional, or you get a TransactionRequiredException.

The subtle part that interviewers probe is cache coherence. A bulk modifying query runs as SQL directly against the database and completely bypasses the persistence context. If Rajesh's account SAV2001 was loaded before the bulk UPDATE that adds Rs.1,000 interest to every savings account, the in-memory copy still shows the old balance. Reading it again in the same transaction returns Rs.100,000 instead of Rs.101,000 because Hibernate serves the cached copy.

The fix is the two attributes on @Modifying. clearAutomatically set to true clears the persistence context after the bulk query, forcing subsequent reads to fetch fresh data from the database. flushAutomatically set to true flushes pending in-memory changes before the bulk query, ensuring the SQL operates on the latest state. The recommended combination for most bulk operations is both flags set to true. The tradeoff is that bulk queries bypass the entity layer entirely: no cascade, no @PreUpdate callbacks, no @Version increment. For a handful of entities, use entity-level updates. For millions of rows, the bulk modifying query is the right tool.

Remember one sentence: @Modifying marks a @Query as UPDATE or DELETE, and clearAutomatically = true is essential to prevent stale reads from the persistence context after the bulk operation.

---

Share
Use ← → arrow keys to navigate