Lock a Database Record For a User
Please see this article for details.  Level of concurrency is controlled by isolation level you chose in SQL server or in your connection. Unless specified or changed Read committed is the default isolation level when you install SQL server and do not overwrite in your transaction. 


  Transactions specify an isolation level that defines the degree to
  which one transaction must be isolated from resource or data
  modifications made by other transactions. Isolation levels are
  described in terms of which concurrency side effects, such as dirty
  reads or phantom reads, are allowed.


That being said despite any isolation level you chose data modification is always protected.  


  Choosing a transaction isolation level does not affect the locks that
  are acquired to protect data modifications. A transaction always gets
  an exclusive lock on any data it modifies and holds that lock until
  the transaction completes, regardless of the isolation level set for
  that transaction. For read operations, transaction isolation levels
  primarily define the level of protection from the effects of
  modifications made by other transactions.


You can still encounter lost updates if you not at least at Repeatable read isolation level.  See this article for details about repeatable read.

This is a good article explaining isolation level with examples.