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.