1

I use spring boot + MySQL5 database.

There is a periodic service that runs and need to do the following transaction:

  1. Delete records (with condition)
  2. Insert records

In addition another service does select queries and should see a snapshot of the records without interfering with the delete+insert transactions.

I have the following code:

@Service
public class BulkInsert
{

    public static final String DELETE_ALL_ROWS_QUERY = "DELETE FROM GnsEntity where is_synced = true and was_removed = false";

    @Inject
    private EntityManager entityManager;

    @Transactional
    public void save(List<GnsEntity> gnsEntityList)
    {
        Session session = entityManager.unwrap(Session.class);
        Query entity = session.createQuery(DELETE_ALL_ROWS_QUERY);
        entity.executeUpdate();
        for (int i = 0; i < gnsEntityList.size(); ++i)
        {
            try
            {
                session.persist(gnsEntityList.get(i));
            }
            catch(NonUniqueObjectException nonUniEx)
            {

            }
        }
    }
}

In general it seems to work good.. though a lot of times there's a deadlock exception and I have no clue why..

Thats why I was wondering if my code is relatively fine?

I get the following errors every now and then:

DEBUG","message":"Creating new transaction with name [com.ddd.swiss.microservices.gnssynchronizer.BulkInsert.save]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT","service":"GNSSynchronizer","instanceId":"1","application":"Start","space":"ngampel","class":"org.springframework.orm.jpa.JpaTransactionManager","thread":"pool-3-thread-1","X-B3-TraceId":"5db000bfb3de1a6d49a53edd707419a0","X-B3-SpanId":"49a53edd707419a0"} {"@timestamp":"2019-10-23T07:27:24.318Z","logLevel":"DEBUG","message":"Opened new EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@5a445da1] for JPA transaction","service":"GNSSynchronizer","instanceId":"1","application":"Start","space":"ngampel","class":"org.springframework.orm.jpa.JpaTransactionManager","thread":"pool-3-thread-1","X-B3-TraceId":"5db000bfb3de1a6d49a53edd707419a0","X-B3-SpanId":"49a53edd707419a0"} {"@timestamp":"2019-10-23T07:27:24.318Z","logLevel":"DEBUG","message":"begin","service":"GNSSynchronizer","instanceId":"1","application":"Start","space":"ngampel","class":"org.hibernate.engine.transaction.internal.TransactionImpl","thread":"pool-3-thread-1","X-B3-TraceId":"5db000bfb3de1a6d49a53edd707419a0","X-B3-SpanId":"49a53edd707419a0"} {"@timestamp":"2019-10-23T07:27:24.319Z","logLevel":"DEBUG","message":"Exposing JPA transaction as JDBC transaction [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@241c36b8]","service":"GNSSynchronizer","instanceId":"1","application":"Start","space":"ngampel","class":"org.springframework.orm.jpa.JpaTransactionManager","thread":"pool-3-thread-1","X-B3-TraceId":"5db000bfb3de1a6d49a53edd707419a0","X-B3-SpanId":"49a53edd707419a0"} {"@timestamp":"2019-10-23T07:27:24.319Z","logLevel":"DEBUG","message":"delete from gns_entity where is_synced=1 and was_removed=0","service":"GNSSynchronizer","instanceId":"1","application":"Start","space":"ngampel","class":"org.hibernate.SQL","thread":"pool-3-thread-1","X-B3-TraceId":"5db000bfb3de1a6d49a53edd707419a0","X-B3-SpanId":"49a53edd707419a0"} {"@timestamp":"2019-10-23T07:27:25.451Z","logLevel":"DEBUG","message":"could not execute statement [n/a]","stackTrace":"com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction\n\tat sun.reflect.NativeConstructorAccessorImpl.newInstance0(Nativ

Thanks for the help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nadavgam
  • 2,014
  • 5
  • 20
  • 48
  • Just for information purposes, how long does this service take to run, and when is it being run? – Tim Biegeleisen Oct 23 '19 at 09:27
  • @TimBiegeleisen when the delete+insert is working its about 1 minute.. its running with schedule execution of spring,waiting for 3 min after its done – nadavgam Oct 23 '19 at 09:38
  • Where are the inserts? – Ori Marko Oct 23 '19 at 09:43
  • session.persist() – nadavgam Oct 23 '19 at 09:44
  • You mention your periodic transaction and the read only service, but given that your periodic query is checking the conditions is_synced and was_removed, is there is fact an additional access to the data that is modifying these columns ? – Mark Willis Oct 23 '19 at 09:50
  • in the future yes.. right now not.. so it deletes everything.. – nadavgam Oct 23 '19 at 09:55
  • @TimBiegeleisen it looks okay what I did? given my requirements? – nadavgam Oct 23 '19 at 13:49
  • You are inside a cicle... I suggest to you to flush the session. Let's suppose you have a `batch_size` constant equals to `25` you can do something like this just after the persist(): `if(i==batch_size){session.flush(); session.clear()}` More information can be found here https://stackoverflow.com/questions/41417525/how-can-i-do-massive-insert-using-hibernate – Angelo Immediata Oct 29 '19 at 14:19

0 Answers0