I prefer PC death to be both final, and very rare. I dislike casual resurrection, and disposable characters.
What there really needs to be is a good way to have "players fail to achieve their objectives, but they live to fight another day". I'd rather the tension be on "do we succeed?", than on "do we survive?". But it's hard to have those situations, especially in games where the mechanical focus is so strongly on combat. Once a combat starts, it gets hard to justify it ending in any way other than one side being dead.