Transactions in sql server must be Isolated. It means that if a transaction is running and somebody else comes along and there’re trying to work with data that previous transaction working with, they’re not allowed to touch that.

Sql server handle this problem using concurrency and locks.

Concurrency is the process of managing who has access to data. 

The way that access to data is managed by locks.

Locks:

1. Shared Lock                                                                                                                                                Known as a read lock, This type of lock is obtained anytime when issue a select query. Shared lock can be shared with others who are trying to read that data.
2. Exclusive locks                                                                                                                                          Whn ever going to modify data this lock is issued. We need to make sure nobody else working with that data, reading or trying to modify that. This lock prevents others from accessing that data.
3. Intent lock                                                                                                                                                    If someone doing operation and we want make sure that nobody drops that table or nobody drops the database. This is where Intent lock is issued.

One of the issue with locks is it is bit of overhead.

Locks can be maintained at a few levels

1.Row level

2.Page level

3.Table level

Make sure that do locks as little data as possible and for as short a period of time as possible