A lock is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated when multiple users try to update to the database. Any user can only modify those database records (that is, items in the database) to which they have applied a lock that gives them exclusive access to the record until the lock is released. There are two types for locking data techniques in a database:
1. Pessimistic locking
2. Optimistic locking
In pessimistic locking a record or page is locked immediately when the lock is requested, while in an optimistic lock the record or page is only locked when the changes made to that record are updated. The latter situation is only appropriate when there is less chance of someone needing to access the record while it is locked; otherwise it cannot be certain that the update will succeed because the attempt to update the record will fail if another user updates the record first. With pessimistic locking it is guaranteed that the record will be updated. Locking can be controlled by isolation level.
SQL Server Quest 