How does bitcoin handle unspent outputs from fresh transactions? I read that Satoshidice uses one of the outputs from the bet transaction to fund the response transaction. 


Can anyone provide explanation/reference to how bitcoin handles such transactions.
What is the rationale of allowing such transactions? 
If the protocol did not allow unconfirmed unspent outputs to be used, would that make bitcoin unusable?
Bitcoin protocol doesn't care how many confirmations a transaction has. A user can choose any number of blocks that has to be mined on top of the transaction before considering it confirmed, or in case of Satoshidice it simply doesn't care.

Bitcoin protocol does care that source addresses of a transaction has all required coins to transfer to destination addresses, in other words a transaction has to have all it's inputs to be valid and the number of coins those inputs have must be greater or equal to number of coins in outputs. Therefore if an attacker successfully double spends his transaction to Satoshidice it will also invalidate winning transaction from Sathoshidice, because one of the inputs of winning transaction is the original transaction from the attacker to Sathoshidice.

To answer the question why bitcoin protocol allows it, consider two transactions 


From address A to address B
From address B to address C


To think about second transaction as confirmed you need to wait a certain number of block to be mined on top of it and it doesn't matter how many block were mined on top of the first transaction because it doesn't make second transaction any more secure. Therefore both transactions can appear in the same block and that's completely fine.