Working Code Podcast - Episode 040: Are Database Transactions Overrated?

@Danilo,

This really hit a nerve:

Of course, the default implementation fails so infrequently, that devs haven't been bitten enough by the pain of their database interactions failing.

I feel like this applies to so many aspects of application architecture in general!. Even with relational databases, I've (and my team) have fallen victim to this problem.

In fact, just earlier this week, someone from another team was pointing out some "dirty data" that we seemed to have in one of our tables: two records that looks exactly the same (less the primary key). And, when I took a look at the code, I could see that there was no transaction wrapping the logic that checked to see if a matching record existed before inserting the new one. Clearly, some user double-clicked a submit button and our server-side code wasn't coded to protect against the double-submission in rapid succession.

And, as you point out, in 99.99% of cases, that workflow never fails even without the transaction. So, the developer who wrote it never noticed the issue.

Now, I'm sure we could have also prevented something like this by included certain columns in a unique index. But, that's not always easy. In this case, we were trying to enforce "unique names" (to reduce user confusion). And, I don't love the idea of having to include a "name" column in an index (plus, I'm not entirely sure you can index an entire varchar field - I think only upto a certain size). As such, we tend to lean on transactions to help enforce that kind of business logic.

@Danilo,

All excellent suggestions 💪

@All,

On Twitter, Mat Keep pointed me to a whitepaper he wrote on MongoDB transactions. In it, there is some discussion on how powerful transactions are in a MongoDB-based application:

MongoDB already served many of these use cases before the addition of multi-document transactions. Now it is even easier for developers as the database automatically handles multi-document transactions for them. Before their availability, the developer would need to programmatically implement transaction controls in their application. To ensure data integrity, they would need to ensure that all stages of the operation succeed before committing updates to the database, and if not, roll back any changes. This adds complexity that slows down the rate of application development. MongoDB customers in the financial services industry have reported they were able to cut 1,000+ lines of code from their application by using multi-document transactions.

In addition, implementing client side transactions can impose performance overhead on the application. For example, after moving from its existing client-side transactional logic to multi-document transactions, a global enterprise data management and integration ISV experienced improved MongoDB performance in its Master Data Management solution: throughput increased by 90%, and latency was reduced by over 60% for transactions that performed six updates across two collections. Beyond client side code, a major factor in the performance gains came from write guarantees.

This makes me feel that perhaps we are all more on the same page than I originally had thought. As I was saying to Mat on Twitter, the vast majority of my SQL queries don't have transactions; but, the critical ones do because I like to lean on the database for enforcement of certain constraints. I think what I was reacting to was the idea that "90% of developers don't need transactions", vs something that might be more palatable, "90% of queries don't need transaction".