@Transactional don't commit

Question

I had a problem with a long transaction while working.

Because of the long transaction, any update queries made MySqlTimeoutException.

The query that had a problem is similar to the following.

-- fileDAO.updateFile
-- file_id is PK. Column 'related_board_id' doesn't have any index and type is int.

UPDATE file SET related_board_id = 1 WHERE file_id = 1

The code that had a problem is similar to the following.

@Service
public class FileService {
    @Autowired
    private FileDAO fileDAO;
    
    // I have PlatformTransactionManager bean. Transactional has default settings.
    @Transactional
    public void saveFileList(List<File> fileList) {
        for (File file: fileList) {
            fileDAO.updateFile(file);
        }
    }
}

We used Spring 4.3.x.

At the time, 1200 files were being stored. There was no error log about that transaction, but the transaction didn't commit.

I think that the cause of the long transcation is saving too many files, but I can't understand why the problem occurred without commit in this transaction even though there was no error log.

Why did that long transaction occur?

If my opinion(the cause is saving too many files) is correct, why the problem occurred without commit?

Answer 1

Instead of using your customized DAO class use JpaRepository and call saveAll method.

@Repository
public interface FileRepository extends JpaRepository<File, Long> {
    
}


@Service
@Transactional
public class FileService {

    private final FileRepository fileRepository;

    @Autowired
    public FileService(FileRepository fileRepository) {
        this.fileRepository = fileRepository;
    }

    public void saveAll(List<Long> fileIds) {
        List<File> files = fileRepository.findAllById(fileIds);
        //change the data of file objects
        fileRepository.saveAll(files);
    }
}