Well for that amount of data you may not see any performance benefits at all. So it will be back to security and integrity benefits for you.
You don't need to lock data rows in SQL as such, because it is geared towards set-based updates. In other words, you don't really pull a server-side cursor very often, and then loop through it. Some purists would say you never do that, but in reality you do have to sometimes, especially when you want to execute a remote stored procedure on each row or a few data items from it.
Can you give us any examples of code that you're trying to write then we can try and move you forward.
Regards
Simon