CODEWITHSUNDEEP
javasqlspringoracle-databasejdbctemplate
ROMANIA_engineer
ROMANIA_engineer

Reputation: 56666

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

Upvotes: 2

Views: 7805

Answers (3)

bindu rao
bindu rao

Reputation: 21

Try removing JDBCTemplate and use simple jdbc statement and see if it locks.

Upvotes: 0

DrabJay
DrabJay

Reputation: 3099

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
}

Upvotes: 1

icza
icza

Reputation: 417747

Why did you turn off auto-commit?

setAutocommit(false);

Since you disabled auto-commit, your INSERT/UPDATE will not be executed immediately. So if you call m() twice, since it starts with a SELECT that will be executed again before the INSERT/UPDATE from the previous run will be executed, hence you'll see an execution order:

SELECT > SELECT > INSERT/UPDATE > INSERT/UPDATE

You should commit before you return from m() or you should go with @Transactional but don't disable auto-commit.

Upvotes: 1

Related Questions