You are viewing a plain text version of this content. The canonical link for it is here.

Posted to jdo-dev@db.apache.org by Jörg von Frantzius <jo...@artnology.com> on 2006/03/14 12:19:59 UTC

Optimistic locking - not 100% reliable without triggers?

Hi,

JPOX optimistic version verification entirely takes place within the VM, 
by reading a version column and comparing it with an expected value. 
When it verfies OK, JPOX proceeds and updates the version column with a 
new value. That means verification and update of version do not happen 
atomically, at least not on the database level, unless at least 
REPEATABLE_READ is used.

Now if two threads or processes want to update the same row, and happen 
to verify the row's version at the same time, it is theoretically 
possible that they both decide to update it, i.e. none of them will 
receive a JDOOptimisticVerificationException. Using READ_UNCOMMITTED 
instead of READ_COMMITTED for verifying the version column will increase 
chances of detecting a conflict, but still a conflict can remain undetected.

In Oracle's suggestion for implementing optimistic locking, the process 
will write the same optimistic version that it had previously read, and 
a trigger on the database will do the verification and increment the 
version if it had not been so yet. I guess that the trigger executes 
atomically, so conflicts will always be detected.

Am I wrong here somewhere or do we really need triggers to have 100% 
reliability of conflict detection?

Thanks for any hints,
Jörg

Re: Optimistic locking - not 100% reliable without triggers?

Posted by Erik Bengtson <er...@jpox.org>.

Jorg,

You already has the solution: increase the isolation level. JPOX will try to
hold the database transaction the less time as possible.

Regards,

Quoting Jörg von Frantzius <jo...@artnology.com>:

> Hi,
>
> JPOX optimistic version verification entirely takes place within the VM,
> by reading a version column and comparing it with an expected value.
> When it verfies OK, JPOX proceeds and updates the version column with a
> new value. That means verification and update of version do not happen
> atomically, at least not on the database level, unless at least
> REPEATABLE_READ is used.
>
> Now if two threads or processes want to update the same row, and happen
> to verify the row's version at the same time, it is theoretically
> possible that they both decide to update it, i.e. none of them will
> receive a JDOOptimisticVerificationException. Using READ_UNCOMMITTED
> instead of READ_COMMITTED for verifying the version column will increase
> chances of detecting a conflict, but still a conflict can remain undetected.
>
> In Oracle's suggestion for implementing optimistic locking, the process
> will write the same optimistic version that it had previously read, and
> a trigger on the database will do the verification and increment the
> version if it had not been so yet. I guess that the trigger executes
> atomically, so conflicts will always be detected.
>
> Am I wrong here somewhere or do we really need triggers to have 100%
> reliability of conflict detection?
>
> Thanks for any hints,
> Jörg
>

Re: Optimistic locking - not 100% reliable without triggers?

Posted by Jörg von Frantzius <jo...@artnology.com>.

Hello Eric,

thanks a lot for your answer, that's exactly what I was looking for!

Regards,
Jörg

Eric Samson schrieb:
>
> Hello Jörg
>
>  
>
> One common portable solution to this is to acquire locks during the 
> conflict detection step (using SELECT for UPDATE instead of simple 
> SELECT).
>
> Another approach could be to perform the conflict detection and the 
> update at the same time (statement like "UPDATE WHERE pk=OID AND 
> TS=ts"), but it raises some concerns for the conflict resolution (most 
> JDBC drivers are not able to indicate which rows raised an exception).
>
>  
>
> Best Regards
>
> **...****.:**** Eric Samson, Founder & CTO, x****calia******
>
> //Service your Data!//
>
> ------------------------------------------------------------------------
>
> *De :* Jörg von Frantzius [mailto:joerg.von.frantzius@artnology.com]
> *Envoyé :* mardi 14 mars 2006 12:20
> *À :* jdo-dev@db.apache.org
> *Cc :* JDO Expert Group
> *Objet :* Optimistic locking - not 100% reliable without triggers?
>
>  
>
> Hi,
>
> JPOX optimistic version verification entirely takes place within the 
> VM, by reading a version column and comparing it with an expected 
> value. When it verfies OK, JPOX proceeds and updates the version 
> column with a new value. That means verification and update of version 
> do not happen atomically, at least not on the database level, unless 
> at least REPEATABLE_READ is used.
>
> Now if two threads or processes want to update the same row, and 
> happen to verify the row's version at the same time, it is 
> theoretically possible that they both decide to update it, i.e. none 
> of them will receive a JDOOptimisticVerificationException. Using 
> READ_UNCOMMITTED instead of READ_COMMITTED for verifying the version 
> column will increase chances of detecting a conflict, but still a 
> conflict can remain undetected.
>
> In Oracle's suggestion for implementing optimistic locking, the 
> process will write the same optimistic version that it had previously 
> read, and a trigger on the database will do the verification and 
> increment the version if it had not been so yet. I guess that the 
> trigger executes atomically, so conflicts will always be detected.
>
> Am I wrong here somewhere or do we really need triggers to have 100% 
> reliability of conflict detection?
>
> Thanks for any hints,
> Jörg
>

Re: Optimistic locking - not 100% reliable without triggers?

Posted by Jörg von Frantzius <jo...@artnology.com>.

Erik Bengtson schrieb:
> Jorg,
>
> Quoting Eric Samson <er...@xcalia.com>:
>   
>> One common portable solution to this is to acquire locks during the conflict
>> detection step (using SELECT for UPDATE instead of simple SELECT).
>>
>>     
>
> This should work in JPOX. If it does not raise a bug.
>   
Please see http://www.jpox.org/servlet/jira/browse/CORE-2743