Re: DB dead lock issue with deletePersistent calls

Hi Bruno,

Bruno CROS wrote:
> Hi,
> 
> I'm experiencing some troubles about deletePersistent usage when running
> ODMG transactions. The most of the issues is an Oracle dead lock. 
> ORA-00060.
> 
> I'm looking for what can be wrong, and don't find. Error occurs
> on production server, never on developpement servers !!
>

This sounds like a concurrency issue if it isn't possible to reproduce
it on development server. Did you try to write a multithreaded test to
reproduce the problem?


> To summarize, the transactions that produces the error is build with loops
> containing several steps :
> 1. unreferencing objects who have to be deleted from object that have
> reference on them. 2. flush with TX Extension to produce db "updates" 
> posts.
> 3. delete objects with getDatabase.deletePersistent(o). 4.flush to produce
> "delete" posts before next iteration.
> 
> Finally, when loop is ended, commit.
> 
> ORA-00060 always occurs  on DELETE SQL queries, as two threads would
> delete/update the same record.
>

Did you trace/log the generated sql-statements with oracle?

> I ask myself some questions  :
> 
> - Does Impl.getDatabase().deletePersistent(o) can be called several 
> times in
> unique one transaction without trouble ?

yep! As long as you don't commit the tx, Database().deletePersistent(o)
should always lookup the same tx.

> 
> - Does Impl.getDatabase().deletePersistent(o)  and a flush always post a 
> SQL
> DELETE when flush is caélled? ( mean not at commit only)

yep!

> 
> - Does the update posts of objects to be finally deleted, can lock records
> when "deletes queries" are post ? e.g : unreferencing A from B and flushing
> cause SQL UPDATE of B and implicitly of A (why not) then, delete A can't be
> done because it is DB locked.
> 
> What does the implicitLocking option do on DB?

Nothing, the locking-api is completely independent from DB. But if the
DB lock settings are more strictly then the locks used by the locking
api you can get DB-locking issues. E.g. if you use ReadUncommited on OJB
and RepeatedRead on DB.

> 
> What are the differences between markDelete(o) and 
> database.deletePersistent(o)
> ? at flush ? at commit ?

 From api of #markDelete:
"Marks an object for deletion without
locking the object. If the object wasn't locked before,
OJB will ask for a WRITE lock at commit."

database.deletePersistent immediately locks the object and mark it for 
delete.


> 
> And according to the OJB following note about deletePersistent usage.
> 
> "It is important to note that the Database.deletePerstient() call does not
> delete the object itself, just the persistent representation of it. The
> transient object still exists and can be used however desired -- it is
> simply no longer persistent. "
> 
> Is it possible OJB implicitly locks an old persistent object
> (deletePersistent marked) (so transient now) and occurs a DB lock of record
> (however update occurs before delete instead of the opposite)?

Normally transient objects are never locked and OJB doesn't lock an
object on DB (e.g. using select ... for update).

regards,
Armin

> Note that
> this can occur if deletePersistent DELETE posts are not done regarding the
> call sequence.
> 
> Working with 1.0.4 and some few patches, Oracle 10g.
> 
> Thanks for answers.
> 
> Regards.
>