Hmm, this could be a long response
First, as a player, I usually do everything I can to keep my characters alive. I plan ahead, I make contingencies, I keep escape routes open, and if at all possible, I tilt the odds in my favour in every battle I can. I've generally been pretty successful with this approach: I do sometimes lose characters, but at a comparatively slow rate.
All that said, despite my aversion to character death, as a player I want to know that the possibility exists. Survival needs to be a challenge, and something to be worked toward, for it to have value to me. Why is this? Well, oddly enough, it's because I don't particularly enjoy combat
I can roleplay with or without danger to my characters, and derive a lot of enjoyment from doing so. Portraying the awkward homecoming of my Dwarf Wizard ("why couldn't he have a respectable job, like mining?") is fun for me, in and of itself.
When we get into combat, on the other hand, I need it to be more than just a bunch of dice-rolling, or I won't be interested. I need the intellectual challenge of "how do I stay alive and beat the enemy as effectively as possible?". Fortunately, the d20 combat system is flexible enough to make this an interesting challenge.
I'm curious - of the people who don't like character death, do you do use the full combat system, or cut it down somehow? If I were forced to sit around rolling dice for an hour in a combat I knew couldn't kill me, I'd get bored.
An aside - I don't know how common it is in the US, but a lot of convention games in Australia have an option of playing them in 'cinematic' style, which means that you chuck out the rules system and run by GM fiat. This turns combat into another form of roleplay, as you describe your actions and the GM rules on the fly whether it work. It can be quite successful, if done well. Some people use it in their home games, too.
As a GM, I generally tend to kill people only where:
- they make foolish and dangerous decisions; or
- I've clearly flagged the encounter as one of significant danger, beforehand
For instance, let's say I'm running a game and two of the encounters are a skirmish with some Orcish raiders (a minor reminder to the PCs that there is a looming war with the Orcs) and an encounter with a necromancer the PCs have been tracking for three sessions, and whose handiwork they have seen several times before. They know the necromancer travels with a pack of ghouls.
In the former encounter, I'd play the orcs in a disorganised, but not entirely stupid, fashion. They're loud, brash and direct, but they aren't just cannon-fodder. Still, as long as the PCs don't do anything stupid, they'll just take a few lumps - no deaths.
On the other hand, when they meet the necromancer and his ghouls, they'd better have a good plan, and suitable preparation, or someone will be undead chow. This is an encounter that's been building for several sessions, and I've given them some specific information about their foe. Failing to use that information, and to make ready for the upcoming battle, will lead to character death.
So my basic position is: the threat of death should be real - particularly in 'major' encounters - but proper planning and care should mitigate and reduce (though never entirely eliminate) the possibility.
