I'm a bicycle mechanic, not a magician (faith healer, miracle worker, fill in your favorite McCoyism here)!
Yep, it's true. Sometimes a bike comes to me that is just beyond help, or hope. It's actually pretty rare, but that doesn't make it any easier.
I wanted to address these situations, because they are difficult, for both the owner and the mechanic, assuming the mechanic really cares about what they do. And let's assume that here, ok?
The most straightforward, but by no means easy variation on "it's dead" is when I discover some really serious damage to the frame, such as a crack in the tubes. Sometimes, dirt will hide such things from the owner, and even the mechanic, unless the bike is cleaned and examined closely. Upon discovering this kind of damage, I stop work, and contact the owner, if they are not already present (sometimes, rarely, we can catch it on the initial check in). A crack in the frame, anywhere, means "game over" in almost every case. The cost of repair is such that unless the bike has great collectible or sentimental value, that it just doesn't make sense to pursue that option. For the typical customer, a crack means a new bike, or at least a new frame. If it's within a warranty period and terms, that's better, obviously, but in many cases, it's just a loss.
The reason I say those cases are straightforward is because just about anyone can see and understand that a break in the frame of the bicycle means it's finished. Even the most optimistic rider can grasp "if I keep riding it, bad things will happen" when they see a crack. Not so the more subtle problems that I've seen, such as a bent frame. And depending on the location and nature of the damage, and the material of the frame, a bent frame may be salvageable. But even then, it's important that the customer understand that the bike has sustained damage which may affect it's long term safety and utility. And in some cases, a bent frame can not be made safely rideable. I've found that convincing people that this is true can be a big challenge, especially if they have been riding it in that condition for a while. It's my job to make sure they are told the truth about the damage, as well as my professional opinion on the best course of action to take. But I cannot force someone to give up on a damaged bike, and that puts me in a tough position. I hate the thought that someone might get hurt, but if I have laid it all out for them, and they choose to continue riding the bike, I can't stop them.
What I can and will do, in addition to warning the owner, is not allow myself to be pulled further into responsibility for any future problems. By that, I mean I can tell them I will not work on the bike in its damaged condition. If I feel the bike is not safely rideable, it would be irresponsible of me to do other repair work to get them back on the road. And it would be even worse for me to attempt a "repair" to the damage that will not work. What do I mean by that? Well, take a frame that has a bent fork, for instance, or a misaligned rear triangle assembly (the parts surrounding the back wheel)... If it's a steel frame, due to the nature of the material, I can sometimes safely "cold set" the frame or fork back into alignment. On the other hand, aluminum, a very common frame material these days, does not respond well to cold setting. In fact, aluminum is very prone to failure if bent and then re-bent back to its original shape. Titanium and carbon fiber are basically impossible to cold set.. in fact, carbon doesn't bend, it breaks. So if you bring me an aluminum bike that's been bent, I won't even try to straighten it, and I will discourage you from trying yourself. And yes, I have had someone propose that. Luckily, as it turns out, we found him another bike for such a good price he changed his mind.
It's important to point out here that any frame that has been bent in some way needs to be examined by a professional, and it's best to listen to what they say. Your bike was designed around certain angles and tubing configurations, and any alteration of those could adversely and dangerously alter the bike's ride qualities. I've had folks bring in bikes with forks bent back badly from hitting a curb or something, and getting them to understand that a) the metal has been fatigued, and depending on the severity, may fail later, and b) the handling of the bike is now compromised and could prove dangerous to the rider. It helps to show people clearly what I'm talking about... I use a straightedge, or a frame alignment tool to show the problem, and explain why it is a problem. Most of the time, with patience, people "get" it, and will follow my advice.
Another category of "dead" bike syndrome is when some component that is meant to be removable or adjustable becomes permanently fixed to the frame. This can happen due to rust or corrosion, or it can be caused by a chemical reaction between two different metals. The latter commonly occurs with seat posts and some types of stems. It's standard procedure when assembling most bicycles to grease those items when they are installed, providing a barrier between the two metals (typically steel and aluminum). If that step is neglected, it is possible over time for a chemical reaction (as I recall, it's called galvanic corrosion) to cause the two metals to bond to one another. As a mechanic, I can try a number of things to break that bond, but sometimes it just won't budge. If the seat or handlebar position is fine as it is, then it's not a huge problem, as long as the customer understands the limitations they now have. If there's any need ever to move or remove those items, you're stuck, literally and figuratively, and it's time to look at a new bike or frame.
In other cases, it may be rust or similar corrosion that is the culprit, and often that occurs in the bottom bracket... the area where the crank bearings reside. Moisture can collect there, as it's the low point on the frame, and if rust is allowed to take hold long enough, it can be impossible to remove or even adjust the bottom bracket bearing assembly. Again, your mechanic can try a bunch of different things, but sometimes it just won't move. And as I said above... if you can live with the fact that those parts are frozen, then the bike is still ridable, but most often it's time to shop for a new bike, or at least prepare for that when the frozen parts become a fatal problem for the bike.
I always find it hard when one or more of these problems crop up on a customer's bike, especially if I know they really like the bike. If it's "just a bike" to them, obviously it's not that big a deal... but many folks really become fond of their bicycles. It makes sense... my bikes, especially those I've had the longest, or ridden the most, are almost companions, having shared some good times with me. So it's hard to say goodbye, silly as it may sound to some. I understand that, so when I find a problem that I think is "fatal", I try to be honest, and make it clear that I understand that it may be hard for the customer to hear and accept. I want to fix all of them... but sometimes I can't... and it's important to explain why, so the customer knows I'm not just trying to sell them another bike, or am too lazy to fix it. I suppose there are people out there who approach it that way, but I've never met them, and I hope I never do.
Next: "It's not dead yet!" Or what happens when it's not a total loss, but is very, very far gone.