0

Both (a) transaction isolation levels and (b) JPA lock modes are enforced using DBMS' locks on tables/rows. What will happen if I set (a) not to check any locks and (b) to check some, or vice versa? Which takes precedence?

Example (using Spring):

@Transactional(isolation = Isolation.READ_UNCOMMITTED)
public void incrementVal(int primaryKey) {      
    SomeEntity e = entityManager.find(
        SomeEntity.class, primaryKey, LockModeType.PESSIMISTIC_WRITE);
    int val = e.getVal();
    e.setVal(val + 1);
}

Or, reversing the constraints:

@Transactional(isolation = Isolation.SERIALIZABLE)
public void incrementVal(int primaryKey) {        
    SomeEntity e = entityManager.find(
        SomeEntity.class, primaryKey, LockModeType.NONE);
    int val = e.getVal();
    e.setVal(val + 1);
}

This is obviously not a real problem, but I'm trying to understand how these work, and their interplay. Thanks!

Jan Żankowski
  • 8,690
  • 7
  • 38
  • 52
  • Did you try it? – Andremoniy Dec 20 '16 at 14:42
  • Not yet, I thought someone may know off-hand, plus having it on SO will hopefully help others. – Jan Żankowski Dec 20 '16 at 14:44
  • This depends on the DBMS being used. e.g. not all DBMS actually support dirty reads. The DBMS might return a higher isolation level then requested (e.g. read committed instead of read uncommitted). The LockModeType is a Hibernate thing if I'm not mistaken and has no effect on the DBMS side –  Dec 20 '16 at 14:47
  • @a_horse_with_no_name Thanks, though the question still stands - is the more restrictive locking policy of the two applied then, for example? Also, LockModeType is in JPA, not just Hibernate, and people say it's enforced on DBMS level (http://stackoverflow.com/questions/41242940). – Jan Żankowski Dec 20 '16 at 14:51
  • The isolation level does not define any locking behaviour - it only defines which data can be seen by a transaction. –  Dec 20 '16 at 14:53
  • 1
    @a_horse_with_no_name Isolation level does define locking - it's implemented using table or row level locks in DBMS - see http://stackoverflow.com/questions/16162357 – Jan Żankowski Dec 20 '16 at 15:02
  • That might be true for some old DBMS. But modern DBMS do not use locks to support different isolation levels. –  Dec 20 '16 at 15:05
  • @a_horse_with_no_name Seems that both MySQL 5.7 (https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html) and PostgreSQL 9.6 (https://www.postgresql.org/docs/9.6/static/transaction-iso.html) use locks for that. – Jan Żankowski Dec 20 '16 at 15:16
  • Postgres only locks rows if you change them, not when you select them - but row level locks due to DML changes are not directly related to the isolation level. The isolation basically controls how _reading_ of modified data is handled. –  Dec 20 '16 at 15:19

0 Answers0