Isolation level not working in spring boot

Question

Recently I started working with isolation levels, but specifying isolation level in @Transactional annotation does not seems to work in spring boot. I tried a lot of different things but I cannot get it to work, below is my code.

@Transactional(isolation=Isolation.READ_UNCOMMITTED)
 public  void updateWalletAddresses(List<RegisterWallet>      walletMetaCollection) throws Exception{
    Transaction tx =null;
    Session session =null;
    if(sessionFactory == null){
        return;
    }   
    session = sessionFactory.openSession();     
    try{
       String sql = "select * from user";   

       SQLQuery query = session.createSQLQuery(sql);
       query.addEntity(User.class);
       List<User> userlist=query.list();
       int i=0;
       while(i<userlist.size()){
        System.out.println(userlist.get(i).getEmail());
        i++;
    }

    }catch(Exception e){

        throw e;
    }
}

before executing the above walletservice method I am starting a transaction in mysql client but I am not committing it so that I have dirty data. After that I executed above code but it does not print uncommitted data even though I specified transaction read uncommitted. code for starting transaction in mysql is

set autocommit=0;
start transaction;
insert into user (name,email,password,roleid,username) 
values("prashank","[email protected]","password",1,"prashank");

Note: as I am not committing the transaction then above insert cause dirty read problem. I am not able to read uncommitted data Note: Similarly any other isolation level are not working . Please help

Are you sure that transactions are properly enabled in your app? Per here: the mere presence of the @Transactional annotation is not enough to activate the transactional behavior. The @Transactional annotation is simply metadata that can be consumed by some runtime infrastructure that is @Transactional-aware and that can use the metadata to configure the appropriate beans with transactional behavior. — stephen.vakil
yes transactions are enabled spring boot will auto enable them — Thinker
I still cross checked whether transaction are enabled or not by inserting a user in and then manually throwing an exception. Inserted User is rolled back successfully. i.e transaction is working — Thinker
Any link for changing isolation level in "Spring-Boot" will be highly appreciated. — Thinker
You should also not be doing openSession but use currentSession instead. When opening a new session you might even get a new transaction, rendering the @Transactional useless. (With openSession you are opening a new session outside the scope of spring, you aren't closing it so you are also leaking connections). — M. Deinum

ExploreEv ExploreEv · Accepted Answer · 2020-02-09T19:15:43

Double check your DB engine with

SHOW TABLE STATUS LIKE 'table_name';

If is MyISAM, then transactional is not supported, use below to create InnoDB, which support transaction, then your case should work

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL55Dialect

Also I don't think

set autocommit=0;

is required bcz start transaction will automatically disable it

Isolation level not working in spring boot

1 Answers