Spring Data JPA isolationlevel does not affect on SQLServer queries

70 Views Asked by At

I have a @Service layer class with several methods, which have @Transactional(isolation = Isolation.READ_UNCOMMITTED) annotated queries and @Transactional annotated write transactions. Sometime deadlock appears on transactions and I can see in SQLServer Profiler deadlock analysis XML, the queries have READ_COMMITED isolation level. How can be isolationlevel setting changed over the transactions? Any help welcomed.

There are repositories like this:

@Repository
public interface mStorageRepository extends AdBaseClientOrgRepository<mStorage, Long>  {
    @Modifying
    @Query("UPDATE mStorage s SET s.qtyonhand = s.qtyonhand + ?2, s.updated = current_timestamp WHERE s.id = ?1")
    void updateqtyBymStorageid(long id, BigDecimal movementqty);

    @Query("SELECT s FROM mStorage s WHERE s.locator.id = ?1 AND s.stockAlert = ?2 order by s.datelastinventory")
    Iterable<mStorage> getByLocatorAndStockAlert(long mLocatorId, String stockAlert);
.
.
.
}

There are date service layers like this:

    @Service
    public class mStorageService extends AdBaseClientOrgService<mStorage, mStorageRepository> {
    
        @Autowired
        public mStorageService(Logger logger, TimeService timeService, MessageService messageService) {
            super(logger, timeService, messageService);
        }
    
        @Override
        @Autowired
        public void setRepo(mStorageRepository repo) {
            this.repo = repo;
        }
    
    
        @Transactional
        public void updateqtyBymStorageid(long id, BigDecimal movementqty) {
            logger.debug("start updateqtyBymStorageid : {} , {}",id,movementqty);
            repo.updateqtyBymStorageid(id, movementqty);
            logger.debug("end updateqtyBymStorageid : {} , {}",id,movementqty);
        }
    
    
        @Transactional(isolation = Isolation.READ_UNCOMMITTED)
        public Iterable<mStorage> getByLocatorAndStockAlert(long locatorId, StockAlert stockAlert) {
            Iterable<mStorage> ret = this.repo.getByLocatorAndStockAlert(locatorId, stockAlert.toString());
            getLazyProperties(ret);
            return ret;
        }
.
.
.

}

There are several business logic service layer implementations also:

@Service
public class BaseProductionPartsUsageService {

    @Autowired
    public BaseProductionPartsUsageService(final Logger logger) {
        this.logger = logger;
        logger.info("BaseProductionPartsUsageService initialized");
    }

    protected Logger logger;


    @Autowired
    public void setStorageService(mStorageService storageService) {
        this.storageService = storageService;
    }

    protected mStorageService storageService;

// calls several data service methods in method annotated with @Transactional
.
.
.
 }

Sometimes we have deadlock and when we check the details in SQLServer profiler we can see that the query method, which was annotated by READ_UNCOMMITED is READ_COMMITED in profiler xml.

0

There are 0 best solutions below