EngineSmith's Blog

Engineering Craftsman

MySQL JDBC Multiple Query Trap

Posted by EngineSmith on January 20, 2011

This is a pretty big lesson recently. We are using MySQL JDBC driver against 5.1 Percona build. For a simple use case, we decided to use transaction (though we know it doesn’t scale well). Though stored procedure can be used, we chose to use a trick in JDBC driver: allowMultipleQueries. http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

Basically, the following statements are put together as one iBatis query: begin; insert into A …; update B where ….; end;

This thing worked perfectly fine for several months, until one day, after new release, suddenly our MySQL started having trouble, it began to show errors of deadlocks. Tons of random queries are grouped together into big transactions and causing deadlocks. We scratched our head for a long time since absolutely nothing has changed in that release related to this transaction logic, neither did we touch MySQL or change JDBC driver. Seems the transaction boundary was extended randomly outside of the those two above queries, and since we use connection pooling (BoneCP), consequent queries on the same connection were combined into big transactions. This is really terrible!

Eventually we took out this allowMultipleQueries trick and did everything in plain two step SQLs (yeah, no transactions). Until today, we still don’t know exactly what triggered the problem since it was working fine for several months.

By the way, a side note about transactions. It sounded like a perfect solution on paper, in reality, especially web world (where you have sharding, many database nodes), it doesn’t work. One perspective to look at is: modern hardware/network is quite reliable nowadays, comparing to the cost to ensure transaction, probably it is better to spend the time/money on ways to fix things (tool, customer support, reconciliation) if a transaction was interrupted. It will also make your system much simpler and easier to scale.

Forgot to mention, in 2004, Paypal had an outage for almost a week due to some smart guys introduced two-phase commit into their Production system. Great idea to guarantee ACID, also a text book example use case (banking transaction). Sadly, in reality, it doesn’t work.

Advertisements

3 Responses to “MySQL JDBC Multiple Query Trap”

  1. i like it MySQL JDBC Multiple i interrogative Trap « EngineSmith's Blog now im your rss reader

  2. It truly is rare to find an expert in whom you can have some trust. In the world at present, nobody actually cares about showing others the answer in this subject matter. How fortunate I am to have now found such a wonderful site as this. It really is people like you who really make a true difference in this world through the strategies they reveal.

  3. I simply desired to appreciate you once more. I’m not certain the things that I could possibly have handled without the entire techniques revealed by you relating to such subject matter. It has been the horrifying difficulty in my opinion, however , viewing the specialized tactic you processed that took me to weep over gladness. Now i’m happy for your work and thus have high hopes you really know what a powerful job you’re doing teaching some other people through the use of your web page. Most likely you haven’t come across any of us.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: