I'm using Spring @Transactional for multiple table inserts inside a single function.

For each entity's read/write I'm using EntityManager,lets suppose that in my function 10 tables are getting updated with data then in that case all 10 tables are locked till the transaction is not over , which is user experience wise bad since it causes waits\ delays for the user where they are viewing few pages which uses these tables.

So how can I prevent the locking of tables for read while the whole insert table process is taking place, Is there a way to avoid using transaction and do single table independent insert. Below is code snippet

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_UNCOMMITTED, noRollbackFor = {SQLException.class, IllegalStateException.class, PersistenceException.class})
    public BaseImportDtoRoot importData(BaseImportDtoRoot baseDto) throws Exception {
try{
table1.fninsert(); .. call each class to insert entity wise
table2.fninsert();
}
catch(Exception e){
}
}
public class table1(){
fninsert(){
MstTable tb1= new MstTable ();

                tb1= modMap.map(MstTableDto,
                        MstTable.class);
                
                entityManager.persist(tb1);
    entityManager.flush();
        entityManager.clear();
}

CodePudding user response：

It would be glad, if you can provide us a code snippet, in order to know exactly your state.

CodePudding user response：

Is there a way to avoid using transaction

You can not, transactions are created whenever you persist data, so you need to either:

1. Use @Transactional, which wraps your DML inside a transaction

2. Create your transaction manually

   entityManager.getTransaction().begin(); entityManager.persist(data); entityManager.getTransaction().commit();

and do single table independent insert

I think what you mean is to create separate transactions for each insertion. You could do that by creating multiple methods which the annotation @Transactional and removing the @Transaction annotation on the importData method. (by doing so the importData method is not atomic anymore)

Please correct me if I misunderstand anything.