1

Say I have a table MY_TABLE without optimistic lock column. I have the following update statement that will be executed with org.hibernate.SQLQuery:

UPDATE MY_TABLE SET NAME = ? WHERE SOME_NUMBER = ? AND NAME = ?

In some place in my Java code, I'll create a new async thread to execute this update. Say that at a given time after some seconds this thread is executed:

Thread 1:

UPDATE MY_TABLE SET NAME = 'TEST2' WHERE SOME_NUMBER = 3 AND NAME = 'TEST'

This operation can take some minutes. Few milliseconds later the following thread is executed:

Thread 2:

UPDATE MY_TABLE SET NAME = 'TEST3' WHERE SOME_NUMBER = 3 AND NAME = 'TEST'

I need to make sure that, since the rows affected are the same, the UPDATE from the first thread is executed before the one from the second thread.

Does the default lock strategies from Oracle already guarantee that? Or do I need anything else?

Thanks!

Improve this question

2 Answers 2

Reset to default
0

No

You can be sure that one of the threads will obtain the lock and hold it until the transaction ends. And while it holds the lock, no other transaction can get the lock. But there is no guarantee that the thread you started first will get the lock first, because there is no coordination of the threads from the start to the update statement.

This is no special problem of Oracle but that is typical when working with threads. If you want to guarantee some order of processing you are responsible to coordinate this. For example your first thread requests a lock L (in your program, on your client or in the database, what ever is necessary)) and after it got the lock the second thread is started and requests the lock L. But L is released not before the first thread has finished.

Improve this answer
2
  • Hmm that's true @miracle173. I will use this workaround to guarantee that the threads will be created in the proper order. Thanks
    – qxlab
    Commented Sep 10, 2015 at 17:45
  • @qxlab: Yes, this latch is what I described as lock "in your program". But why do you call it a workaround? I think it is the solution.
    – miracle173
    Commented Sep 11, 2015 at 5:53
0

As you can see from the documentation you supplied, oracle places an exclusive lock on the row during an update. This means that the second transaction in thread 2 will wait until thread 1 has committed and thereby releasing the lock on the row. If you only execute the UPDATE statement oracle will handle it for you.

Improve this answer
3
  • Thanks @Niels Jakobsen, so the exclusive lock will be applied on all the rows involved, is that right? Also, considering many threads being executed... will the lock acquisition follow a FIFO approach?
    – qxlab
    Commented Sep 10, 2015 at 16:50
  • Yes it will lock all involved rows in the table until the transaction commits. Each thread can hold different locks on different rows, it is only when it wants to write to a row that another transaction holds an exclusive lock on, that causes a wait. And yes the lock acquistion is simply a FIFO.
    – Niels Jakobsen
    Commented Sep 10, 2015 at 17:01
  • "the lock acquistion is simply a FIFO" do you have a reference for this statement.
    – miracle173
    Commented Sep 10, 2015 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.