Folks,

can anyone tell me if Hibernate 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. Of couse this would require different implementations for differents dbs...

Judging from Hibernate code and posts on this list there is no such option. If I am right, why not? Just no done yet or for philosohpical reasons?

Thanks,

Oliver

Well Hibernate could do this, I suppose. But each DBMS has a different set of error codes that mean "deadlock" so the developers would need to research all these codes.

You should look at the Spring framework which does this nicely, translating custom error codes from a variety of DBMSs into a generic hierarchy of non-checked Exceptions. (Though it doesn't do it for Deadlock exceptions out-of-the-box, it's possible to customise it by writing your own sql-error-codes.xml file).

HTH

Craig

Craig,

thanks for the reference to the Spring framework. I thought deadlocks are so central and as Hibernate already has to deal with all those different DBMS' I though it would be suitable. I am wondering how do Hibernate users deal with deadlocks then as they are so common?

There is some initial code for this coming from another project I work on that I could adapt to Hibernate if anyone is interested.

Or is this out of Hibernate's scope?

Oliver

Exception convertion like in Spring is currently under construction and will probably be in Hibernate3

It's already in 2.1.7.

Hi Christian

I'm using 2.1.7, but when I encounter a deadlock in MySQL, all I see is a:

net.sf.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update

and the nested exception is:

java.sql.BatchUpdateException: null, message from server: "Deadlock found when trying to get lock; try restarting transaction"


The MySQL SQL error code is 1213.


Do I have to do something special to get this to work?


Actually, I've just had a look at the source - the MySQLDialect class, and I don't see any 1213 error code. Additionally, what sort of exception gets thrown for a Deadlock as there doesn't seem to be a specific Exception subclass in the net.sf.hibernate.exception package.


Craig

Oh, perhaps I misinterpretted your comment, Christian. I see that exception translation is there in 2.1.7, but just nothing specific for deadlocks. Will this be in 3.0?

Just contribute to the Exception translation code ... patches are always welcome.

Yes, you just have to add that to the mapping in the dialect.

Hmmm, I have been thinking about this and depending on the isolation level there are quite a number of exception codes that might indicate the transaction should be repeated. E.g. if we have low isolation it may well happen that constraint violations occur that would not if we had serializable as isolation level.

In short: this does not seem to be that simple.

Anyway, this is what I have so far. Maybe anyone can make use of it:

MySQL:
- 1213: Deadlock victim

SQLServer (also Sybase):
- 1205: Deadlock victim
- 547: Constraint violation referential integraty
- 2627: Constraint violation primary key

Postgres (details http://www.aeria.net/postgres/postgresq ... endix.html):
- 40*: All kinds of deadlock or "can not serialize"
- 23*: All kinds of constraint violations

Oliver

Also short: You know that "retrying" doesn't make the problem of deadlocks, etc. go away. It makes it worse, in my opinion. Deadlocks appear in a system under high load. Hammering that system with retries is not going to help. Fail immediately and inform the administrator.

Can you repeat that statement with an example. I think this is not possible.

I disagree. What should the administrator do? Nothing...

Agreed: Deadlocks never go away, they just happen. But sometimes they are just bad luck. And if you have carfully designed your database schema they should occur rarely even under high load. In this case repeating a deadlocked statement might just bring you to the desired result. Maybe a limitation on the number of retries (1 as default?) would be good.

Be careful not to mistake livelocks (with timeouts) with deadlocks.

Oliver

Consider I have the isolation level read uncommitted (pretty bad, I know). Now "tx a" adds a new user, writes data to the users table and also to a second table that has a foreign key to this newly added user.

"tx b" reads this data from the second table and tries to find the referenced data from the user table. Now consider "tx a" rolled back between those two statements undoing all its changes. Now the information from the second table is invalid causing a referential integraty constaint violation.

This would be impossible with isoaltion serializable. If this is not obvious or I am wrong I will explain upon request.

Oliver

You are right. I never considered read uncommmitted a valid transaction isolation mode.