I am using JdbcTemplate from Spring framework.
The database is Oracle.
Simplified Code:
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.:
How can I be sure that SELECT
and UPDATE/INSERT
will be run together ? (with/without SELECT .. FOR UPDATE
, @Transactional
, etc. )
Select For Update Spring Jdbctemplate
3 Answers
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.
Why did you turn off auto-commit?
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. Hack for mu online 97d.
Try removing JDBCTemplate and use simple jdbc statement and see if it locks.
Not the answer you're looking for? Browse other questions tagged javasqlspringoraclejdbctemplate or ask your own question.
I have a spring application which reads data from the Database and sends it to system 'X'. I am using task executors to spin up threads, so there are like 5 threads which are reading the database for rows at the same time. For each thread I need to make sure that unique records are selected.To achieve this I am using JdbcTemplate and 'select for update'
I have written the code but in the logs I am able to see 2 threads picking up the same rows. I am not able to figure out the root cause of this issue. Does anyone has a suggestion
3 Answers
Have you had transaction control properly set up?
If not, the transaction will only happen for the duration of the update statement, and will be committed automatically (You are using Oracle I believe, base on your syntax).
That means, although you acquired the lock of those records, they are released right-away.
You need to annotate your class with @Repostitory tag and the @Transactional tag to make sure that all the actions in the same call are handled in one transaction.
If they are not handled in the same transaction then each SELECT_FOR_UPDATE will happen on a different transaction and thus your threads queries will not be syncronized and your select_for_update does not matter.
Do you have access to modify the database? If I understand your question correctly I recently had a similar problem and implemented a scheme like this:
Add a new column to your database like 'thread_number' or something like that. Set it to some default value like 0. Give each thread a unique identifier. Then you 'claim' a record in the database by updating its 'thread_number' to the identifier of the thread processing it. Then the other threads will not find it when querying if you include 'where thread_number = 0' in the SQL.
I know it's kind of broad, but I hope it helps.