Description Caspar D. 2009-10-13 07:58:20 EDT

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.14) Gecko/2009090216 Firefox/3.0.1
Build Identifier: CDO 2.0.0.v200906160459

During multiuser testing, with around 15 users performing frequent commits and refreshs, we are seeing duplicate CDOID/CDO_VERSION entries appearing in our database. Obviously these pairs should be unique, so this is data corruption.

First, I'm a bit surprised that CDO doesn't put a uniqueness constraint on this compound key -- so "part 1" of this Bugzilla is a request to add that.

Second, I am wondering: how does CDO protect against conflicting "updates" at the persistence level? Obviously an "update" is really a UPDATE+INSERT combination with the insert adding the new revision -- so what I mean is: how does CDO prevent two INSERTs from inserting two rows with the same CDOID/CDO_VERSION ? Since CDO has not put a uniqueness constraint on the compound key, this responsibily can not be assumed to be handled by the database.

I am aware of the lockedObjects collection in TransactionCommitContextImpl, but these objects are released shortly after the DBStoreAccessor returns. So the assumption seems to be that once the DBStoreAccessor returns, the data has safely been written. But a DBMS will surely return before performing the actual write, if that's possible given constraints and isolation requirements. 

Note, the above is based on a somewhat hasty code review, so if I misunderstood something I apologize. Also, I must admit that we are still using Derby -- we'd like to switch, but for the upcoming release of our product we are too far down the development/test cycle for that. And at this stage we see no reason to assume that this is Derby-specific.

Reproducible: Sometimes

Steps to Reproduce:
During multiuser testing, with around 15 users performing frequent
commits and refreshs, we are seeing duplicate CDOID/CDO_VERSION entries
appearing in our database. Obviously these pairs should be unique, so
this is data corruption.

First, I'm a bit surprised that CDO doesn't put a uniqueness constraint
on this compound key -- so "part 1" of this Bugzilla is a request to add
that.

Second, I am wondering: how does CDO protect against conflicting
"updates" at the persistence level? Obviously an "update" is really an
UPDATE+INSERT combination with the insert adding the new revision -- so
what I mean is: how does CDO prevent two INSERTs from inserting two rows
with the same CDOID/CDO_VERSION ? Since CDO has not put a uniqueness
constraint on the compound key, this responsibily can not be assumed to
be handled by the database.

I am aware of the lockedObjects collection in
TransactionCommitContextImpl, but these objects are released shortly
after the DBStoreAccessor returns. So the assumption seems to be that
once the DBStoreAccessor returns, the data has safely been written. But
a DBMS will surely return before performing the actual write, if that's
possible given constraints and isolation requirements. So that seems to
leave room for an update from a second session to be processed in such a
way that it appears to have been serialized at the CDO level, but
actually may be parallel at the database level. 

Admittedly, this above is based on my review of fairly complex code, so
if the protection is there and I just overlooked it, I apologize. 

Also I must admit that we are still using Derby -- we'd like to switch,
but for the upcoming release of our product we are too far down the
development/test cycle for that. However, at this stage we see no reason
to assume that this is Derby-specific.