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
