Cool, thanks mucho Jay! Sorry to worry everybody about deadlocks! You
learn something new every day.
Regards,
Bill
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Jay
> Walters
> Sent: Tuesday, May 08, 2001 11:33 AM
> To: '[EMAIL PROTECTED]'
> Subject: RE: [JBoss-dev] TODO: JBossCMP 1.1 FAST!
>
>
> 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
>
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development
