CODEWITHSUNDEEP
c#sql-server
Renae
Renae

Reputation: 442

What is the difference between NOLOCK and TransactionScope?

What is the difference between:

sqlCmd = "SELECT * FROM dbo.Products WITH (NOLOCK)";
List<Product> products = thisDl.ExecuteQuery<Product>(sqlCmd).ToList();

sqlCmd = "SELECT * FROM dbo.ProductItems WITH (NOLOCK)";
List<ProductItem> productItems = thisDl.ExecuteQuery<ProductItem>(sqlCmd).ToList();

And:

using (new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
    List<Product> products = (from p in dc.Products where select p).ToList();
    List<ProductItem> productItems = (from p in dc.ProductItems where select p).ToList();
}

Of the two, which is better to use?

Upvotes: 1

Views: 3426

Answers (2)

Asken
Asken

Reputation: 8061

For your statements they are the same. Now TransactionScope is the recommended one to use and NOLOCK is said to be used as a last resort.

Upvotes: 2

Pranay Rana
Pranay Rana

Reputation: 176936

NOLOCK table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance because the database engine does not have to maintain the shared locks involved. The downside is that, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read.

TransactionScope is a lighter, faster way to enlist modifications to data in a transaction. But the TransactionScope is more powerful and its grasp extends beyond databases. The TransactionScope can enlist other things like COM+ objects in a transaction, and you will shortly see the it appearing alongside LINQ for data queries and in ADO.NET for entities. TransactionScope: don't leave home without it. TransactionScope is an evolutionary step in supporting database transactions. Perhaps the idea was that an easier-to-use transaction model would be a model that will be used.

Upvotes: 0

Related Questions