Tony and I just got done with an 8 hour debug fest of our DB code. This was rather brutal and finally we ended up with a mess of souts (IJ users will know these as System.out.printlns) to track it down. The issue was that if I start a transaction and then call to another method or service that starts a separate transaction to insert some data, the original transaction won’t ever see that data.

At first we were thinking this was some hibernate magic that was caching negative selects or something, but using our trusty souts we found that the problem was at the JDBC connection level. We figured this out by printing out the hashCode of the JDBC connection objects as well as the hibernate session objects to see what Spring was doing with transactional boundaries.

Turns out that MySQL 5.x using InnoDB and Max by default sets the connections isolation level to REPEATABLE-READ! A repeatable read means that at the nanosecond the transaction is started MySQL in essence takes a snapshot of the entire database. Any select you perform will always return the same data no matter what other connections and transactions are doing to the database. This is great for super-uber-financial-debacle applications, but for our CRUDesque web app this is complete overkill. So, updating /etc/my.cnf to include a new transaction-isolation setting set to READ-COMMITTED fixes this nicely.