Sunday, March 28, 2010

Optimistic and Pessimistic Locking in Oracle ADF BC

While doing Oracle ADF 11g training during this week, we were discussing with students about Optimistic and Pessimistic locking support in ADF BC. Actually, while available documentation gives you good insight into theoretical side of locking mechanism, its still quite hard to understand how it really works and when we should apply Optimistic or Pessimistic locking. I have prepared sample application, where I'm describing differences between those two.

Download - OptimisticPessimisticLocking.zip application, it is configured by me to work in Optimistic locking mode. Let's switch to Pessimistic locking (default one), you can do this through ADF BC application module settings:


In order to test locking behavior, we need to simulate multiple users - just open sample application with two separate browsers:


We change data in the second browser (second user), press Save button. Then we go to the first browser (first user), modify data and press Save - error about concurrent data modification in current will be shown:


And now is very important part - let's assume that user in the first browser will wait and will not try to save or undo his changes. This basically means, in Pessimistic locking case, database lock from the first user still will remain in database. Now, if second user will go to modify same record and will try to save it:


The second user will get error about database lock - user will be stuck in current record:


The second user will be forced to wait until first user will Save or Undo his changes in order to proceed with data commit. Another option for the second user - Undo his change in the current row and proceed with other rows editing. When the first user will close his transaction, the second one will be able to save his changes for previously locked record as well:


Now we will see how it works in Optimistic mode:


The second user is changing data, committing it and then first user is doing change in the same row and trying to save it - error about concurrent modification will be generated:


However in Optimistic locking case, the first user will not hold database lock and second user will be able to continue his work (with the same or other records):


At the same time, first user will be able to press Save button second time, and his data will be committed.

15 comments:

Steve Muench said...

The doc specifically suggests to always use optimistic locking mode for web applications in section "40.11.1 How to Set Applications to Use Optimistic Locking".

Andrej Baranovskij said...

Hi Steve,

Thats correct. My objective here was to describe, what is the difference between Optimistic and Pessimistic - so developer will understand it better.

Thanks,
Andrejus

Unknown said...

Andrejus,

Great article! Most developers don't have it clear in their minds.

Andrej Baranovskij said...

Thanks George, my pleasure ! ;-)

Didier Laurent said...

In optimistic mode, the fact that the RowInconsistentException/JBO-25014 is only raised at the first attempt to commit the record seems to cause some problems to developers.

Any attributes that were modified in the current transaction keep their modified values, but the others attributes are refreshed with the values from the database.
When the user tries to commit his changes a second time, then the exception 25014 isn't raised anymore, and the commit succeeds.

If the default behavior doesn't fit your requirements, you can override the lock() method.

See Note 842855.1 in "My Oracle Support" for more details.

Andrej Baranovskij said...

Thanks Didier,

What I see quite often - developers don't understand the difference between optimistic and pessimistic. So, I believe shared info will help.

Regards,
Andrejus

Vikram Kohli said...

Thanks Andrejus for making me not only understand ADF looking , but so many other ADF concepts.

Vikram

Andrej Baranovskij said...

Thanks Vikram, thats inspires me to provide even better content.

Andrejus

thegame said...

Thanks a lot 'Andrejus', I'm developing my University proyect's in Oracle Fusion, and I did not know about this loockin type, becuase the system show me a error loocking.

But I know about loocking, I repair my Proyect. Thanksss

Greats from South America !!!

Rahul More said...

in which cases do you suggest to use pessimistic

Andrej Baranovskij said...

In this case:

1. User A edits data and saves, but validation error is produced
2. You want to prevent any other user to update same record, while user A is fixing validation error

Andrejus

cquest said...

Hi Andrejus ..
Thank you for you blogs ..
It helps the entire team I am working for..
It is amazing and yet so simple..

Unknown said...

Thankyou very much Andrejus

Anonymous said...

Hi
unable to download zip file attached above.

Andrej Baranovskij said...

Hi,

All old samples can be downloaded from archive: http://andrejusb.blogspot.lt/2017/08/my-blog-samples-download-repository-for.html

Regards,
Andrejus