I'm late to this conversation, but I really wanted to answer some of the questions here. I'm a developer, and before switching to Entity Framework, I coded my own data access methods, which usually included some forms of concurrency checks.

First of all, the 'timestamp' syntax is deprecated because it has been replaced by 'rowversion'. But the underlying mechanism is NOT deprecated.

Also, any application-level concurrency checks are very separate from database/transaction locking.

Optimistic Concurrency helps prevent overlapping updates. Example: Bill and Ted both load the same record, make their changes, then hit Save. Bill hits Save first, updating some columns in the record. Then Ted hits Save. This could be 20 seconds later, and it could be 20 minutes later. Without concurrency checks, Ted's updates will undo and/or overwrite Bill's changes without any warning. In most situations, Ted should get a warning saying "Hey, this record has changed by someone else since you last loaded it. Are you sure you want your changes to overwrite their changes?" In most cases, the user will want to see what has already been changed.

Without any concurrency, the SQL update statement will blindly update records.
E.g.: UPDATE table_name SET last_name='Smith' WHERE id=5

With Optimistic Concurrency, the SQL update statement will also check if the record has changed in the database since the user last loaded it. There are two common ways to do this, both involving additions to the 'where' clause:

1) The 'where' clause includes all values in the record as they were last loaded by the user. If any column in the db record has changed since the user loaded it, the number of rows updated will be 0, and the user can be warned of a possible concurrency issue.
E.g. UPDATE table_name SET last_name='Smith' WHERE id=5 AND first_name='Janice' AND last_name='McPhee' AND EmployeeType=2.
This can be cumbersome to work with (at least manually), especially when there are lots of columns.

2) The 'where' clause includes a single 'timestamp' ('rowversion') column. The 'rowversion' column is always automatically incremented any time a change is made to the record, so it's more reliable than checking a last_updated_time. It's also much easier to check a single column than having to check every other column in the record. Like the example above, if any column in the db record has changed since the user loaded it, the number of rows updated will be 0, and the user can be warned of a possible concurrency issue.
E.g. UPDATE table_name SET last_name='Smith' WHERE id=5 AND rowversion=1230

When the database design can be modified, it is simple to go with option 2 and add a 'rowversion' field to the record. Otherwise, option 1 is usually used.

This has nothing to do with database locking. You can't use a database lock to prevent the Bill & Ted situation I described above. And when you have dozens or hundreds of users, Optimistic Concurrency is the only reasonable way to prevent users from unknowingly clobbering each other's changes without having to implement some sort of check-out/check-in locking.