Re: How to avoid database deadlocks?

>  I'm developing some BMP entity beans with WebLogic 6.1 &
> Oracle 9 and am running into lots of ORA-00060 deadlock
> detected errors. I know the cause of this, but seem to have a
> chicken and egg situation when trying to solve this...
>
> Let's say we have two entity beans, A and B, with 1:1
> relationship between them. Both have accessor methods to each other
>
> A.getB()
> B.getA()
>
> in database one of the tables hold foreign key pointing to
> another's primary key. It's not important which one.
>
> Now, we have an application where you can view & edit both
> entities. View screen for A shows also B and vice versa.
> Pseudo-code for these view screens would be:
>
> view A:
>
> aToShow = findByPrimaryKey(primary_key_from_user)
> bToShow = a.getB()
>
> view B:
>
> bToShow = findByPrimaryKey(primary_key_from_user)
> aToShow = b.getA()
>
> Now, the problem is that WebLogic seems to call ejbStore() in
> the order in which the beans were initially accessed. Let's
> say thread #1 views A and thread #2 views corresponding B.
> This would create following SQL:
>
> thread #1: UPDATE A SET...WHERE ID = 203
> thread #2: UPDATE B SET...WHERE ID = 17
> thread #1: UPDATE B SET...WHERE ID = 17 (waits for lock by
> #2) thread #2: UPDATE A SET...WHERE ID = 203 (waits for lock by #1 =>
> deadlock)

Why are views firing UPDATEs? You're using 2.0 entity beans, right?
Sorry, I'm a bit unfamiliar with BMP 2.0....

>
> Now, there could be some "hacks" around this, but the
> situation is not this simple since this occurs in much more
> complex situations and where data is actually updated. Maybe
> in this case some ejbSelect -methods could be used to reverse
> the order in one of the views, but this is really not
> practical when cases get more complex. Is there any generic
> way to avoid such problems while still using BMP and same data model?

Implementing an isModified/isDirty pattern would minimize the problem,
but the bottom line is you're causing a race condition: two threads are
racing for resources.

>
> Interestingly enough, I do not seem to get the same problem
> with CMP & CMR. I wonder why this is the case. Still I would
> need to get BMP also working.

That's because CMP knows when to fire UPDATEs and when not to. It
minimizes the chances of the problem ocurring.

Implement the isModified/isDirty pattern on your BMPs:

Public class JanneBMP implements EntityBean {
        private transient boolean janne_modified = false;

        void dirty() {
                janne_modified = true;
        }

        public boolean isDirty() { return janne_modified; } //For
Orion/OC4J
        public boolean isModified() { return janne_modified; } //For
Orion/OC4J
}

Then your BMPs:

Public class AEJB Extends JanneBMP {

        public String name; //BMP field
        public String getName() { return name; }
        public void setName(String name) { dirty(); this.name = name; }

        public void ejbStore() {
                if (!isDirty() )
                        return;
        }
}

Finally, the errors you're getting are because deadlocks are detected
(either by the DB or the App Server). Undetected deadlocks would just
hang up your DB/App Server. They're caused by a race condition; You do
*handle* them (yes, even if you don't write code: the exception
propagates and the operation is aborted, no retrys).

You can then:
A) Present the info to the user (possibly with a retry button?) or retry
yourself or whatever-- a reactive approach.
B) Avoid race conditions -- a proactive approach.

Essentially the problem you have is *exactly* the same you'd have by
using plain JDBC or Oracle's Stored Procedures for that matter. DBs, App
Servers, they will *heuristically detect* the deadlocks, *abort* the
deadlock inducing operation(s), and inform you. EJBs won't
"automagically" handle the deadlocks because that's a business logic
choice you have to make.


My 2c,

JP

===========================================================================
To unsubscribe, send email to [email protected] and include in the body
of the message "signoff EJB-INTEREST".  For general help, send email to
[email protected] and include in the body of the message "help".