8 hour mysql debug fest

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.

One thought on “8 hour mysql debug fest

  1. Never understood why anyone would use MySQL in the beginning. Postgres is the greatest open source database. Free, robust, much better standards support, no commercial MySQL AB crap, loved by the academic community, etc It’s always had nested subselects, transactions etc.

    have you ever looked at the MySQL code itself? Yuk.

    Google “mysql vs postgres” for some scary reading for mysql users.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s