Hibernate version: 3.2

Hello,

please, could you tell me how can I set individual transaction isolation level? I just cannot find the api call, only the general config property.

I also wanted to know if it's possible to change the level to a more strict one in the middle of a transaction. Thanks.

jv

Even at the JDBC level, you can only specify the isolation level at the start of a transaction, before the first db access. The database needs to know what locking is needed for the very first read access. At the JDBC level, you can use the API

Connection.setTransactionIsolation(int level), where level = TRANSACTION_SERIALIZABLE, etc.

If you are running in an application server, you are using Connections owned by it, and should not try to manipulate the isolation level at runtime. You can add pessimistic locking as needed to your base level, or run at serializable and make multiple small transactions to lessen contention, etc.

Thanks a lot for your kindly answer. I'm no way a DBA but I infer you're telling me it's not a good practice to decide what kind of isolation is more suitable for each transaction no matter it's nature. It doesn't look very intuitive but explains why there isn't any easy way to do it.

Thanks again.

jv

why do you need to set it anyways?


Farzad-

Actually, at the database level it is OK to have different transactions run at different isolation levels. It's only because of the Connection pooling of the application server that makes us stick to one level. If you are using a Connection directly from the JDBC driver you can use multiple levels.

Thanks eoneil & farzad.

I would find interesting to be able to specify the lesser resource intensive isolation level as a default but some times you need a serialized isolation (coded as 8, if I remember it right).

And I would like to continue using hibernate in those cases, without needing to work at connection level.

Any suggestion appreciated ;-)

jv

Is it possible for you to define two data sources each of which has the specific isolation level you want? Then you could use two session factories for each isolation level. However, you will definitely need to use distributed transactions if these two scenarios can be used together.


Farzad-

If you're trying to get good performance, it's good to avoid distributed transactions if possible.

The bad performance from serializable isolation, if any, comes about from long-held locks (rather than the overhead of *creating* the locks, which is almost always negligable). So if you run at serializable, and add more commits to your less-critical code, you should be able to get good performance and still have serializable where you need it.