mysql autocommit vs managers and daos insert methods

Question

I've searched through the net but no answer so far, at least no clear answer.
Suppose you are in the following situation

@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
private void usingManagerTest()
{
    List<SomeType> someList = someDao.findAll();
    for (SomeType listItem : someList )
    {
         someManager.create();
    }
}

where someManager.create() set the fields of an entity, say someEntity, and then calls someDao.create(someEntity).
Mysql logs shows that for every iteration in the for, the following mysql queries are performed:

set autocommit = 0 
insert into ... 
commit

Now suppose you are in the following situation:

@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
private void usingDaoTest()
{
    List<SomeType> someList = someDao.findAll();
    for (SomeType listItem : someList )
    {
         SomeEntity someEntity = someManager.createEntity();
         someDao.create(someEntity);
    }
}

where the createEntity method calls some setters on a java entity, and the create is performed by the DAO. This lead to a mysql log like the following:

set autocommit = 0
insert into ...
insert into ...
insert into ...
...
commit

where the number of insert query is the number of iteration in the for cycle.

I've read the spring documentation but so far it is not clear to me why this happens.
Anyone that could explain this behaviour?

Thanks

P.S. I know that the title is not clear, any suggestion is welcomed.

UPDATE: it seems that it works differently from what I've said: the log resulting from running usingDaoTest() does not shows at all the autocommit query (that is no good for me).
I'm still interested in understanding why the two scripts work differently, but now I'm interested also in understanding how to achieve the second log result (where all the operation in the for loop are executed between autocommit = 0 and commit).

Thanks again

UPDATE2: after some other test I've understood a bit more the logic behind the @Transactional, so I've performed a more specific research, founding a solution here.
This discussion can be considered closed, thanks to all.

score 0 · Answer 1 · answered Oct 01 '14 at 08:29

0

MySQL will perform the operations for you while your transaction is running. (reason why autocommit is set to 0) After you commit your transaction, all changes will be effectively performed on the database tables that are visible to other transactions.

This is the normal situation. However there is a possibility to define transactions where the changes performed are directly visible to other transactions. This has its up- and downsides.

answered Oct 01 '14 at 08:29

Kurt Du Bois

7,550
4
25
33

Thanks, but still I don't get the reason. I don't call an explicit commit calls, I just call a dao create: first time through a manager and second time directly in the loop. The create should call the commit using hibernate and in both case this happen in a loop, so why they act differently? Plus both method are defined as propagation.requires_new so they should use the same transaction for the loop. I've omitted that also the dao.create() has a @transactional annotation but propagation.required. – lupin_85 Oct 01 '14 at 09:07

mysql autocommit vs managers and daos insert methods

1 Answers1