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:
- 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.
- 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.
- 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
