As you may know, SQL Server uses different kinds of locks on resources belonging to the requested data and objects, to manage and maintain data consistency and data concurrency. By default, SQL Server acquires a shared lock on the resources when a SELECT query is executed. The resources can be anything from a table, to a range of keys, or single row. So, when we execute a SELECT query, the SQL Server tries to acquire a shared lock on the requested resources. However, if another transaction is updating the same data and has acquired the UPDATE locks on the same resource, the SELECT query that tries to acquire a SHARED lock on the resources may have to wait until another transaction is completed, based on the transaction isolation level.
The SQL Server allows us to specify query hints in the queries that we execute against the database engine. To avoid the query waiting time caused by lock conflicts, as we just discussed, we can use one of the table hints, WITH...