I just can't tell you how much I disagree with this. I absolutely hate the "you just fought your way through a robot infested factory, now you're near the prize. Too bad you only have 74 lockpick and this is a 75 lockpick door. If only you had a hat."
I want my chance to roll a natural twenty. Why else would I be carrying a hundred bobby pins?
I agree with that, but then I also think that the distribution of locks plays a role. In dungeons, locks should not be too tough. In rare cases, they should be very hard, but then there should be a key lying around somewhere of on the body of some NPC. But mostly, once you're in a dungeon you should be able to pick the locks provided that you brought the necessary equipment - either lockpicks or Open spells/scrolls.
Locks with high lock levels should be found in special places - vaults, mansions etc. Mostly to be found
inside of cities, because that's where a highly skilled thief usually operates, and that's where high lock levels make sense and are fun to experience. It shouldn't be one darned chest that is kept from you at low levels, it should be a whole building, or at least a section of a building, that is secured that way. You come across it several times and start thinking "Man, I wish I had the skills to break into there, I wonder what I'd find".
That was.. kinda how it was done in Morrowind. The cellar of Arille's Tradehouse and the Census and Excise Office for example, they were at the very beginning of the game and there was NO way to break into the former and only a very small chance to break into the latter. Later, all the vaults in Vivec, the Palace of Vivec... It was fun, because you knew that you weren't supposed to go there anyway as a normal character. The hard and very hard locks were meant for high level thief characters, and you acknowledged that fact.
The "natural twenty" was in Morrowind as well, only that it was done more realistically - you had to have at least some skill already proportional to the lock level. Still, it ofen took you what, 50 times before the lock opened.
Now, on to the different lockpicking systems. There are more than Fallout 3, Oblivion and Morrowind though - I think Thief 1 and Thief 3 also show very important examples of lockpicking systems that could be put into an RPG without any problems. Thief 3 has mostly the same system as Oblivion, but with nicer graphics and also in realtime. Thief 1 had a bar that filled up slowly, when it was full the door opened - also in realtime, and in case a guard came by, you could quickly abort the attempt.
I really enjoyed Fallout 3's system for the game that Fallout 3 was, and I really enjoyed Morrowind's system, but I think that an RPG implementation of those Thief systems would be most excellent. It's true that MW had this annoying "stabbig the lock" part to it, so why not play a nice lockpicking animation instead, which actually interacts with the lock on the door? The bar that fills up could be done more immersive nowadays, for example simply by "successful" sounding clicks and rattles during the animation.
So that's my proposed system... when you make a lockpicking attempt, you actually see your character doing something with the lockpick and the lock in realtime, and instead of hitting the lock 50 times, you just have to wait an proportional amount of time until the lock opens... of course, unless your lockpick breaks first, in which case you have to select a new one and start over (or a guard comes your way and you have to abort the attempt).