Re: Deadlock Error in database!!!!!

spraveen2001_at_yahoo.com (Praveen) wrote in news:98d8ec76.0303072139.5d410f41 _at_posting.google.com:

> Hi All,
>
> We have an application with java,jboss server. If they are trying to
> login through that application. They are getting "ORA-04020: deadlock
> detected while trying to lock object DEMO.TMP_USR". Here TMP_USR table
> is a global temporary table. What might be the problem in the
> database? is there any solution for this??
>
> Thanks in Advance,
> Praveen

I have only seen one bug that I can recall that caused a deadlock that wasn't the application. Every other situation was code design or schema design.

Since you are talking about a temporary table the schema design is out because that requires a parent child relationship without a FK index.

A deadlock means the following:

User A updated table X and needs to also update table Y. User B updated table Y and needs to also update table X.

But A can't update Y since the row A needs is currently locked by user B, and user B can't update X since the row B needs is currently locked by user A. Oracle can detect this situation, and one of the users will get killed by Oracle since there is no way for both to continue. Find where you have the above situation, establish data flow rules so everyone follows the same flow, and deadlocks won't occur. Received on Fri Mar 14 2003 - 07:33:33 CET