Yes, that is certainly a concern.
The databases we use internally to run our centralized services (such as the Webpanel: https://panel.dreamhost.com) host only our databases and only our code interacts with them. In that sense, it's a very closed environment, easy to control. We rarely (never?) lose those connections, because we are in control.
However, in a more open environment (like the machine that hosts your database along with hundreds of other customer databases), one bad user can potentially bring the machine to a halt. This rarely happens as well. We've put safeguards in place recently (such as limiting the number of connections a single user can consume). However, such calamities do happen. The only way to avoid it currently is to purchase a dreamserver (www.dreamservers.com).
Having said that, many users utilize our non-transactional MySQL service to store sensitive information that need be in a consistent state (for example, financial information). While a non-transactional db is not ideal for such functions, I think it is fine for the vast majority of our users.
Are you referring to Atomicity Consistency Isolation Durability?
Or are you referring to some other program/utility named ACID?
Yes, I'm familiar with the former. I understand your concerns (we've had them too!). Maybe you could elaborate on the type of application you plan on using. One can often write code to more safely handle sensitive data that needs to be in a consistent state, and many applications already have that intelligence written in.
You might also want to read the following:
That's perfect! So long as they test each connection occassionally (or each time) before performing a query, you should be safe and fast.
Volume of queries shouldn't matter that much (until you get to hundreds or thousands of queries per second).
I think you'll be fine with a non-transactional DB, but then again, you should never trust your hosting company! =)