Blocking resolutions

Once you’ve analyzed the cause of a block, the next step is to determine any possible resolutions. Here are a few techniques you can use to do this:

Use a covering index on the contended data If the query of one of the processes can be satisfied using a covering index, then it will prevent the process from requesting locks on the contended resource
Optimize the queries executed by blocking and blocked SPIDs E.g. break UPDATE/DELETE statements into multiple batches using the appropriate WHERE clauses. If the individual statements of the batch are executed in separate transactions, then fewer locks will be held on the resource within one transaction, and for shorter time periods.
Decrease the isolation level Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED or WITH (NOLOCK) locking hint.
Partition the contended data This way, exclusive locks will be acquired at partition level, so it will increase concurrency.

Isolation levels: dirty reads, non-repeatable reads, phantom reads

An image saying everything about transaction isolation levels in SQL Server:

  Description Dirty reads Non-repeatable reads Phantom reads
Read Uncommited (S) locks are not acquired by SELECT statements.
You can also achieve this degree of isolation on a query basis using the NOLOCK locking hint:
Yes Yes Yes
Read Commited (S) locks are requested by the SELECT statements, but (S) locks are acquired only while data is read (not until the end of the transaction). No Yes Yes
Repeatable Read Shared locks are retained by the SELECT statements until the end of the transaction which completely prevents others from updating any data covered by the lock. No No Yes
Serializable Instead of acquiring a lock only on the row to be accessed, the Serializable isolation level acquires a range lock on the row and the next row in the order of the data set requested.
This prevents update and the addition of rows by other transactions in the data set operated on by the first transaction.
No No No
Subscribe to RSS - isolation-levels