samedi 18 juin 2016

JdbcTemplate SELECT ... FOR UPDATE - no lock

I am using JdbcTemplate from Spring framework.

The database is Oracle.

Simplified Code:

void m() {
    setAutocommit(false); // the same result with/without this line (by default: true )
    JdbcTemplate jt;
    ...
    String selectForUpdateLine = "SELECT X FROM T ... FOR UPDATE";
    int x = jt.queryForList(selectForUpdateLine, objs, types, Smth.class).size();
    ...
    addDelay(); // to be sure that I can simulate 2 consecutive SELECTs (just for test)
    ...
    if ( x == 0 )
        jt.update(insertLine, objs2, types2); // insert
    else
        jt.update(updateLine, objs2, types2); // update
}

If I call m() twice it executes:

SELECT > SELECT > INSERT/UPDATE > INSERT/UPDATE

but I want to have

SELECT > INSERT/UPDATE > SELECT > INSERT/UPDATE

I expected to have a lock after the first SELECT ( on the strengths of SELECT ... FOR UPDATE ), but both selects are called, so the UPDATE/INSERT doesn't work well.

I also tried to use @Transactional for the method, trying to have a single transaction that contains both INSERT and UPDATE/INSERT, but it didn't work. E.g.:

@Transactional(isolation=Isolation.SERIALIZABLE,propagation=Propagation.REQUIRES_NEW)

How can I be sure that SELECT and UPDATE/INSERT will be run together ? (with/without SELECT ... FOR UPDATE, @Transactional, etc. )

Aucun commentaire:

Enregistrer un commentaire