Has there ever been a successful double spend attack on the Bitcoin network? If not, is it really necessary to wait for confirmations?
For what it's worth, MyBitcoin.com claimed that their closure was precipitated by a series of double-spend attacks:


  Our programmer was under the assumption that one block was good enough
  to secure a transaction. Two years ago when the software was written,
  this single confirm myth was a popular belief.


User theymos (an administrator of the bitcointalk.org forums) had publicly warned them that accepting transactions after a single confirmation was "insane."

However, there is no way to verify whether or not any double-spend attacks have actually taken place because to my knowledge no one is currently scanning for them on the network (doublespent transactions are normally discarded after the event is resolved to the satisfaction of the block-chain).  All we can observe publicly are block-chain reorganisations which also naturally occur from time to time when blocks are found near-simultaneously by different miners or pools.  Perhaps a statistical argument could estimate how often these should be occurring (we've had 14 in the past month) and argue for the existence of double-spend attempts on that basis, but I've not personally seen such an analysis.

Most critically to your question about whether or not to wait for confirmation, however, is the fact that you have missed a possible attack vector, known as a "Finney Attack" after Hal Finney who identified it.  In a Finney Attack, a miner includes one "double" of the two transactions in their own blocks while mining, but does not broadcast it to the network.  When they eventually find a block, they purchase something with a conflicting transaction (the second "double" in the double-spend attack), and then immediately broadcast their block, which will invalidate the purchasing transaction in the blockchain.  If the merchant hasn't waited for confirmation, the attacker would walk away with both the purchased item (hopefully something cashlike) and their original bitcoins.

Since this situation and its variants apply largely to merchants who offer 'on-demand' items delivered instantly, your analysis of "everyday users transferring small amounts of bitcoins" remains roughly correct.  Keep in mind that if accepting 0-confirmation transactions became the norm, the incentive for the "send them simultaneously" attack would rise significantly (requiring even one confirmation prevents this in the vast majority of cases).  But for the time being, regular users should be more worried about regular old "you-pay-first" fraud on non-escrowed transactions, and man-in-the-middle attacks where an interlocutor passes messages between two trusted parties looking to do business, then walks away with the cash while they both yell that the other person is scamming them (this attack has been executed in the wild).

tl;dr Merchants and exchanges definitely need to wait for confirmations any time transactions are of significant value and/or in exchange for cashlike goods.  Individuals transferring small amounts need to be more worried about other aspects of trust in the exchange, and can protect themselves quite effectively with just a single confirmation.