The depressing part of that transcript is immediately after the co-pilot tells the pilot he's been pulling back on the stick the whole time - the pilot instantly knows it's all over and that he's got a couple seconds to live, and that it was such a stupid error that got them there. It's horrifying.
Under the normal fly-by-wire control law, constant back stick wouldn't stall the aircraft. The computers would stop pitching the aircraft up before it reached the stall angle of attack. But, the system had dropped to Alternate law which is a reversionary measure, in this case caused by the pitot system failing. Under Alternate law, high angle of attack protection is lost and you can pitch up into a stall.
That said, in the AF447 case, the suspicion is that the F/O never even appreciated they were in a stall, irrespective of Alternate law being active.
The only explanation is that sometimes the brain just goes haywire during incredibly intense moments. Like pedal misapplications in cars, when people frantically try to brake but jam the accelerator instead, it never seeming to click in their heads that they're speeding up, ie pressing the wrong pedal.
The linked yokes in an Airbus to average their inputs without force feedback, unless one yoke or the other is explicitly disabled. The experienced pilot had no idea that his co-pilot was stalling the plane the entire time.
In the Air France crash over the Atlantic, the early theories all centered around the loss of airspeed indicators--a mechanical problem. When the flight recorders were finally found (an astounding feat of salvage BTW), it turned out that the airspeed indicators did fail...but then they came back online, and the pilots flew a perfectly good aircraft into the ocean anyway.
So as we look at this SFO crash, the lessons from Air France are mixed. On the one hand it's possible that the early theories of pilot error will be wrong, and it will be some subtle or previously unknown software or hardware error. On the other hand we know that modern highly trained pilots can make enough wrong decisions in a row to crash a sound airplane.