It seems that you're using an outdated browser. Some things may not work as they should (or don't work at all).
We suggest you upgrade newer and better browser like: Chrome, Firefox, Internet Explorer or Opera

Bane of the Cosmic Forge, maybe, which I played through for the first time a couple years ago. Splendid use of language to create atmosphere.
Probably Arcanum for me. In particular, the final conversation with the big bad really had me immersed. Since then I've of course played immersive stuff, but nothing where I really felt like I was the character I was playing.
Gemini Rue was good, but The Walking Dead is better (I'm playing it now, just finished episode 3). As I'm not the first one to say this, this probably holds some truth. Portal 2 also qualifies as does Dear Esther. If you can have discussions about how a story really is (if it is told in a good way - it's also possible to discuss if a story isn't well told), like I had with those last two games, the story and immersion can be qualified as good.
Deus Ex Human Revolution. I find the social topics in that series extremely interesting (freedom to aug versus how it changes man and the world) and thought they handled the dialog and plot pretty well.

Gemini Rue is a good one too, more in a movie sense than "immersed" though.

I replayed Fallout 2 not long ago, still love that game's story and dialog.
Recently been replaying Baldur's Gate 1 & 2 using the BGT mod. I finished BG1 and am currently playing through BG2. Aside from that I just replayed Silent Hill 2 with my wife. It was her first SH game and she was pretty into it.
Actually right now I've been playing this game called Rise of the Argonauts. So far there has been far more storyline/dialogue than gameplay and I've been loving it. Some of the best voice acting talent. It's pretty engrossing story going over a variation of the Jason and the Argonauts tale.

So as of this weekend. I've had this game sitting on my shelf collecting dust for a good 2 years. Haven't touched it.

Game looks pretty nice as well. So far 8.5/10 gaming experience for me.
Most recently the Mass Effect series, before that it was probably freelancer, and going back further it was probably Command & Conquer.

As you can see it doesn't happen anywhere near as often as it should.
DX:HR, TW2, there have been a few last year and this year.
Definately Deus HR, and just recently Amnesia the dark descent.
Fallout(s) and Deus Ex(s) Are the only ones I can think of that actually had me well.. thinking alot about them.
Fallout III.
Geneforce 1
Alan Wake. Sure, it's arguable whether the writing is sophisticated or just pretentious but personally I really considered the plot engaging. I wanted to know what all of this is about, what's going to happen to Alan and his wife etc. and it certainly had some mindfucks.

But the last time a game's plot REALLY blew my mind away was probably in Kane & Lynch. You can say what you want about the gameplay (although personally I liked it) but the characters are brilliant. Some may say that the plot is absurd, I say that it's just over-the-top, a bit like Paul Verhoeven's movies. I have rarely seen such unlikely anti-heroes, guys you may want to like and to consider cool but who are actually old pathetic screwups. And it provided some of the most powerful moments I have ever experienced in a game ("you should have fucking let me talk to them!"). I actually ended up beating the game in one session - not just because it's short, I really wanted to know how things are gonna turn out.
Post edited November 26, 2012 by F4LL0UT
There have been several games in which I have been immersed in the gameplay but the story is a different matter entirely. Perhaps Dragon Age 2. I just played that recently and while I didn't enjoy a lot of the combat or gameplay, I genuinely did enjoy the story and cared about some of the characters (sorry Anders, you were just too much of a pansy).
Morrowind. It doesn't have the best graphics, and the combat system isn't good, but it's one of the best games I've played. The storytelling, and the world in general, support you entering as a nobody and coming out as - well, I won't say for the sake of spoiling - far better than the later TES games do (look, I've just seen the emperor assassinated! I'll walk off and join the mages' guild! Yeah...)

I don't want to hype it too much, but if you enjoy rpgs like TES but have been irritated by the consolification of later releases (oh look, every heavy armour has an equivalent light armour. What a coincidence), I'd recommend trying out Morrow some time.