CRM Query Performance using NoLock and Dirty Reads
By Joe Gill
Published On 17th May 2016
Using NoLock can provide a performance boast to your CRM code when querying data. When you use NoLock the sql generated sets the transaction isolation level to READ UNCOMMITTED. What this means is that your query does not issue any shared locks. Shared locks prevents the data you have read being modified. As a consequence of this it may possible for your query to return “dirty reads”.
QueryExpression qe = new QueryExpression();
qe.EntityName = “account”;
qe.ColumnSet = new ColumnSet(“name”);
qe.NoLock = true;
Dirty reads are where you read data that has been modified but not committed by another transaction. To demonstrate returning “dirty reads” when using NoLock I did the following. I created a plugin that triggers on the account create post operation event. The plugin sleeps for a minute if the account name contains the word ghost. This will stop the transaction from committing the database insert until the sleep has completed.
QueryExpression qe = new QueryExpression();
qe.EntityName = “account”;
qe.ColumnSet = new ColumnSet(“name”);
qe.NoLock = true;
Dirty reads are where you read data that has been modified but not committed by another transaction. To demonstrate returning “dirty reads” when using NoLock I did the following. I created a plugin that triggers on the account create post operation event. The plugin sleeps for a minute if the account name contains the word ghost. This will stop the transaction from committing the database insert until the sleep has completed.
if(entity.LogicalName == “account”)
{
if(context.MessageName.ToUpper() == “CREATE”)
{
String name = (String)entity[“name”];
if (name.Contains(“ghost”))
System.Threading.Thread.Sleep(60000);
}
}
From Dynamics CRM I then created a new account with the name ghost and the UI froze while the plugin executed. Meanwhile I ran the following code which executes a QueryExpression against the account entity. It then sets QueryExpression NoLock attribute to true and executes the query again.
QueryExpression qe = new QueryExpression();
qe.EntityName = “account”;
qe.ColumnSet = new ColumnSet(“name”);
Console.WriteLine(“*********** Retrieve Accounts”);
EntityCollection ec = _orgService.RetrieveMultiple(qe);
foreach (var a in ec.Entities)
{
Console.WriteLine(“Name: “ + a.Attributes[“name”]);
}
Console.WriteLine(“*********** Retrieve Accounts – No Lock”);
qe.NoLock = true;
ec = _orgService.RetrieveMultiple(qe);
foreach (var a in ec.Entities)
{
Console.WriteLine(“Name: “ + a.Attributes[“name”]);
}
As you can see from the results the second set of records includes details on the ghost account even though UI is still frozen and the database transaction has not been committed yet.
You can need to consider carefully if you should use NoLock in your environment and dirty reads may not be a problem if you seldom have transaction rollbacks. Alternatively, you made decide to use it in specific queries where the data changes infrequently.
NoLock can also be used with FetchXML as follows <fetch mapping=’logical’ no-lock=’true’>
