This loser is you: the appeal of netorare
A previous post suggested that otaku (moe otaku in particular) tend to have a very righteous approach to romantic relationships, in the sense that they value pure, sincere, genuine commitment to a romantic interest, both during a relationship and as a prerequisite to forming it (it's a trivial but significant observation that the genre overwhelmingly favors “confessing” over “asking out,” for instance). Since their ethos makes the commitment precede the relationship, otaku are prone to unrequited feelings and infatuation. And turning to 2D doesn't really help here for now: usually, your dakimakura won't talk back.
Of course, not all otaku are romantically challenged. Some are even rumored to be married with kids. Nevertheless, anecdotal observation and secondary evidence do suggest a strong connection between otakudom and a specific perception of romantic relationships (not a very successful one). It is a bit of a cliché, but one that has enough basis in reality that the medium itself can play with it in interesting ways—particularly by teasing the audience with a surrogate character modeled around that cliché.
This is something the good folks over at tv tropes would call This Loser Is You. Usual romance shows tend to be pretty forgiving to this character type, with some happy, more or less believable resolution in the end. A few shows hit harder, however. The prime example is probably Evangelion, which was basically crafted as a ten-hour long insult on its viewership. And I must say I did derive a measure of enjoyment from watching the show, in some warped, masochistic sense—the pathetic realization that I hadn't dealt with all the issues Shinji is going through despite not being an effing useless teenager anymore, I guess (although the brat has so many issues that this might be expected).
As far as masochism goes, however, ero has a lot more to offer. The singularly scathing narrative pattern I want to go into here is netorare. In colloquial Japanese speech, the word refers to having your spouse or lover “stolen” from you. There is obviously a wealth of fictional representations of that situation, in a good part of which the character being cheated on is a clumsy but well-meaning guy acting as an audience stand-in. In manga alone, there are so many examples that people have devised multiple subcategories, according to the relationship between the protagonist and the “robber,” the lover's degree of consent and initiative, the way the protagonist realizes he has been cheated on and so on.
I find the sexual hijinks of housewives about as exciting as the civil code, though, so you can tell I'm not an avid consumer of that kind of literature. However, the subculture has extended the meaning of netorare to include situations in which the protagonist is not actually in a relationship, much less married, but has a girl he is desperately infatuated with. He doesn't take steps to approach her, however, and watches powerlessly as some other guy seizes the opportunity. All examples of netorare I know of in loli manga are of this general form.
Of course, nothing is “stolen” from anybody there, but that doesn't make the protagonist's (and correlatively, the reader's) feeling of loss and defeat any less acute, which contribute to the fun—or spoil it, depending on your inclinations. I personally have a definite weakness for this type of stories. It might because I vaguely remember having been in the same situation once long ago, back in middle school. But it's more likely another case of light masochistic tendencies at work.
One slightly funny subversion of this occurs in the otherwise mediocre eroanime (oxymoron alert!) Ai shimai. The plot, conveniently summarized by Wikipedia, has a mother and her two daughters become sex slaves of Mr. Generic Jerk for ridiculous reasons while the mother's husband is on an extended business trip. When he returns, the husband is rather impassive when Mr. Generic Jerk bangs her wife in front of him. But he's devastated when the villain touches her younger daughter, because he secretly loves her (we are shown how he keeps some of her used underwear in his office desk for sniffing purposes).
Turning to works of greater merit, the first example of netorare I encountered was Kabe no mukougawa, a characteristically lovely-and-depressing, and uncharacteristically useable one-shot by Sekiya Asami, collected in her second book Otona ni naru mae ni. The protagonist is head over heels in love for his younger sister, but understandably afraid to tell her how he feels. One day, he wakes up too late from a nap to show up at his part-time job and decides to kill time in his room. That's when her sister comes back from school. With what sounds like an older guy. Nobody is supposed to be home. They go up in her room. Her first time. And the brother weeps as he hears everything though the thin wood wall. The first time I read it, I think I cried too.
And finally, it's again a short piece by Ueda Yuu that made me write this article, namely his contribution to the Nov. 2009 issue of LO (which I only recently got my hands on: it was too late to get it before leaving France, and already out of print when I arrived in Japan, so I had to resort to buying it second hand, non-virgin, which is slightly dirty when you think about it). It's called Otona ni naritai, and the setting is a bit different. Satoshi, the protagonist, is again an older brother, though of the not blood-related variety. He loves her stepsister Satoko, and that feeling seems to be requited to some extent. Satoko goes so far as expressing insistent sexual curiosity when they're alone together. He refuses to satisfy that curiosity just yet, however, seeing as she is so young. But not everyone shares those petty concerns, and a friend of Satoshi offers to make her a woman, so that she can be ready for her brother. She accepts on condition that they do it in front of him, to be sure. So they tie him up, put him a ball gag, and the fun begins. What happens afterwards is so upsetting (and such a turn on if your interests run close to mine) that I'd rather not to spoil it and let you enjoy the piece yourself instead.
That those examples all involve some sort of intrafamilial forbidden love might not be a coincidence, but it's not because I have an incest fetish (I may have many strange preferences, but that's not one at all). I guess it has a role as a plot device, making the qualms of conscience of the loving nice guy all that more palpable.