SQL Server SELECT statements causing blocking
To perform dirty reads you can either:
using (new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
//Your code here
}
or
SelectCommand = "SELECT * FROM Table1 WITH (NOLOCK) INNER JOIN Table2 WITH (NOLOCK) ..."
remember that you have to write WITH (NOLOCK) after every table you want to dirty read
From documentation:
Shared (S)
locks allow concurrent transactions to read(SELECT)
a resource under pessimistic concurrency control. For more information, seeTypes of Concurrency Control
. No other transactions can modify the data whileshared (S)
locks exist on the resource.Shared (S)
locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain theshared (S)
locks for the duration of the transaction.
A shared lock
is compatible with another shared lock or an update lock, but not with an exlusive lock.
That means that your SELECT
queries will block UPDATE
and INSERT
queries and vice versa.
A SELECT
query will place a temporary shared lock when it reads a block of values from the table, and remove it when it done reading.
For the time the lock exists, you will not be able to do anything with the data in the locked area.
Two SELECT
queries will never block each other (unless they are SELECT FOR UPDATE
)
You can enable SNAPSHOT
isolation level on your database and use it, but note that it will not prevent UPDATE
queries from being locked by SELECT
queries (which seems to be your case).
It, though, will prevent SELECT
queries from being locked by UPDATE
.
Also note that SQL Server
, unlike Oracle
, uses lock manager and keeps it locks in an in-memory linked list.
That means that under heavy load, the mere fact of placing and removing a lock may be slow, since the linked list should itself be locked by the transaction thread.
SELECT can block updates. A properly designed data model and query will only cause minimal blocking and not be an issue. The 'usual' WITH NOLOCK hint is almost always the wrong answer. The proper answer is to tune your query so it does not scan huge tables.
If the query is untunable then you should first consider SNAPSHOT ISOLATION level, second you should consider using DATABASE SNAPSHOTS and last option should be DIRTY READS (and is better to change the isolation level rather than using the NOLOCK HINT). Note that dirty reads, as the name clearly states, will return inconsistent data (eg. your total sheet may be unbalanced).