Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › JPA › Queries

Bulk update and delete operations

After one UPDATE changes many rows, why does the entity still have the old value?

An UPDATE or DELETE run with executeUpdate (a bulk update or delete operation) changes many rows with one SQL statement. But this SQL runs directly on the database without going through the persistence context. So an entity that you already loaded does not know that the database has changed.

Checked against real execution · 66 combinations were run on Hibernate 7.4.5 and H2, and the kind and order of the SQL, the value read, the value in the DB, and the exceptions were checked against the results

Concepts in this lab

Bulk update and delete operations
A way to change or delete many rows at once with a single UPDATE or DELETE statement, instead of changing entities one by one. You build the query in JPQL and run it with executeUpdate(), and the number of rows changed is returned. Changing entities one by one with dirty checking sends as many SQL statements as there are rows, but a bulk operation needs only one. However, this SQL runs directly on the database without going through the persistence context, so entities you already loaded keep their old values. So you run the bulk operation before you load the entities, or you empty the persistence context with em.clear() after it.
em.clear and em.refresh
Two ways to bring the persistence context back in line with the database when they disagree. em.clear() empties the whole persistence context, so later queries read fresh values from the database. The entity objects you held before emptying become detached, so changing their values is not applied to the database. em.refresh(entity) reads only the entity you specify again from the database and overwrites the values of that object. If you call clear while there are changes that have not been sent to the database, those changes are lost.
Persistence context
The logical space in JPA that holds and manages entities. Entities that you load or save are kept here, and JPA tracks the changes made to them and applies those changes to the database when the transaction ends. It usually shares its lifecycle with one transaction (in a web request with Open Session in View (OSIV) turned on, it lasts until the request ends). Features such as the first-level cache, dirty checking, and transactional write-behind work on top of it.
First-level cache
The store inside the persistence context that keeps entities by identifier. If you load the same identifier again within the same transaction, it returns the stored entity without sending a query to the database. As a result, entities with the same identifier are always the same object within the same transaction. By default it disappears when the transaction ends (in a web request with Open Session in View (OSIV) turned on, it lasts until the request ends), so it is different from an application-wide cache.
Transactional write-behind and flush
Transactional write-behind means that INSERT, UPDATE, and DELETE statements are not sent to the database right away. They are collected and sent together. However, for an entity whose primary key is generated with the IDENTITY strategy (such as AUTO_INCREMENT in MySQL), the INSERT runs immediately when you request the save, because the identifier must be obtained. A flush sends the collected changes to the database. It happens automatically just before the transaction commits and just before a JPQL query runs. A flush only sends SQL to the database. It does not commit the transaction.

Try these

Running with the default settings

  1. getResultList(): One SELECT reads 3 items and keeps them in the persistence context. The price of item 1 is 1000.
  2. executeUpdate(): One UPDATE statement raises the price of 3 rows in the database by 500 at once. The return value is the number of rows changed, 3. This SQL does not go through the persistence context, so the entities it holds stay as they are. Item 1 is 1500 in the database and 1000 in memory.
  3. first.getPrice(): Reads 1000 from the entity you already hold. It differs from the value in the database, 1500.
  4. The method ends and commits: There is no more SQL to send. It commits as it is.

Result of this run

Code

@Entity
class Item {
    @Id Long id;
    String name;
    int price;
    int getPrice() { return price; }
    void setPrice(int price) { this.price = price; }
}

// Inside a transaction. The DB has 3 items (item 1 costs 1000, item 2 costs 2000, ...)
List<Item> items = em.createQuery("select i from Item i order by i.id", Item.class).getResultList();
Item first = items.get(0);                 // item 1

int count = em.createQuery("update Item i set i.price = i.price + 500")
        .executeUpdate();                  // bulk UPDATE

System.out.println(first.getPrice());
// commit when the method ends

Assumptions of this model

This lab assumes one simple entity, Item, whose id is set directly in the code, run in one transaction and one persistence context. All 66 combinations of 3 methods, 5 ways of reading, whether there is an unflushed change, and the item counts 2, 3, and 8 (the "read" setting is not used for "Change the entities one by one") were run on real Hibernate 7.4.5 and an H2 database, and the kind and order of the SQL sent, the return value of executeUpdate, the value read, the value in the DB after it ended, and the exception were confirmed to match the screen. The SQL on the screen is shortened to be easy to read. The flush just before the bulk operation is the behavior under the default setting (FlushMode AUTO). The exception when refreshing a deleted row was Hibernate's UnresolvableObjectException in this environment (the JPA documentation lists EntityNotFoundException for this case). Spring Data JPA's @Modifying(clearAutomatically = true) was not run in this environment, and the explanation follows the documentation. A bulk UPDATE on an entity with @Version, the relationship between bulk operations and the second-level cache, and bulk operations run as native SQL are not covered.

Previous lab: JPQL and fetch joins · Next lab: Spring Data JPA

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these