At least for me, I'm not at all an expert in how to do transactions. If I wanted to store data, and have it remain self-consistent with some schema, wouldn't it be easiest for me to start with using a DB that already provided consistency-features? It could be that it will be fast enough for my purposes, no? Wouldn't it be premature optimisation for me to go with a system that provided more performance by not providing consistency? While a good developer who's been around data-bases and relational data storage a good while may know how to implement consistency themselves, what are the chances of arbitrary developers being able to better implement consistency themselves than the database developers?
Unless of course the argument is that often the performance-impacting consistency guarantees are not required. But then, aren't ACID DBs tweakable? You can make, e.g., PostGres play a lot more loose with data than it does by default, I gather, and gain a lot of performance.