[OJB-developers] Database generated primary keys

[Brian D. <bde...@in...>]

Hi all,

I was wondering if anyone has tried to, or if there are plans to expand
OJB to support database generated primary keys, such as Informix's
SERIAL type.

I am running into a problem using the included SequenceManagers when the
tables being mapped to may be inserted into by other non-OJB processes.
In such cases OJB may begin a transaction to insert a new row, grab the
max value from the table in question, but then by the time it actually
does the insert, another process may have already inserted the next row.

There seem to be two options, to create a SequenceManager which gets the
max value on the table and places a lock on the table until the
transaction is complete, or to have OJB try to support the built in
SERIAL-type fields of the databases.

From looking at the architecture of the ODMG code, however, that may be
pretty tricky. It looks like when an object is first locked, a new key
is generated using the SequenceManager if one is not already set and the
autoincrement indicator is true. This allows the LockManager to lock a
new object using the newly generated primary key. It is not until later,
however, when the ODMG transaction is committed, that the insert is
actually performed. This means that if you were going to try to rely on
the database to generate the primary key on insert, that you don't have
a primary key when the object is first locked to hold a lock on.

One question that came to mind was if a lock really needs to be placed
on a new object that is deferring its primary key generation to the
database? Instead, could the identity of that object just be unique to
that instance until a primary key is assigned to it during the commit?
This would mean that the assignment of reference foreign keys and
collection inverse foreign keys would have to be moved from the lock
stage to the commit stage, but I can't think of why that would be a
problem.

Has anyone gone down this road before, or have thoughts on this?

-Brian

--
Brian DeVries                   Sr. Software Engineer
mailto:bde...@in...   http://www.intraware.com
Voice: 925.253.6516             Fax: 925.253.6785
--------------------------------------------------------
Intraware... The leading provider of Electronic Software
             Delivery and Management (ESDM) Solutions