Spring @transactional does not work in repository

Question

I have simple repository, which is annotated with @Transactional. And for ORM implementation i'm using SQL2O.

I have very specific queries, in one method I do in trasaction DELETE then UPDATEand then commit() after all retrieving data back by SELECT query. So the problem is in some cases it returns wrong result values. By this I mean the last inserted data is not in result value because it isn't already been committed.

Example of method in Repository class:

@Transactional
@Repository
@Slf4j
public class WishListRepositoryImpl implements WishListRepository {

    @Autowired
    private Sql2o sql2o;

    @Override
    public LinkedHashSet<String> deleteThenUpdateAndReturnFavorites(final String delete, final String update,
                                                                    final String crmId) {
        try (Connection connection = sql2o.beginTransaction()) {
            connection.createQuery(DELETE_FAVORITES_QUERY)
                    .addParameter(MATERIALS_JSON, delete)
                    .addParameter(CRM_ID, crmId)
                    .executeUpdate();
            connection.createQuery(ADD_NEW_FAVORITES_QUERY)
                    .addParameter(MATERIALS_JSON, update)
                    .addParameter(CRM_ID, crmId)
                    .executeUpdate();
            connection.commit();
            return new LinkedHashSet<>(connection.createQuery(FIND_ALL_CLIENT_FAVORITES_QUERY)
                    .addParameter(CRM_ID, crmId)
                    .executeAndFetch(String.class));
        }
    }

I need that the result value should be after success commit() in one transaction.

Answer 1

I have solved my problem with Lock:

  some method(){
    locker.lock()
    try (Connection connection = sql2o.beginTransaction()) {
            do some execute..
            connection.commit()
        } finaly{ locker.unlock()}
    }