How to reduce multi inventory, each transaction or only one transaction?

Question

like that method1

@Transactional(rollbackFor = Exception.class)
public void reduce(list) {
    for (someone : list) {
        xxx
    }
}

or method2

public void reduce(list) {
    for (someone : list) {
        reduceOne();
    }
}

@Transactional(rollbackFor = Exception.class)
private void reduceOne(one) {
    xxx
}

which one is better for concurrent system? and why? if choose method2, how to resolve someone reduce fail?

Answer 1

Both have advantages and disadvantages.

The first option creates one transaction for the whole loop, hence if you have 1000 elements in the list:

1. If an exception occurs for the 1000th element, all 999 will not be persisted
2. If an exception does not occur, you gain a performance benefit, because you created only one transaction

For the second option:

1. If an exception occurs for the 1000th element, all 999 will be persisted
2. However you create a transaction for every loop iteration which slows down your program significantly

As far as concurrency is concerned, it all depends on the transaction isolation level of your RBMS.

For example, for Oracle, it is READ_COMMITTED, so in the case of updating the row by one thread, the rest can freely read this row regardless the transaction of the update is finished or not. Hence both options should behave similarly.

For MySql it is REPEATABLE_READ. Therefore the first option potentially locks many rows by one thread, what prevents the rest from reading those locked rows. In that case, the second option may be better.

So as you can see there are many factors and there is no best answer. It depends on the database which you use, on the exceptions frequency and on what stands behind xxx in your methods.