TopLink JPA EntityManager flush and Oracle on update trigger

Question

We have one table Product in Oracle database which is having two triggers on it only for auditing purpose (legacy implementation).

1. For insert or update
2. on Update

Sometimes when we are merging an entity for an update of this table. it stuck at flushing and it consumes time ~2-3 seconds. This behaviors we can not produce in development environment and we are not much sure what is causing this delay. Before we have not placed EntityManager.flush() so flush mode was auto. But it was getting blocked on any subsequent select statement. Then we tried to add flush at the time of merge itself.

public class ProductDAO{
     private EnitiyManager em;
     public void updateProduct(ProductDTO productDTO){
         Product product = em.findById(Product.class, productDTO.getId());
         product.setModifiedDate(new Date());
         long start = System.currentTimeMillis();
         em.merge(product);
         em.flush(); // This call get blocked by DB
         System.out.println("Time taken (ms): " + (start - System.currentTimeMillis());
     }
}

The generated SQL for this update is very simple and we are struggling to find out what is causing this much delay.

UPDATE PRODUCT SET MODIFIED_DATE = ? WHERE PRODUCT_ID = ?

Environment Details

• JPA 2
• Toplink
• Oracle
• 3.5 million records in PRODUCT table

Issue is resolved as the trigger was doing some table scan based on user who is modifying the Product. PL/SQL fix resolved this issue.