It is possible that with sufficient investment in tools for writing safer code, unoverridable smart contracts will become useful enough to outweigh the risks.

It's also possible (and I feel that this will be the case) that in the end the risks will be too large anyway for large adoption, so we'll collectively decide that for most use cases smart contracts are useful only with a "social" mechanism for overriding them, contrary to the current "code is law" policy of Ethereum.