Tuesday, May 17, 2016

CRM Query Performance using NoLock and Dirty Reads

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.

if (entity.LogicalName == "account")
                {
                    if (context.MessageName.ToUpper() == "CREATE")
                    {
                        String name = (String)entity["name"];
                        if (name.Contains("ghost"))
                            System.Threading.Thread.Sleep(60000);
                    }
                }