Webservice calls within the same JDBC transaction are causing DB lock timeouts

I'm using the H2 database embedded within a Spring-MVC app.

I have declare Transactions at my service level. In particular 开发者_运维百科I have a case where we do the folllowing:

1. Start a transaction
2. Make a webservices call
3. Make the subsequent database calls
4. Upon rollback (an exception), the webservices call is manually rolled back, and the Spring Transaction Manager handles calling a rollback on the DB transaction.

But I realized that the existence of the webservices call within the DB transaction is causing a table-wide lock, which causes other users to receive errors (I can produce this on a 1-user system with 2 browsers).

I am trying to plot my best course of action.

• Should I be changing the transaction isolation level in Spring, will that affect the DB locking?
• Is it just bad form to have a webservice and DB call in the same transaction?
• Should I enable row level locking within the H2 DB because of this?
• Am I not thinking of other solutions?

---

I should add that my service method serviceA() calls two other methods webServiceX() and daoMethodY(). serviceA() is encompassed in a transaction because any exception needs to rollback both the webServiceX() (a function I provide), and rollback the daoMethodY() database operations (a function of the DataSourceTransactionManager).

---

I think your approach is reasonable, and you should definitely try row-level locking if it's possible.

You might want to reconsider your design. Is the database duplicating state which really comes from the web service? In that case, you might want to consider caching the web service calls instead. But that depends on your application.

Alternatively, you might just have to roll your own transaction management. As long as it's generic, it shouldn't be too much trouble. We've done this in our project where we're not using Spring's transactions. Something like

performTransaction() {
    doWSCall();
    // no need to worry about WS call exception, because DB call won't happen
    try {
        doDbCall()
    } catch (Exception ex) {
        rollbackWSCall()
        // rethrow ex
    }
}

where all the methods are abstract.

---

I wouldn't have a web service call mingled into a database call. Your method is breaking the rule that says "do one thing well".

Have your service call other web services and the DAO.