At depth, I completely agree. Within 5 or 10 feet of the surface, finding out your valve is off should not be a major emergency. Ideally, one would just reverse the descent and return to the surface with a few fin kicks. Then they could proceed to correct the mistake without any critical time dependencies.
Tragedies rarely happen from just one mistake or failure. Usually, there are at least 3 things that need to go wrong to create a fatality. I'd speculate the mistakes that could lead to such a tragedy from a closed tank valve are likely to be as follows: (1) diving grossly overweighted, (2) failure to open the tank valve prior to entering the water, (3) failure to return to the surface upon realizing the mistake (requires dropping weight because of mistake #1), (4) failure to reach an alternate air-source, and/or open the tank valve to correct the problem under water.
By itself, mistake number 2 should not be a big deal. Embarrassing, yes; but certainly not fatal. The big mistake is number 1. Mistake number 1 makes all the other issues much more serious. I feel warning and reminding divers about the dangers of diving with too much weight is a worthwhile endeavor. This mistake can easily be avoided in the dive planning stage. Mess this up and it may turn out to be that one extra thing that takes a manageable situation and turns it into a fatality.
As a mental exercise, everything you just said is true. In the real world, it rarely happens that way. People who experience these failures aren't necessarily overweighted ... and while I agree it's a common error, it's not generally either the primary or proximate cause of a diving accident. And most divers generally won't think to dump weights because they've never actually done it before. Even in a balanced rig, once you attain the surface you need to be able to remain there, and that's really the proper place and time to consider dumping weights. But many people don't. After all, most divers never practice dumping weights in a class. And almost nobody practices the skill outside of class. And if you don't practice a skill, you don't have that skill ... what you have is knowledge that works well from the safety of your keyboard. But in a moment of stress it's unlikely you'll think to put that knowledge to use.
And what about the buddy ... where's he? Well, classes always tell you that you should dive with a buddy, but they often neglect to actually teach you how. It's common to see dive buddies descending at different rates ... to watch one buddy have an issue and attempt to arrest their descent while the other buddy keeps descending without noticing. Why is this? Because they never actually were taught how to descend together, maintaining eye contact on the descent. Or, more commonly on typical vacation dives, because you're descending as a group, and you may not even know who your "buddy" is. Unless you know that person, and have dived with them often enough to be familiar with their skills, you cannot count on a dive buddy to be handy if you should suddenly need air. That leaves an alternate air source that you carry on your person as your backup plan. And again, it's useless unless you've practiced deploying it under moments of stress ... because stress changes everything in terms of what you'll think to do in an emergency.
Easiest way to avoid this type of mistake turning into a tragedy is to learn how to manage your valve, and practicing it on every dive ... just after the descent, reach back and give it a quick turn, first one way then the other, verifying that it's fully open at the end of the exercise. This gives you knowledge that it can be managed, it gives you muscle memory that it can be done, and it gives you confidence that you can manage it in a moment of stress. Then you'll have something to fall back on if it should be needed that's more substantial than an internet or classroom conversation about something you've never actually tried to do before. Also work on buddy skills ... particularly on staying together and making good eye contact on descents, where problems of this nature are most likely to occur. Descend together ... watching each other. That way if one of you has a problem the other is there to help out.
Don't assume that just because you "know" how you'd handle a situation that you would react that way in a real emergency ... I've asked many divers after a minor mishap why they didn't think to do things they were taught to do, and they invariably admit they just didn't think to. Why? Well, usually because once the class was over they never thought to practice it again. Skills only work when they're ingrained ... and a skill that can't be practiced on a regular basis is questionable at best in an emergency. We don't dive on the internet ... and classes are an artificial environment. Skills need be practiced, both for the muscle memory and the self-confidence that you're prepared to deal with an emergency. Those who fall back on skills that they never tried, or don't practice regularly are taking risks they may not even be aware of.
There's a massive difference between knowing what to do and being capable of doing it during times of stress ...
... Bob (Grateful Diver)