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.
The Call of Cthulhu rpg features countless Tome of Eldritch Lore for characters to study the Mythos with. The rules plainly state these tomes take long periods to study - many weeks for the best quality ones. This is because the books are written in bizarre ciphers and obscure occult jargon (black magic and forbidden knowledge is something you want kept a SECRET), the standards of literacy were much worse in the ancient eras (so don't expect good grammar or spelling, let alone an index), and the authors were insane megalomaniacs with poor social skills (so expect repetitive ranting and badly communicated instructions). Additionally, these ancient books are fragile and require delicate handling. Some spells learnt from these tomes may not work or backfire because the book is missing a page or the handwriting is blurred.
GURPS Discworld Also and other parts of the Discworld Role-Playing Game sub-line, in keeping with the source novels, note 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 — or, perhaps more likely, end up landing in the trifle on the dining-table below.
Some notes on the GURPS Ritual Path Magic system detail how someone would go about buying a grimoire to cast a powerful spell or a charm that casts a powerful spell for you ... as well as discussing how the local magical underground and people investigating you would track down your purchases and find out what you bought. You see, the market for magical stuff is likely to be 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.
Pathfinder zigzags the Guns Are Useless trope with firearms: while primitive (few adventure paths have guns more complex than flintlock), pricey to mantain and always are at risk of misfiring, they hit touch AC at close range (meaning armor is completely bypassed), have consistent damage, and have a large critical multiplier. Magic can only do so much when a bullet is cruising towards your organs.
They get even deadlier in the hands of a competent Gunslinger, who can eventually negate most of their downsides such as being able to clear a jammed barrel or aim at specific spots to cripple enemies. While characters who aren't proficient in firearms can use them as unreliable sidearms at best, it turns out that a class whose entire quirk is actually learning how to operate and mantain guns can actually employ them effectively.
A Barbarian's Rage, at least before the unchained version, could lead to this. While Raging, his Constitution increases and as such he gains some extra HP. Unfortunately reaching negative HP will end his rage immediately, and with that he will lose the Constitution bonus and thus take damage equal to the extra HP he gained, possibly killing him. Just because your Unstoppable Rage lets you fight on with grievous wounds, they will still take their toll once you run out of adrenaline.
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 Stripperiffically dressed gladiators...and have exactly the kind of piss-poor armour save you'd expect in reality. In a double take of this trope, they use their fighting skills to bob and weave in melee, making them considerably more survivable in hand-to-hand. Standard procedure for players using them is to use a transport, and if that's unavailable then to hop between cover and hope for the best.
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 Empire actually holds very little galactic space within their territory. They were fortunate enough to have emerged within a dense galactic cluster, making new worlds relatively close. While they have dozens of star systems under their banner, the area their empire takes up is practically a speck on the galactic map. Also, as much as they have spread out, they're well aware that a single supernova could drive them extinct, further fueling their their expansionism.
Tau also have next to no presence in the Warp, which has a few consequences. First is that this makes it dificult-to-impossible for them to interact with it and their technology hasn't matured enough to do it for them. Though they are aware of the Warp, it makes them largely ignorant about its nature and how to better utilize it. In this 'verse, this means that they have only a crude, painfully slow FTL, but the tradeoff is that it's much safer. A secondary effect of this, combined with how young their entire race is and their short lifespans, they have not made much headway into the galaxy at large.
The second consequence is that the Tau are almost completely ignorant of the nature of the Warp. They aren't aware of the deletrious physucal and mental effects of exposure, the impossibility of making trying to sense of it, the living beings born from it, nor Chaos as a whole, and no understanding of psychic powers beyond what they see from other species. This has some minor upsides, in that there's no risk of Demonic Possession, and not regularly travelling through the Warp means they don't have to worry about the countless horrors waiting within. It also means, however, that on the rare occasions when they do encounter Chaos forces, such as during Dawn of War: Dark Crusade, they struggle to understand just what they are dealing with.
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. However, as he is addressed as "citizen", he may have been kicked out of the military as well.
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.
Note that the Orks are not completely immune to the effects of this trope. As it turns out, their greatest enemy is not the Imperium, the Eldar, the Tyranids or even the Forces of Chaos; it's... themselves. The Orks take their Blood Knight tendencies to their logical conclusion — in the same way that some pieces of Ork tech have a chance to backfire horribly due to shoddy workmanship, Orks can and will turn on each other out of boredom and frustration if they run out of enemies from other races to fight. You may think that because of how vastly numerous they are this wouldn't be an issue, but Ork empires regularly rise up and go on campaigns of slaughter, only to eventually collapse in on themselves in a frenzy of fratricidal carnage. Also, while they may believe that War Is Glorious, it bears repeating that they are made for fighting and winning; enjoying a fight is well and good, but losing is no fun at all. If a Warboss can't lead his subordinates to victory, they will turn on him.
Wounds from weapons with a strength twice the target's toughness will cause instant death. Since they're logically strong enough to tear them in half.
The Feel No Pain rule cannot ignore wounds caused by attacks with Instant Death. While theres a myriad of reasons justifying the rule, from superhuman durability, Healing Factor, and a plain old medic being nearby. The main reasoning is that it wouldn't matter if you couldn't feel a wound like that.
In the Horus Heresy variant ruleset, there are far less characters with the Eternal Warrior special rule. This means that some fancy swords that cause Instant Death one sixth of the time are popular, but the Chunky Salsa Rule is also in full effect, and the weapons don't often make the wielder more likely to actually hurt the target. Many an epic feat of swordwork from a peerless Astartes warrior with a billion points worth of equipment has been cut short because the enemy Archmagos simply shrugged off the hits and splattered his opponent with a Power Fist.
A similar point of fact about the Heresy is that the vast majority of armies are some variant of Adeptus Astartes. This means that you normally have a very good read on your opponent's capabilities. However, they get the same, and while the Night Lords' terror tactics or the World Eaters' rage-filled melee charges may work extremely well against squishy human conscripts, against other Space Marines or the monstrous combat robots of the Mechanicum, they're an extremely good way to get your head blown off in shower of bolter shells.
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 any sufficiently experienced character, anybody can and will die. Most basic mooks deal enough damage to take quarter or even third of all the 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 a single strike. Then there is bleeding. And penalties mounting with loss of hit points. A single arrow or bolt can kill your character on the spot. Chunky Salsa Rule is very firmly in place. And all of that covers only a bog-standard confrontation with humanoids. Most of the 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 a 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 amounts 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 feed 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.
Arguably, 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 simply 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 gunfights (say, the police arriving) and the emotional ones. Long story short - people in real life are not action movie heroes. They just can't take it.