Oracle db lock when using PROPAGATION_REQUIRED_NEW with two separate spring bean

Question

I have 2 spring bean (Bean A and B) which access the db. Both bean are in PROPAGATION_REQUIRED_NEW and the isolation level is READ COMMITED on a ORACLE db.

Bean call A select (via Hibernate) on the same table twice with different criteria. Then the bean B is called to for each of the row oth the table to do an update on them. Weirdly everything work fine if Bean A does only one call (select) to the db. I've check with the query from dba.stackexchange and it's only when the second "select" in Bean A that have a db lock.

NOTE: Why not using PROPAGATION_REQUIRED or PROPAGATION_NESTED for bean B ? We don't use PROPAGATION_REQUIRED Because we don’t won’t to roll back everything if one of the row fail and has for PROPAGATION NESTED the version of our jdbc driver we are using doesn’t support it.

My current workaround is to have a bean that handle the select seperatly and the main bean doesn't do any transaction just call the two other bean. I just find it really strange that doing "select * from X" cause a lock on the table when we provide locking NONE and we are in READ_COMMITED. Am I missing something here?

The first query:

select
    temptable0_.ID as ID1_43_,
    temptable0_.IDSEND as IDS2_43_,
    temptable0_.DATERECEI as DAT3_43_,
    temptable0_.DATEPROC as DAT4_43_,
    temptable0_.STATUT as STA5_43_,
    temptable0_.ERROR_TYPE as ERR6_43_,
    temptable0_.CODE as COD7_43_,
    temptable0_.DESCRIPTION as DES8_43_,
    temptable0_.NOTIF as NOT9_43_,
    temptable0_.ACTION as ACT10_43_,
    temptable0_.IDCLIENT as IDC11_43_,
    temptable0_.PARAM as PAR12_43_ 
from
    TABLE_TMP temptable0_ 
where
    temptable0_.STATUT in (
        'NEW'
    )

The second query (same transaction (BEAN A))

select
    temptable0_.ID as ID1_43_,
    temptable0_.IDSEND as IDS2_43_,
    temptable0_.DATERECEI as DAT3_43_,
    temptable0_.DATEPROC as DAT4_43_,
    temptable0_.STATUT as STA5_43_,
    temptable0_.ERROR_TYPE as ERR6_43_,
    temptable0_.CODE as COD7_43_,
    temptable0_.DESCRIPTION as DES8_43_,
    temptable0_.NOTIF as NOT9_43_,
    temptable0_.ACTION as ACT10_43_,
    temptable0_.IDCLIENT as IDC11_43_,
    temptable0_.PARAM as PAR12_43_ 
from
    TABLE_TMP temptable0_ 
where
    temptable0_.STATUT in (
        'ERROR'
    )

And the Update statement (done by BEAN B)

   update
        TABLE_TMP 
    set
        IDSEND=?,
        DATERECEI=?,
        DATEPROC=?,
        STATUT=?,
        ERROR_TYPE=?,
        CODE=?,
        DESCRIPTION=?,
        NOTIF=?,
        ACTION=?,
        IDCLIENT=?,
        PARAM=? 
    where
        ID=?

Where IDSEND is incremented, DATEPROC is set and STATUT and ERROR_TYPE are set if there a error or a success.

related : http://stackoverflow.com/questions/8490852/spring-transactional-isolation-propagation — Chris, May 02 '16 at 13:03
Don't trust jdbc specification or Spring abstractions, check how Oracle works instead. — John Donn, May 02 '16 at 13:20
Oracle does not support dirty reads (READ UNCOMMITED). Plus: you will need to show us the actual SQL statements. A plain select statement will **never** lock any other transaction in Oracle (and neither will a plain select statement ever be locked by other statements) — , May 02 '16 at 15:20

score 2 · Answer 1 · answered May 02 '16 at 14:35

2

Oracle doesn't support READ UNCOMMITED. You are using the default mode with is READ COMMITTED.

If you try to select a records with a pending transaction change from other sessionon it, you'll have to wait until the update is commited.

answered May 02 '16 at 14:35

Marmite Bomber

19,886
4
26
53

From what I've read The READ UNCOMMITTED isolation level allows dirty reads but Oracle Database doesn't use dirty reads, nor does it even allow them. The basic goal of a READ UNCOMMITTED isolation level is to provide a standards-based definition that allows for nonblocking reads. Source: http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o65asktom-082389.html – Chris May 02 '16 at 14:51
You are rigth that READ COMMITTED is the default and it's seem that we are using it instead of READ UNCOMMITTED. The thing I don't get it's that one query that just do a read (select) before a series of update work fine but having 2 query before the update produce a lock. – Chris May 02 '16 at 16:47

Oracle db lock when using PROPAGATION_REQUIRED_NEW with two separate spring bean

1 Answers1