CODEWITHSUNDEEP
mysqlhibernatespring-data-jpaspring-transactionsautocommit
Ronak Patel
Ronak Patel

Reputation: 3849

avoid commit queries from spring jpa repository

spring @Transactional is issuing commit query, when I changed the propagation to NEVER or SUPPORTS - commit query stopped but insert(with propagation REQUIRES_NEW) to the table is not immediately available for other read only query/thread. It is available after some milliseconds of lag. is this expected behavior? one thread writing to table using proportion = REQUIRED, but other thread can not see the record using prorogation NEVER or SUPPORTS when mysql auto commit is ON at db server settings.

2023-08-14T21:04:35.329619Z  1028 Query set session transaction read only
2023-08-14T21:04:35.329818Z  1028 Query SET autocommit=0
2023-08-14T21:04:35.330616Z  1028 Query select c1_0....<from table1>
2023-08-14T21:04:35.331177Z  1028 Query select f1_0....<from table2>
2023-08-14T21:04:35.331687Z  1028 Query commit

Example Code:

//one thread creating db rec using this
@Transactional(transactionManager = MYSQL_TRANSACTION_MANAGER, propagation = Propagation.REQUIRES_NEW)
public void persist(Foo foo) throws Exception {
        em.persist(foo);
    }


//other thread reading rec inserted by above method - this read is after over method completion - not concurrent - but possibly different db connection
@Transactional(readOnly = true,
        transactionManager = MYSQL_TRANSACTION_MANAGER,
        propagation = Propagation.SUPPORTS) //or NEVER
public Foo find(long id){
        //String sql = ...;
        Query q = em.createQuery(sql);
        return q.getFirstResult();
    }

Upvotes: 0

Views: 276

Answers (0)

Related Questions