I will be listing 5 examples including unbeatable boss battles, scripted narrative choice playouts and endgame downers. I will be explaining the plot points that lead to these moments in detail. Be aware of major spoilers.
God of war II: beginning Boss
In the epic opening of God of War II, you get to play out a power fantasy almost instantly by repelling a city raid and acting like an all-round badass. You kill soldiers in a few hits and are almost maxed out in terms of abilities. This is all stripped from you after Kratos takes a heavy blow by a colossus. Bloodied and heavily injured, Zeus suddenly appears and goes all Cronus, thinking you will overtake him. He demands for your undivided loyalty but Kratos rejects it, ensuing an impossible battle between him and Zeus. You barely have a chance to fight and your attacks are slow and weak, leading to Kratos being killed in the following cutscene. This serves as the perfect drive for revenge for the rest of the game after Kratos is brought back to life.
Batman arkham ASYLUM: QuickTime event
At some point in Arkham Asylum, the game seemingly crashes and restarts to the beginning. Instead of escorting villain Joker to the asylum, Joker is escorting Batman. Soon after he is strapped into the patient chair, The Joker points a gun. The player is seemingly in control of the Caped Crusader again with a message stating to "press the middle thumbstick to dodge Joker's bullet". There is, of course, no such thing as a 'middle thumbstick', and the death actually takes you back to the story to reveal that Scarecrow's toxins induced this sequence. It is a clever shakeup and a totally unexpected part to the classic superhero game.
Mass effect 2: opening
The explosive introduction to Mass Effect 2 pits the player in the crew's spaceship Normandy as it is being attacked. Flames sorround Commander Shepherd as he tries to do whatever he can to help his crew and escape. After rescuing pilot Joker, a blast sends Commander Shepherd out into space as he is asphyxiated and dies. This outcome is brilliant because it lets gamers know that every act of heroism comes at a price in one of the most consequential games of the 7th generation.
Red Dead REDEMPTION: FINAL SHOWDOWN
Ending the modern masterpiece is perhaps the greatest conclusion to any piece in the medium. After killing the last member of his old gang as agreed to by a deal with the government, protagonist John Marston reunites with his family. The game lets you settle down with them through a handful of missions until the government returns for John. After securing the family's safety, the game gives the player control of John facing down the rest of the government's goons. You are able to kill up to six but, no matter what, John is gunned down. The decision to allow players to control his last fighting chance is powerful and unforgettable.
Shadow Of the COLOSSUS: FINAL IN-GAME moment
In this, you play as a young warrior named Wander, searching for a way to bring a girl back to life. He comes across an ominously voiced entity named Dormin that promises that they will fulfil Wander's goal on the condition that he slays 16 fantastical colossi. Throughout Team Ico's magnum opus, something seems clearly wrong about your actions. After killing the last colossus, you are greeted with a devastating revelation. It is revealed that Dormin was using you for harnessing the colossi's power. A cursed and weak Wander is killed by a group of soldiers after a shaman leads them to stop the slaying of colossi. Dormin then morphs Wander's body into a shadowy giant.
As if the guilt was not enough, the game forces players to play as the literal manifestation of the monster they have become in the form of Dormin. You are allowed to kill some of the soldiers before the shaman uses Wander's sword to lift the curse. You are then sucked into a whirlwind of light that reduces your cursed stature until you are a humanoid shadow figure. As you are being sucked in, the last thing Wander sees is the lifeless body of the one he loves. The game then gives players control of shadow Wander as he hopelessly holds his arms at her until he is consumed. Colossi's death theme plays during this scene for perfect thematic effect. This choice of control given to the player is ingenious. It is immensely heartbreaking and the best piece of interactive tragedy ever brought to the medium.
Can you think of any moments like this where failure is unavoidable or a boss is impossible? Share these in the comments.