engineer engineer - 5 months ago 145
Java Question

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. )

Answer

Instead of a SELECT FOR UPDATE and then an INSERT or UPDATE can't you just issue a single MERGE statement, and let the database do the hard work for you. You may need to check you have the appropriate Unique Constraints on the table you are merging into e.g.

void m() {
    JdbcTemplate jt;
    ...
    jt.update(mergeLine, objs2, types2); // merge
}
Comments