SQL Server uses locks to provide concurrency while maintaining data consistency and integrity. Whenever you read data, SQL Server locks the data while reading, to prevent other connections from modifying provide the data at the same time.
If you modify data, SQL Server locks that data as long as the transaction lasts to avoid dirty reads in other connections. These locks are called exclusive locks, and they are incompatible with any other locks.
SQL Server uses other types of locks to prevent specific concurrency problems and to guarantee the consistency of internal operations.
Locks are maintained as long as they are necessary, depending on the isolation level selected. It is the programmer's responsibility to design short transactions ...