<p>I really want to use SimpleDB, but I worry that without real locking and transactions the entire system is fatally flawed. I understand that for high-read/low-write apps it makes sense, since eventually the system becomes consistent, but what about that time in between? Seems like the right query in an inconsistent db would perpetuate havoc throughout the entire database in a way that's very hard to track down. Hopefully I'm just being a worry wart...</p>

This is the pretty classic battle between consistency and scalability and - to some extent - availability.  Some data doesn't always need to be that consistent.  For instance, look at digg.com and the number of diggs against a story.  There's a good chance that value is duplicated in the "digg" record rather than forcing the DB to do a join against the "user_digg" table.  Does it matter if that number isn't perfectly accurate?  Probably not.  Then using something like SimpleDB might be a good fit.  However if you are writing a banking system, you should probably value consistency above all else. :)
Unless you know from day 1 that you have to deal with massive scale, I would stick to simple more conventional systems like RDBMS.  If you are working somewhere with a reasonable business model, you will hopefully see a big spike in revenue if there's a big spike in traffic.  Then you can use that money to help solving the scaling problems.  Scaling is hard and scaling is hard to predict.  Most of the scaling problems that hurt you will be ones that you never expect.
I would much rather get a site off the ground and spend a few weeks fixing scale issues when traffic picks up then spend so much time worrying about scale that we never make it to production because we run out of money. :)