Hibernate multithread transactions problem in high concurrency environment

Hi all, it is not easy to describe the problem I am facing, so please be patient :-)

The Spring/Hibernate core layer of the application serves millions of users with extremely high concurrency and very high number of database operation per seconds. One of the main phases of the application flow involves multiple database tables, either by reading and inserting/updating. I manage transactions programmaticlly with a transaction template (not with AOP), with this properties:

Code:

---

- isolationLevelName = ISOLATION_REPEATABLE_READ
- propagationBehavior = PROPAGATION_REQUIRED

---

Here is the pseudo-code of the class:

Code:

---

// insert on table1
myObject = transactionTemplate.execute() {
        insert record on multiple tables. The one that I care about for my test is table1.
}

---

Code:

---

// insert on table2
transactionTemplate.execute() {
        doOnOtherTable(myObject);
}

---

the doOnOtherTable method is structured as follows and it is supposed to update some counter and other stuff on another table (at the end, the number of the rows inserted in the first transaction (the one that returns myObject) must be equal to the counter inserted by the second transaction:

Code:

---

public Foo doOnOtherTable(Object myObject) {
        Foo foo = fooDAO.getFooByObjectUniqueField(myObject.getUniqueField());
        if(foo == null) {
                Foo newFoo = new Foo();
                newFoo.set(...)

                try {
                        fooDAO.saveFoo(newFoo);
       
                } catch(DataIntegrityViolationException e) {
                        foo = fooDAO.getFooByObjecyUniqueField(myObject.getUniqueField());
                        return (Foo) transactionTemplate.execute() {
                                Foo fooToUpdate = fooDAO.getInLockModeUpgrade(foo.getPrimaryKey());
                                fooToUpdate.set(...);
                                fooDAO.updateFoo(fooToUpdate);
                        }
                }

        } else {
                foo = fooDAO.getFooByObjecyUniqueField(myObject.getUniqueField());
                return (Foo) transactionTemplate.execute() {
                        Foo fooToUpdate = fooDAO.getInLockModeUpgrade(foo.getPrimaryKey());
                        fooToUpdate.set(...);
                        fooDAO.updateFoo(fooToUpdate);
                }
        }

        return fooDAO.getFooByObjectUniqueField(myObject.getUniqueField());
}

---

If I execute this code in a multihread environment I am supposed to insert 600 rows on the table1, and a counter on table2 must be 600, but I get 600 rows on table1 and almost 450 on table 2. The exception I get is this one:
null id in table2DTO entry (don't flush the Session after an exception occurs). I cannot flush the Session manually. If I execute this code monothread everything is fine.

I tryed to use a different transaction template (transactionTemplate2) (with isolation level = SERIALIZABLE and propagation = REQUIRES_NEW) for the table2 operations, and the results are better: 600 rows on table1 and counter = 520 on table2.

I tried to remove the transaction containing the doOnOtherTable() method, just to see if something changed, and everything is fine using the transactionTemplate2. But this is not acceptable, because I need also doOnOtherTable2(myObject) and doOnOtherTable3(myObject) in the same transaction. If I add also these two methods the situation gets even worse.

Theorically, all the methods doOnOtherTable/2/3, should be done inside the first transaction, the one iserting rows on table1. Table1 is more critical than the other tables, so a rollback in the doOnOtherTable methods would cause a rollback on table1 as well, and I cannot permit this (this is why I splitted the transaction in two differents ones, for the moment).

Hope someone could help me out.

Thanks in advance,

Fabio