nLockTime affects transaction validity only, if you make a transaction with an nLockTime in the future (by block height or unix time) it is invalid until that time has been passed. This means that if you sign a locked transaction, you can also just as easily sign a different transaction spending the same outputs that is spendable today.
This is useful if you wanted to make a contingency transaction and give it to a third party which spends the output to a charity if you aren't around to make use of it in a years time. If you are around you can spend it in an alternate way, if you aren't a third party can broadcast the locked transaction to the network once it has become valid.
The limitation is in that you can't prove that funds are actually locked in this state, there's no way of knowing that the person won't just spend around an nLockTime transaction in a different way and bypass the restriction on when they could spend the funds. Setups are possible where a user sends an output to a new key, makes a locked transaction to themselves at another address, then destroys the private key, but this is incredibly flimsy and has no real assurances that the key wasn't retained secretly.
CHECKLOCKTIMEVERIFY is a new proposed opcode that allows you to use the current real time in a transaction script, allowing some more interesting and provable use cases that involve actually locking money rather than a particular transaction. This is closer behavior to how people normally think nLockTime should operate when they first hear about it.
With CLTV, scripts that have different preconditions for spending based on the time are possible. Rather than limiting to a single presigned nLockTime transaction, a person could have every transaction in their wallet spendable by a specific charity key if 2 years pass without them being spent, or a 2-of-3 multisignature could have a contingency backdoor where an additional party can be included if the funds have not been used for an appreciable amount of time.
CLTV also allows for payment channels to be used even with transaction malleability. A mutated transaction can break nLockTime refund transactions at any point, which makes them quite risky at present. With CLTV the payment channel transactions can just timeout and be refunded to the creator without the need for any further interaction.
BIP65 is potentially dangerous to end users as there are pretty much no take backs (if you lock your money for 5 years, there's no back doors or workarounds that will get your money back before then), but that said, that's a user interface problem rather than a fundamental security one. There's little contention about soft forking this change into the Bitcoin codebase in the future.
