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.