Any tabletop RPG player knows this can happen to the heroes or the villains. It doesn't matter how dramatic the story has made it, one lucky roll from either side can make a climactic showdown very, very brief. The extent to which this happens can tell a lot about the nature of a game and GM. Games that heavily avert this trope (such as Dungeons & Dragons and Star Wars d20) tend to create a very heroic, action-movie like feel.
Grittier, meaner, more brutal games (The World of Darkness, Dark Heresy, Call of Cthulhu, and so on) intentionally invoke this trope to help create the feel of danger, failure, and high stakes. Some games even shoot to overplay this trope in the name of schadenfreude; for instance, in Paranoia, your character is incompetent, your boss is insane, and your teammates will throw you under the bus at the drop of a hat— so sure enough, you're guaranteed to suck, fail, and die repeatedly for laughs.
GURPS Discworld Also, in keeping with the books, notes that reality should ensue sometimes. If someone swings on a chandelier then either this will be an incredibly dramatic and impressive tactic or they'll find themselves dangling helplessly in front of a villain who comments they've read too many books.
The GURPS supplement for the Ritual Path Magic system details how someone would go about buying a Grimorie to cast a powerful spell or a charm that casts a powerful spell for you...as well as details on how the local magical underground and people investigating you would track down your purchases and find out what you bought. See, the market for magical stuff is a niche one, making it almost impossible to guarantee privacy, and no community of savvy wizards is going to neglect keeping an ear out for people getting their hands on dangerous and very useful magic.
An edition of Hackmaster averts No Arc in Archery by noting that shots at long enough distances need a high enough ceiling to not get in the way of the arrow's trajectory.
In-universe, this trope is a common lament of The Fair Folk in Exalted - Creation doesn't "play fair" and actually, well, enforces the consequences of their actions. In the Wyld, things work by dramatic rules, and a raksha can murder his friend, fall in love, or be eaten by tigers without actually needing to worry about the long-term effects. As a result, they are likely to be caught flat-footed when they walk into Creation and suddenly die, permanently, when they are killed.
Dark Eldar Wyches are dressed Stripperiffically... and have exactly the kind of piss-poor armour save you'd expect in reality.
The setting makes full use of taking place over an entire galaxy, which is a bloody huge place. Massive wars fought over decades and dozens of systems and cost billions of lives, like the Sabbat Worlds crusade, are ultimately minor affairs that have little to no effect on the galaxy as a whole. Even ten thousand years later huge swaths of the galaxy are still unexplored or barely understood, with new civilizations discovered (and exterminated) on a regular basis. It takes months or years to travel any significant distance even with (almost) reliable FTL travel. And while the Imperium is most certainly dying, it's so big that it will take thousands of years to be destroyed completely.
Similarly, most people would be greatly surprised to learn that the Tau actually holds very little galactic space within their territory. This is because they have no Warp presence, and are unable to fully utilize Warp Travel so they can only "skim" on it. This means that they make very short jumps and would take decades to make trips that only takes other races a few months to make. Combined with how young their entire race is relative to everyone else (implied to be barely more than a dozen milleniums old, contrasting with Humanity at 40k years, who are still considered the second-youngest race in the galaxy) and their short lifespans (being 40 is apparently ancient for them), they have not made much headway into the galaxy at large.
For all the equipment, training and propaganda supplied to them, most Imperial Guardsmen are absolutely aware of just how outclassed they are against the majority of their opponents. This means they will use any dirty trick in the book, including good old fashioned cowardice. The Imperium has a novel solution to this, however when they are insisted to fight bravely against impossible odds, the Guardsmen have been known to "insist" back.
There's the Void Missile in Apocalypse, which kills you by opening a Negative Space Wedgie at the blast zone. The rules for it forbade special rules like Eternal Warrior or Cover Saves from saving you, as you, the ground under your feet, and anything within the immediate vicinity is either A) being erased from existence, or B) not being killed, technically, but still being teleported off the battlefield. Only a magical force field (invulnerable save) is allowed against it, because their very nature counteracts the Void Missile. Similarly, no matter how great your fortitude or determination is, being stomped on my a Humongous Mecha will still destroy your bones and your internal organs (in-game, stomp attacks made by Titans ignored the Eternal Warrior rule and Armor Saves). note Ironically, fluff describes that Terminator armor can withstand being stepped on by a Titan. It will be completely submerged under ground however with no means to rescue himself.
There's a short story about a Navy Captain entitled "Rocks are NOT 'free', citizen" who figured that crashing an asteroid into a planet was cheaper than firing an expensive magna-melta missile. Turns out the price of sub-light engine fuel and rations for the military personnel, who were sitting in the ship doing fuck-all for months while the asteroid slooooooowly made its way to the planet, was three times more expensive than the original missile plan.
The offending captain's punishment also stands out. The famously draconian Imperium decides this failure calls for... a week-long accountancy class. They aren't calling him a traitor, they just think he sucks at math.
Orks, as a genetically engineered race specifically for war, actually has numerous "failsafes" built into them to ensure that they can run on their crazy redneck crap across the galaxy without suffering this trope. The biggest one is their Gestalt Psychic Field, which literally makes their beliefs real if enough of them believe in it. Minor ones include hardcoding technical designs and specs into the genes of Mekboyz, so they'd instinctively know how to build vehicles, guns and everything else for basic survival without actually needing to be taught it. Their entire biology also centers around this, with Gretchin and Squigs showing up before actual Orks do, to be a form of manual labor and food source so that the warband doesn't starve. They also reproduce like fungi, ensuring that as long as there's something organic around, Orks can reproduce indefinitely.
If the strength of a weapon is twice the target's durability, it WILL instantly kill them. It doesn't matter if its a grunt or a super soldier, if it's strong enough to tear them in half it will, unless armor or another thing stops it.
The Witcher has it in spades. Without counting witchers, who are Purposely Overpowered, everyone can perform just like a human being with different degree of training and fighting experience. But most importantly, while it's possible to perform inhuman deeds with sufficiently experienced character, Anyone Can Die. Most basic mooks deal enough damage to take quarter or ever third of all Hit Points you have - exactly the effect you expect from being slashed in the chest with a sword. Two or three such hits and you are dead meat, no matter how badass you are. Professionals will know where, what and how to hit, so they will drop your character with single strike. Then there is bleeding. And penalties mounting with loss of hit points. A single arrow or bolt can kill your character on spot. Chunky Salsa Rule is very firmly in place. And all of that covers only a bog-standard confrontation with humanoids. Most of offensive spells will melt you, some of them literally - after all, a lighting just strike you or a fire ball swallowed your body. And it gets worse if you were wearing a set of armour, as it's now potentially red-hot, with your body still inside. Monsters, especially the big ones, can kill a character or a whole party in single strike with medicore rolls. What did you expect to happen when a flying lizard in size of a barn decides to land on you and then spit flaming fluid around? And healing takes realistic amound of time, so recovering from a Diner Brawl will be a day or two, but after a climatic confrontation with Monster of the Week or just some big fight the whole party will be a total mess of bloody bits and broken bones, to the point where they will require someone to fed them for next two or five months. If you are lucky, this will happen after the end of the scenario, thus providing a time to heal before next adventure. If not... well, better take out your spare character card. One of many.
Even without the spontaneity of any combat mechanic, there are situations where the DM may just call the Chunky Salsa Rule on you, regardless of what the numbers say.
Arguebly, one of the main themes of Unknown Armies. Both the game's rules and setting are built on trying to show the reader what were to happen if actual human beings, with all their human faults, were to try and pull a World of Darkness. The magician's organization where most of the world's mystical power is collected? More like a school club filled with special-needs children, each of which thinks themselves to be a big, badass magical mastermind. The vast majority of the time they're just being ridiculous. The rest of the time they're horribly dangerous - to themselves as much as anyone else. Magicians in general, who draw their power from belief, Mage: The Ascension style, tend to be completely dysfunctional human failures because it turns out that to believe in something strongly enough it gives you magic powers is pretty much the definition of being psychotically delusional. The big conspiracies who rule the game's setting from behind the stages? One and all would be squashed like bugs if the FBI ever heard about them. The reason that there's an "Occult Underground" in the first place is because magicians are really for the most part a lot better at convincing themselves they possess ultimate power than actually exercising it, and tend to be burned by the hundreds (literally) whenever the muggles find out. The rules, too, take pains to avert the normal tropes of urban fantasy games: combat isn't just gritty and messy, it's not even cool, with an emphasis less on head exploding and more on people shitting themselves, screaming for their mommies and missing each other at point-blank due to panicking with their guns. The combat chapter actually opens with a long essay on all the ways to avoid combat (from negotiating a peaceful solution to just calling the police) and proceeds to elaborate both on the realistic consequences of having cool gunfigths (say, the police arriving) and the emotional ones. Long story short - people in real life are not action movie heroes. They can't take it.