Basically you're pretending to be a terrorist, you have to shoot up an airport full of civilians (who die very realistically, beg for their lives, etc.), and then at the end of the level the main terrorist whose cell you're trying to infiltrate shoots you in the face and you die.
The thing is, in a multiplayer FPS, the "killing" is just a competitive metaphor. The mechanics for "killing" are oft-remarked as being very unrealistic, and the "death" is just a temporary delay before respawn. It's a kill in name only.
As far as the single-player mode goes, I can't speak from experience... and neither most other CoD players, I wager.
I had multiplayer in mind - most call of duty players do not play campaign. But yes, that scene was debated a lot.
I think that scene is something that makes Call of Duty: Modern Warfare 2 a game for adults, the same way as ear cutting scene in reservoir dogs makes it something for adults.
As far as I know, other call of duty games do not have such scene. I may be wrong on that one through.
It's slightly possible to do the level without shooting the civilians yourself (your terrorist buddies can kill them for you), but you do have to kill the Russian airport police who show up to stop you. If you refuse to shoot them, the battle just continues forever until you open fire.
Basically you're pretending to be a terrorist, you have to shoot up an airport full of civilians (who die very realistically, beg for their lives, etc.), and then at the end of the level the main terrorist whose cell you're trying to infiltrate shoots you in the face and you die.