I am seizing on your statement of no indexes on the foreign key in PEAR. I
believe that without the index on PEAR_APPLE_ID Oracle will need to lock up
the PEAR table on an update in order to enforce the cascade delete, if you
put an index on that column then it will no longer need to do this. I have
a book somewhere that speaks to this issue, but can't seem to put my hands
on it right now, I've pasted in a URL with this info for you...
http://osi.oracle.com/~tkyte/unindex/
Cheers
Jay
-----Original Message-----
From: Bill Burke [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 11:13 AM
To: [EMAIL PROTECTED]
Subject: RE: [JBoss-dev] TODO: JBossCMP 1.1 FAST!
Comments inserted later....
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Gina
> Celiko
> Sent: Monday, May 07, 2001 11:14 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [JBoss-dev] TODO: JBossCMP 1.1 FAST!
>
>
> Nope, it won't quite work that way I am afraid. You
> are talking about foreign key relationships here. I am
> mainly answering Bill here and agree with James, but
> just adding one point where a pear is inserted.
>
> --- James Cook <[EMAIL PROTECTED]> wrote:
> > > -----Original Message-----
> > > From:
> > [EMAIL PROTECTED]
> > >
> >
> [mailto:[EMAIL PROTECTED]]On
> > Behalf Of Bill
> > > Burke
> >
> > > It's very easy to get deadlock.
> > >
> > > Table Apple:
> > > apple_prim_key Number
> > > apple_data1 varchar(256)
> > > apple_data2 Number
> > >
> > > Table Pear:
> > > pear_prim_key Number
> > > pear_apple_id Number (indexed/secondary key
> > constraint to Apple table. Not
> > > sure of the correct term here).
> > >
> > > I have 2 threads, each running in their own
> > transaction, each working with
> > > totaly different rows from the same tables in an
> > Oracle 8.1.6 DB.
> > >
> > > 1. Thread 1 does a AppleHome.findByPrimaryKey
> > obtaining an Apple Entity
> > > Bean.
> > OK
> > > 2. Thread 1 calls a set attribute on the Apple
> > entity bean. CMP loads and
> > > locks the EntityBean. Since a field is changed,
> > this bean will also be
> > > updated when the transaction commits.
> >
> > I think this is the flaw. Unless jBoss is
> > specifically written to do so (and
> > it shouldn't be IMHO if it intends to be scalable in
> > any large degree),
> > neither setting an attribute on an entity bean or
> > reading the EB from the DB
> > will produce any kind of lock.
> >
> > > 3. Thread 2 does a AppleHome.findByPrimaryKey for
> > a different Apple Entity
> > > Bean and sets an attribute on it as well.
> >
> > OK....still no locks anywhere.
> >
> > > 4. Thread 1 inserts a Pear into the database.
> > This causes a
> > > shared-lock on
> > > the secondary key index/constraint for the entire
> > apple table.
> >
> > Since this is occurring within a transaction, the
> > insert will not cause a
> > lock until the XAResource is committed, correct?
> > So...no lock yet.
> >
> > > 5. Thread 2 inserts a Pear into the database.
> > Causes a shared-lock.
> >
> > Not yet.
> >
>
> Nope, when a pear is inserted, it only checks to see
> if the apple_id exists in the apple table and only if
> it exists then it will insert the pear into the pear
> table. That's the nature of referential integrity and
> foreign keys. So,
> 1. Therefore checking for pear_appleId is a
> select/read only operation and Oracle doesn't lock for
> those unless explicitly specified as
> "select for update"
> 2. Even if the appleId row in the apple table
> corresponding to the pear_appleID row in the pear
> table was being updated at that point,it is true that
> the row in the apple table is locked, however since it
> is only a select/read operation for foreign key
> checking, it wont even notice the row being locked
> because, oracle always makes a copy of the row which
> is locked for all read operations. Therefore, the pear
> table would be reading a copy of the row. There still
> be no deadlocks here.
>
>
So you're suggesting that I was hallucinating when I encountered these
problems? How come Oracle timed out Thread 1 with a SQL Exception 'Deadlock
detected ....'? We even queried the Oracle DB to find existing locks, and
there were some shared locks going on. I'm no DBA, but some
exclusive/shared lock stuff is happening in the above example when primary
keys are updated.
I just looked at our sql, and actually I don't have any indexes but have
this constraint:
alter table PEAR
add constraint FK_PEAR_APPLE_ID_APPLE foreign key (PEAR_APPLE_ID)
references APPLE (APPLE_PRIM_KEY) on delete cascade
I'm sorry to drag this thread out, but I think its important to figure out
what consequences updating primary keys has. I at least want to understand
it for myself.
Bill
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development
