There's a fundamental mismatch between the HTTP request-response model and the SQL "open transaction" model - you can't open a transaction, serve a form, and wait for a user to submit it and commit that in the same transaction, because what if the user walks away? But if a transaction doesn't extend to the actual user action then it's basically useless; you can't do transactional read-modify-write when the one doing the modifying is the user.
After all, what do you do when a transaction fails? Most webapps either retry or fail (if they even bother to handle that case at all), but you don't need transactions to do either of those. In an actual database application you can tell the user their transaction didn't commit and ask them to deal with it, but again you can't really do that over the HTTP request/response cycle (because what if the user submits the form and walks away? They expect their input to be saved, whatever happens on your backend).
What is the alternative? Use distributed databases that don't guarantee atomicity at all, and write up complicated solutions to work around it? Or are there some non-transactional, atomic databases that more nicely fit the web model that I don't know about?
You have to write the "complicated solutions" anyway. SQL just lets you paper over the problem for longer. 99% of webapps lose data and don't notice or care, ACID is just a stick to beat other datastores with. IME something like Cassandra offers far more actually useful advantages (collection columns, true master-master HA out of the box) than an ACID/SQL database ever can.
After all, what do you do when a transaction fails? Most webapps either retry or fail (if they even bother to handle that case at all), but you don't need transactions to do either of those. In an actual database application you can tell the user their transaction didn't commit and ask them to deal with it, but again you can't really do that over the HTTP request/response cycle (because what if the user submits the form and walks away? They expect their input to be saved, whatever happens on your backend).