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.
> > 6. Thread 1 finishes and is trying to commit. CMP
> updates the entire Apple
> > including the primary key. Since a primary key is
> being updated on an
> > Apple, the database needs to obtain an
> exclusive-lock on the secondary
> > key/index constraint. It can't obtain an
> exclusive-lock because thread 2
> > has a shared-lock on the secondary key
> index/constraint, so it waits.
>
> So now the locks fire...it was too much for me to
> follow, so I will just
> state that one transaction probably got rolled
> back...not deadlocked.
>
> > 7. Thread 2 finishes and does the same logic as in
> step 6. It waits for
> > Thread 1 because Thread 1 has a shared-lock on the
> index.
>
>
> > DEADLOCK! Remember, Thread 1 and Thread 2 are
> accessing totally different
> > Entity beans! This simple scenario is why
> updating primary keys is very
> > very bad.
>
> Ohhh...Im sorry to get this far in the message and
> see that you were griping
> about the modification of primary keys. :-) Please
> understand that I am not
> at all in support of that. I believe it goes against
> any semblance of
> reason.
>
> > Because of RTFM problems and because tuned-updates
> used to be false by
> > default, we almost trashed JBoss because we were
> getting deadlock in very
> > simple code.
>
> So, you were modifying the primary keys? I still
> don't see what tuned
> updates and deadlocks have to do with each other? If
> a CMP update is not
> allowed to modify the PK, would that solve your
> issue? As we both stated,
> the PK should be sacred.
>
> jim
>
>
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
>
http://lists.sourceforge.net/lists/listinfo/jboss-development
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development
