Deadlocks mapped to common exception?

[Oliver Zeigermann <ol...@gmail.com>]

Folks,

can anyone tell me if OJB is able to find out that a certain SQL
exception really is a deadlock exception and maps it to another common
one (DeadlockException)? This would be very helpful to repeat a
deadlocked transaction. I found no code in OJB that might do such a
job.

If there is nothing in OBJ the Jakarta Slide project has some initial
code that does this for some dbs. Maybe this could be integrated in
OJB?

Oliver

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Deadlocks mapped to common exception?

[Armin Waibel <ar...@apache.org>]

Oliver Zeigermann wrote:


>>>So, to get this going do you want me to prepare a patch
>>>for OJB which does exactly this: Introduce the createException method
>>>in the plattform class plus the exceptions and the initial code to
>>>feed it reasonably for Postgres, MySQL, SQLServer, and Sybase (is
>>>Sybase supported by OJB in the first place?)?
>>
>>+1 this will great!
> 
> 
> Will try, it might take some time...
>

No problem.


> 
>>>Maybe as a seconed step
>>>there could be a configuration mapping file ala Spring or Hibernate?
>>>
>>
>>+1 This could be an option for OJB 1.1
> 
> 
> Considering what you said, you could just throw the exceptions and the
> user decides if he/she wants to redo the transaction or if it is a
> real programming error. This way there would be no need for a
> configurable mapping...
> 

ahh, misunderstand this. I thought user can configure which kind of 
exception will be thrown for an SQL error state, e.g. 
345==>KeyKonstraintException.

regards,
Armin


> Oliver
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Deadlocks mapped to common exception?

[Oliver Zeigermann <ol...@gmail.com>]

On Fri, 10 Dec 2004 13:16:24 +0100, Armin Waibel <ar...@apache.org> wrote:
> What is the "top level"? Slide itself or the app using Slide. If you run
> a persistence layer in a managed environment (like a j2ee conform
> appServer) it is not possible for the persistence layer to restart the
> tx, because tx demarcation is handled by the container or bean or by the
> client using UserTransaction(and tx could be a distributed tx).
 
> So +1 for improving exception handling like you suggest, but -1 for all
> stuff like automatic tx redo on persistence layer level. Or I'm wrong
> and Slide could handle this in managed environments?

Agreed. Slide uses a UserTransaction, rolls it back and restarts it.
This is not on the persistence layer. Thus OJB should not restart
anything as well, but just throw the exception and the user needs to
decide what is to be done.
 
> 
> > Slide also has some internal locking which can be used
> > to either completely eliminate deadlocks (at the cost of no
> > concurrency while writing) or at least limit its likelyhood.
> >
> 
> PB-api only supports optimistic locking. The odmg-api supports
> pessimistic + optimistic locking (with configurable lock modes).
> Pessimistic locking should be able to prevent DB deadlock (dependent on
> odmg lock mode and DB lock mode).

I can imagine scenarios when pessimistic (blocking) locks in the
persistence layer can cause distributed (unresolvable) deadlocks. This
can be when the DB has more coase grain locks than the persistence
code or when there are transactions that do not use the persistence
layer at all.

> > I have been
> > told that Hibernate (2.1.7) supports a mapping with an (XML?)
> > configuration file:
> >
> > http://forum.hibernate.org/viewtopic.php?p=2223974
> >
> > Maybe as a default all of the Exceptions mentioned above should cause
> > the transaction to be redone?
> 
> see beginning of response, maybe I misunderstand you ;-)

Agreed. 
 
> > So, to get this going do you want me to prepare a patch
> > for OJB which does exactly this: Introduce the createException method
> > in the plattform class plus the exceptions and the initial code to
> > feed it reasonably for Postgres, MySQL, SQLServer, and Sybase (is
> > Sybase supported by OJB in the first place?)?
> 
> +1 this will great!

Will try, it might take some time...
 
> > Maybe as a seconed step
> > there could be a configuration mapping file ala Spring or Hibernate?
> >
> 
> +1 This could be an option for OJB 1.1

Considering what you said, you could just throw the exceptions and the
user decides if he/she wants to redo the transaction or if it is a
real programming error. This way there would be no need for a
configurable mapping...

Oliver

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Deadlocks mapped to common exception?

[Armin Waibel <ar...@apache.org>]

Oliver Zeigermann wrote:
>>>>can anyone tell me if OJB is able to find out that a certain SQL
>>>>exception really is a deadlock exception and maps it to another common
>>>>one (DeadlockException)? This would be very helpful to repeat a
>>>>deadlocked transaction. I found no code in OJB that might do such a
>>>>job.
>>>>
>>
>>You are right. OJB wraps all SQLException as
>>PersistenceBrokerSQLException (except key constraint errors here OJB use
>>a specific exception). See JdbcAccessImpl#executeInsert. PBSE is an
>>unchecked exception and with #getCause() you can extract the SQLException.
>>
>>
>>
>>>>If there is nothing in OBJ the Jakarta Slide project has some initial
>>>>code that does this for some dbs. Maybe this could be integrated in
>>>>OJB?
>>>>
>>
>>hmm, I only found some code in PostgresRDBMSAdapter#createException.
>>Were can I find the common code for deadlock handling?
> 
> 
> Until now code to detect an exception really is a deadlock exception
> is available for Postgres, MySQL, SQLServer and Sybase only. Slide
> also supports DB2 and Oracle, but the code for them still is missing.
> 
> The rest of the code merely catches the DeadLockException at top
> level, rolls back the transaction, starts a new one and repeats the
> whole request.

What is the "top level"? Slide itself or the app using Slide. If you run 
a persistence layer in a managed environment (like a j2ee conform 
appServer) it is not possible for the persistence layer to restart the 
tx, because tx demarcation is handled by the container or bean or by the 
client using UserTransaction(and tx could be a distributed tx).

So +1 for improving exception handling like you suggest, but -1 for all 
stuff like automatic tx redo on persistence layer level. Or I'm wrong 
and Slide could handle this in managed environments?


> Slide also has some internal locking which can be used
> to either completely eliminate deadlocks (at the cost of no
> concurrency while writing) or at least limit its likelyhood.
> 

PB-api only supports optimistic locking. The odmg-api supports
pessimistic + optimistic locking (with configurable lock modes).
Pessimistic locking should be able to prevent DB deadlock (dependent on
odmg lock mode and DB lock mode).


> However, it turned out that when using low isolation levels there may
> be constraint violations which would not occur if you had your
> transactions serializable. They would have to be treated just like a
> deadlock or "can not serialize error".
> 
> As this really depends on your database schema and constraints I guess
> it is impossible to do this in a completely generic way.

agree, in the Platform classes #createException(...) method we can only 
use method arguments like
- the SQLExeption
- the connection itself or the connection transaction isolation
- the causing object
- metadata of causing object



> I have been
> told that Hibernate (2.1.7) supports a mapping with an (XML?)
> configuration file:
> 
> http://forum.hibernate.org/viewtopic.php?p=2223974
> 
> Maybe as a default all of the Exceptions mentioned above should cause
> the transaction to be redone?

see beginning of response, maybe I misunderstand you ;-)


> On the other hand contraint violations
> really can indicate programming errors...
> 
> I know it is always tricky to ask for new features without
> contributing.

No problem, you can ask, but you shouldn't be expect a implementation 
guarantee for the new features ;-)


> So, to get this going do you want me to prepare a patch
> for OJB which does exactly this: Introduce the createException method
> in the plattform class plus the exceptions and the initial code to
> feed it reasonably for Postgres, MySQL, SQLServer, and Sybase (is
> Sybase supported by OJB in the first place?)?

+1 this will great!

> Maybe as a seconed step
> there could be a configuration mapping file ala Spring or Hibernate?
> 

+1 This could be an option for OJB 1.1

regards,
Armin

> Oliver
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Deadlocks mapped to common exception?

[Oliver Zeigermann <ol...@gmail.com>]

On Thu, 09 Dec 2004 10:39:31 +0100, Armin Waibel <ar...@apache.org> wrote:
> Hi Oliver,
> 
> Oliver Zeigermann wrote:
> 
> >>can anyone tell me if OJB is able to find out that a certain SQL
> >>exception really is a deadlock exception and maps it to another common
> >>one (DeadlockException)? This would be very helpful to repeat a
> >>deadlocked transaction. I found no code in OJB that might do such a
> >>job.
> >>
> 
> You are right. OJB wraps all SQLException as
> PersistenceBrokerSQLException (except key constraint errors here OJB use
> a specific exception). See JdbcAccessImpl#executeInsert. PBSE is an
> unchecked exception and with #getCause() you can extract the SQLException.
> 
> 
> >>If there is nothing in OBJ the Jakarta Slide project has some initial
> >>code that does this for some dbs. Maybe this could be integrated in
> >>OJB?
> >>
> 
> hmm, I only found some code in PostgresRDBMSAdapter#createException.
> Were can I find the common code for deadlock handling?

Until now code to detect an exception really is a deadlock exception
is available for Postgres, MySQL, SQLServer and Sybase only. Slide
also supports DB2 and Oracle, but the code for them still is missing.

The rest of the code merely catches the DeadLockException at top
level, rolls back the transaction, starts a new one and repeats the
whole request. Slide also has some internal locking which can be used
to either completely eliminate deadlocks (at the cost of no
concurrency while writing) or at least limit its likelyhood.

>  > Is this just nonsense or is it that no one is interested? How do OJB
>  > users handle deadlocks?
> 
> I think most people catch PersistenceBrokerSQLException to handle the
> SQLException and the different SQLSTATE.
> 
> It will be no problem to introduce a common method for handling
> SQLException in Platform class based on the SQLSTATE of the
> SQLException, e.g.
> Platform#createException(SQLException e)
> which return different unchecked exceptions inherited from PBSE (e.g.
> KeyConstraintViolation, DeadlockException, TimeoutException, ...).
> 
> Would this be helpful?

Yes! 

However, it turned out that when using low isolation levels there may
be constraint violations which would not occur if you had your
transactions serializable. They would have to be treated just like a
deadlock or "can not serialize error".

As this really depends on your database schema and constraints I guess
it is impossible to do this in a completely generic way. I have been
told that Hibernate (2.1.7) supports a mapping with an (XML?)
configuration file:

http://forum.hibernate.org/viewtopic.php?p=2223974

Maybe as a default all of the Exceptions mentioned above should cause
the transaction to be redone? On the other hand contraint violations
really can indicate programming errors...

I know it is always tricky to ask for new features without
contributing. So, to get this going do you want me to prepare a patch
for OJB which does exactly this: Introduce the createException method
in the plattform class plus the exceptions and the initial code to
feed it reasonably for Postgres, MySQL, SQLServer, and Sybase (is
Sybase supported by OJB in the first place?)? Maybe as a seconed step
there could be a configuration mapping file ala Spring or Hibernate?

Oliver

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Deadlocks mapped to common exception?

[Armin Waibel <ar...@apache.org>]

Hi Oliver,

Oliver Zeigermann wrote:

>>can anyone tell me if OJB is able to find out that a certain SQL
>>exception really is a deadlock exception and maps it to another common
>>one (DeadlockException)? This would be very helpful to repeat a
>>deadlocked transaction. I found no code in OJB that might do such a
>>job.
>>

You are right. OJB wraps all SQLException as 
PersistenceBrokerSQLException (except key constraint errors here OJB use 
a specific exception). See JdbcAccessImpl#executeInsert. PBSE is an 
unchecked exception and with #getCause() you can extract the SQLException.


>>If there is nothing in OBJ the Jakarta Slide project has some initial
>>code that does this for some dbs. Maybe this could be integrated in
>>OJB?
>>

hmm, I only found some code in PostgresRDBMSAdapter#createException. 
Were can I find the common code for deadlock handling?

 > Is this just nonsense or is it that no one is interested? How do OJB
 > users handle deadlocks?

I think most people catch PersistenceBrokerSQLException to handle the 
SQLException and the different SQLSTATE.

It will be no problem to introduce a common method for handling 
SQLException in Platform class based on the SQLSTATE of the 
SQLException, e.g.
Platform#createException(SQLException e)
which return different unchecked exceptions inherited from PBSE (e.g. 
KeyConstraintViolation, DeadlockException, TimeoutException, ...).

Would this be helpful?

regards,
Armin

>>Oliver
>>
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Deadlocks mapped to common exception?

[Oliver Zeigermann <ol...@gmail.com>]

Is this just nonsense or is it that no one is interested? How do OJB
users handle deadlocks?

Oliver

On Sun, 5 Dec 2004 01:23:17 +0100, Oliver Zeigermann
<ol...@gmail.com> wrote:
> Folks,
> 
> can anyone tell me if OJB is able to find out that a certain SQL
> exception really is a deadlock exception and maps it to another common
> one (DeadlockException)? This would be very helpful to repeat a
> deadlocked transaction. I found no code in OJB that might do such a
> job.
> 
> If there is nothing in OBJ the Jakarta Slide project has some initial
> code that does this for some dbs. Maybe this could be integrated in
> OJB?
> 
> Oliver
>

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org