Hibernate returns old data from MySQL database but not from Oracle database

Question

I used a function called ‘assignSection(SectionBean bean)’ which was used to add or remove a section from my WB_USERROLE_SECTION table. In order to check the current section items assigned for a given user role and an institute in the WB_USERROLE_SECTION table, I called another function named as “getSectionList(String userRole, String institute)” each time after calling ‘assignSection(SectionBean bean)’ function. My ‘assignSection(SectionBean bean)’ function worked fine, but the issue was with 'getSectionList(String userRole, String institute)' function.

Here is the code of “getSectionList(String userRole, String institute)” function.

public List<Section> getSectionList(String userRole, String institute) throws Exception {
    List<Section> sectionList = new ArrayList<Section>();
    Session session = null;
    try {
         session = HibernateInit.sessionFactory.openSession();
         String sql = "from Section as t where t.status.statuscode=:status and t.institute=:institute and sectioncode in (select mp.id.sectioncode from Userrolesection mp where mp.id.userrolecode=:userrolecode and mp.institute=:institute ) order by lower(t.description) asc";
         Query query = session.createQuery(sql).setString("status", "1")
                        .setString("userrolecode", userRole)
                        .setString("institute", institute);
         sectionList = (List<Section>) query.list();
     }catch(Exception e) {
         throw e;
     } finally {
         try {
             session.flush();
             session.close();
         }catch(Exception e) {
             throw e;
         }
     }
    return sectionList;
}

Here is the issue,

This function worked fine for Oracle database and returned the correct list of names assigned for a given userrole and an institute of the WB_USERROLE_SECTION table that moment. But when I used this function for MySQL database sometimes it worked fine, but sometimes it returned old data which was different from the data in the WB_USERROLE_SECTION table at that moment.

For example,

‘assignSection(SectionBean bean)’ function, removes section named as “sample_section ”. But when I called getSectionList(String userRole, String institute)” function to check the list of current section items, it contains “sample_section ” as well though it has been removed from the table WB_USERROLE_SECTION.

---

I feel like the data list is returned from a cache, but I have used,org.hibernate.cache.NoCacheProvider in my hibernate.cfg.xml and I have not enabled query caching for this application according to my knowledge.

I found a solution for this and I did this operation within a transaction and it returned me the correct data list. Also I just gave a try by using stateless sessions and it also returned me correct data.

What I want to know is “What could be the reason for this behavior?” working fine for oracle, but not working fine for MySQL? According to my knowledge you don’t need to use a transaction to retrieve data from the database, but here I had to use a transaction to get correct data ?

Here is the mapping class "Section"

@Entity
@Table(name = "WB_SECTION"
)
public class Section implements java.io.Serializable {

    private String sectioncode;
    private String description;
    private Status status;
    private Institute institute;
.
.
.
}

Here is the mapping class "Userrolesection"

@Entity
@Table(name = "WB_USERROLE_SECTION"
)
public class Userrolesection implements java.io.Serializable {

    private UserrolesectionId id;
    private Section section;
    private Userrole userrole;
    private Institute institute;
.
.
. 
}

Here is the mapping class "UserrolesectionId"

@Embeddable
public class UserrolesectionId  implements java.io.Serializable {

     private String sectioncode;
     private String userrolecode;
.
.
. 
}